PL/SQL (MOSC)

MOSC Banner

Pattern searching on LONG columns from SQL*Plus

edited Sep 4, 2013 6:44AM in PL/SQL (MOSC) 11 commentsAnswered
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

Howdy, Stranger!

Log In

To view full details, sign in to My Oracle Support Community.

Register

Don't have a My Oracle Support Community account? Click here to get started.

Category Leaderboard

Top contributors this month

New to My Oracle Support Community? Visit our Welcome Center

MOSC Help Center