Menu
How can I search in (put condition on) blob field in oracle, like text fields?
I need someting like:
select * from table_name where blob_field like '%00ff00ff%'
Oracle throws some error on it.
933
A BLOB (binary large object) is a varying-length binary string that can be up to 2,147,483,647 characters long. Like other binary types, BLOB strings are not associated with a code page. In addition, BLOB strings do not hold character data.
BLOBs are not stored in the normal database files on disk in the same way as is other data managed by DB. Instead, they are stored as binary files in a special directory set aside for the purpose.
INSTR Function. This function returns the matching position of the nth occurrence of the pattern in the LOB , starting from the offset you specify.
To enter an Oracle Text query, use the SQL SELECT statement. Depending on the type of index, you use either the CONTAINS or CATSEARCH operator in the WHERE clause. You can use these operators programatically wherever you can use the SELECT statement, such as in PL/SQL cursors.
You can use dbms_lob.instr for this purpose i.e.
select * from table_name
where dbms_lob.instr(blob_field, utl_raw.CAST_TO_RAW('00ff00ff'), 1, 1) > 0
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
