How to text search in BLOB field

Question

We have created a BLOB type column to store 6000 to 7000 ASCII characters of payload. I am inserting the payload using following code.

PreparedStatement stmt=conn.prepareStatement(insertQuery);
String record="My ASCII Payload";
stmt.setBytes(1, record.getBytes());
stmt.execute();

When i run below query i can see the length of chars in the output.

select dbms_lob.getLength(MY_PAYLOAD)
  from RAW_DATA;

6085

I wanted to perform text search in this BLOB column , i tried following options nothing works. What is the correct way to perform text search in a BLOB column ?

SELECT * FROM RAW_DATA t
WHERE dbms_lob.instr(t.MY_PAYLOAD,utl_raw.cast_to_raw(‘1234’))>0



select *
       from RAW_DATA 
       where dbms_lob.instr (MY_PAYLOAD, -- the blob
                       utl_raw.cast_to_raw ('1234'),
                       1, -- where to start. i.e. offset
                       1 -- Which occurrance i.e. 1=first
                        ) > 0 

Answer 1

You can create text indexes on blobs as long as they contain text (naturally)

SQL> create table t ( x int, b blob);

Table created.

SQL>
SQL> insert into t values ( 1, utl_raw.cast_to_Raw('Hello there'));

1 row created.

SQL> insert into t values ( 2, utl_raw.cast_to_Raw('Goodbye tomorrow'));

1 row created.

SQL>
SQL> create index t_ix on t ( b )
  2  indextype is ctxsys.context;

Index created.

SQL>
SQL> select x
  2  from t where contains(b,'Hello') > 0;

         X
----------
         1

SQL>
SQL> select utl_raw.cast_to_varchar2(b)
  2  from t
  3  where contains(b,'Hello') > 0;

UTL_RAW.CAST_TO_VARCHAR2(B)
---------------------------------------------------------------------------------
Hello there