Searching for a string within the source of a view

If you do this:

SQL> select * from dba_views
2 where text like ‘%&1%’
3 /
Enter value for 1: NL
old 2: where text like ‘%&1%’
new 2: where text like ‘%NL%’

You get this:

where text like ‘%NL%’
*
ERROR at line 2:
ORA-00932: inconsistent datatypes: expected NUMBER got LONG

….because the text field is a long.

So:

create table clobbed_views
as select
owner, view_name, to_lob(text) text
from dba_views

select * from clobbed_views
where text like ‘%&1%’;

And all is hunky-dory

Advertisements