Pattern searching on LONG columns from SQL*Plus
HiIs there a way to do a pattern search on a LONG column from SQL*Plus
e.g. I wish to test if a string exists in the view definition
select VIEW_NAME from dba_views where TEXT like '%ABC%'
raising
ORA-00932: inconsistent datatypes: expected NUMBER got LONG
as the TEXT column is a LONG datatype not VARCHAR2
I have tried variations of INSTR, DBMS_LOB and TO_LOB but not able to make it work.
Is there a simple solution (rather than having to write a database function).
Thanks
e.g. I wish to test if a string exists in the view definition
select VIEW_NAME from dba_views where TEXT like '%ABC%'
raising
ORA-00932: inconsistent datatypes: expected NUMBER got LONG
as the TEXT column is a LONG datatype not VARCHAR2
I have tried variations of INSTR, DBMS_LOB and TO_LOB but not able to make it work.
Is there a simple solution (rather than having to write a database function).
Thanks
0