oracle blob text search

Question

Is it possible to search through blob text using sql statement? I can do select * from $table where f1 like '%foo%' if the f1 is varchar, how about f1 is a blob? Any counter part for this?

Collyrium

Answer 1

This is quite possible and easy to do.

Simply use dbms_lob.instr in conjunction with utl_raw.cast_to_raw

So in your case, if t1 is a BLOB the select would look like:

select *
  from table1
 where dbms_lob.instr (t1, -- the blob
                   utl_raw.cast_to_raw ('foo'), -- the search string cast to raw
                   1, -- where to start. i.e. offset
                   1 -- Which occurrance i.e. 1=first
                    ) > 0 -- location of occurrence. Here I don't care.  Just find any
;

Portulaca

++ When the database isn't yours, to say the schema is wrong isn't helpful all. Thank you so much for resurrecting and answering the question! – Son

And this indeed should be the accepted answer - the question was clearly about BLOB and not about CLOB! – Literacy

This is the correct answer. The one marked as the correct answer is a good answer for a different question: "Which data type should I use when storing a large amount of text?". – Envision

I had to search for a Unicode character, so I adapted this solution as follows: select * from table1 where dbms_lob.instr (f1, utl_raw.cast_to_raw (unistr('\D83D')), 1, 1) > 0; – Forevermore

Answer 2

If it is a Word or PDF document, look into Oracle Text.

Deviation

Answer 3

If you are storing plain text it should be a CLOB, not a BLOB, and then you can still query using LIKE. A BLOB contains binary data that Oracle doesn't know the structure of, so it cannot search it in this way.

This works for CLOBs of any length (at least on Oracle 12C):

SQL> create table t1 (c clob);

Table created.

SQL> declare
  2     x clob;
  3  begin
  4     for i in 1..100 loop
  5        x := x || rpad('x', 32767, 'x');
  6     end loop;
  7     x := x || 'z';
  8     for i in 1..100 loop
  9        x := x || rpad('x', 32767, 'x');
 10     end loop;
 11     insert into t1 values (x);
 12  end;
 13  /

PL/SQL procedure successfully completed.

SQL> select dbms_Lob.getlength(c) from t1 where c like '%z%';

DBMS_LOB.GETLENGTH(C)
---------------------
              6553401

Note that there is only one 'z' in that 6,554,401 byte CLOB - right in the middle of it:

SQL> select instr(c, 'z') from t1;

INSTR(C,'Z')
------------
     3276701

Latashialatch

@Olafur has a good helpful answer below: https://mcmap.net/q/439768/-oracle-blob-text-search – Jorgenson

This is also wrong. It only works for CLOBS that are <32767 characters. Which is pretty small. instr and like will break after that the you have to use dbms_lob.instr to search. – Portulaca

@Olafur: your criticism is false as I have updated my answer to show. – Latashialatch

Stand corrected, it used to be (earlier versions) that like and instr where varchar2. 11g and 12c have overloaded it with a clob version – Portulaca

Answer 4

the below code is to display the details from blob as text using UTL_RAW.CAST_TO_VARCHAR2 function then we use substr function to cut the text from the start of expected data till end. however, you can use instr function, LENGTH function , if you know the location of the data you are looking for

select NVL(SUBSTR(UTL_RAW.CAST_TO_VARCHAR2(blob_body), 
INSTR(UTL_RAW.CAST_TO_VARCHAR2(blob_body), '<ns:xml_element>') + LENGTH('<ns:xml_element>'),
INSTR(UTL_RAW.CAST_TO_VARCHAR2(blob_body), '</ns:xml_element>') - (
INSTR(UTL_RAW.CAST_TO_VARCHAR2(blob_body), '<ns:xml_element>') + LENGTH('<ns:xml_element>'))),
    utl_raw.cast_to_varchar2(DBMS_LOB.SUBSTR(blob_body))
    ) blob_body
from dual 
where SUBSTR(UTL_RAW.CAST_TO_VARCHAR2(blob_body), 
INSTR(UTL_RAW.CAST_TO_VARCHAR2(blob_body), '<ns:xml_element>') + LENGTH('<ns:xml_element>'),
INSTR(UTL_RAW.CAST_TO_VARCHAR2(blob_body), '</ns:xml_element>') - (
INSTR(UTL_RAW.CAST_TO_VARCHAR2(blob_body), '<ns:xml_element>') + LENGTH('<ns:xml_element>'))) like '%foo%';

Survey

Answer 5

Select * From TABLE_NAME and dbms_lob.instr("BLOB_VARIABLE_NAME", utl_raw.cast_to_raw('search_text'), 1, 1) > 0

Gully