Search for a particular string in Oracle clob column

Question

How to get a particular string from a clob column?

I have data as below which is stored in clob column called product_details

CALCULATION=[N]NEW.PRODUCT_NO=[T9856]  OLD.PRODUCT_NO=[T9852].... -- with other text 

I would like to search for string NEW.PRODUCT_NO from column product_details

I have tried as

select * from my_table where dbms_lob.instr(product_details,'NEW.PRODUCT_NO')>=1 

The above fetches full text from my table.

Any help is highly appreciable.

Regards

Answer 1

Use dbms_lob.instr and dbms_lob.substr, just like regular InStr and SubstStr functions.
Look at simple example:

SQL> create table t_clob(   2    id number,   3    cl clob   4  );  Tabela zosta│a utworzona.  SQL> insert into t_clob values ( 1, ' xxxx abcd xyz qwerty 354657 [] ' );  1 wiersz zosta│ utworzony.  SQL> declare   2    i number;   3  begin   4    for i in 1..400 loop   5        update t_clob set cl = cl || ' xxxx abcd xyz qwerty 354657 [] ';   6    end loop;   7    update t_clob set cl = cl || ' CALCULATION=[N]NEW.PRODUCT_NO=[T9856] OLD.PRODUCT_NO=[T9852].... -- with other text ';   8    for i in 1..400 loop   9        update t_clob set cl = cl || ' xxxx abcd xyz qwerty 354657 [] ';  10    end loop;  11  end;  12  /  Procedura PL/SQL zosta│a zako˝czona pomyťlnie.  SQL> commit;  Zatwierdzanie zosta│o uko˝czone. SQL> select length( cl ) from t_clob;  LENGTH(CL) ----------      25717  SQL> select dbms_lob.instr( cl, 'NEW.PRODUCT_NO=[' ) from t_clob;  DBMS_LOB.INSTR(CL,'NEW.PRODUCT_NO=[') -------------------------------------                                 12849  SQL> select dbms_lob.substr( cl, 5,dbms_lob.instr( cl, 'NEW.PRODUCT_NO=[' ) + length( 'NEW.PRODUCT_NO=[') ) new_product   2  from t_clob;  NEW_PRODUCT -------------------------------------------------------------------------------- T9856