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?

Olafur Tryggvason · Accepted Answer · 2015-10-12T22:28:12.663

89

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
;

edited Oct 12 '15 at 22:28

answered Apr 30 '13 at 14:09

Olafur Tryggvason

4,761
24
30

6

++ 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! – Decker Mar 28 '14 at 20:42
And this indeed should be the accepted answer - the question was clearly about BLOB and not about CLOB! – Honza Zidek Mar 02 '18 at 20:15
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?". – latj Jan 31 '19 at 20:11
1

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; – fuggi Jul 11 '19 at 15:18

score 5 · Answer 2 · answered Jun 03 '10 at 23:59

5

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

answered Jun 03 '10 at 23:59

Gary Myers

34,963
3
49
74

Tony Andrews · Answer 3 · 2015-03-11T12:53:41.380

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

edited Mar 11 '15 at 12:53

answered Jun 03 '10 at 11:10

Tony Andrews

129,880
21
220
259

3

@Olafur has a good helpful answer below: http://stackoverflow.com/a/16301995/510711 – flob Mar 02 '15 at 15:34
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. – Olafur Tryggvason Mar 11 '15 at 11:29
@Olafur: your criticism is false as I have updated my answer to show. – Tony Andrews Mar 11 '15 at 12:54
1

Stand corrected, it used to be (earlier versions) that like and instr where varchar2. 11g and 12c have overloaded it with a clob version – Olafur Tryggvason Mar 11 '15 at 13:18

score 0 · Answer 4 · answered Jun 12 '19 at 06:06

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%';

score -3 · Answer 5 · answered Nov 11 '19 at 09:55

-3

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

answered Nov 11 '19 at 09:55

Z Vijaya Kumar

1
1

oracle blob text search

5 Answers5

Linked