Show all views including a specific string in the source code
Seems to be a simple problem and easy to fix. Actually it is not because if you try one of these examples then you will fail:
1
2
3
4
5
6
7
SELECT view_name, text FROM user_views
where instr(text,'child_report_id') >= 1;
-- ORA-00932: inconsistent datatypes: expected NUMBER got LONG
SELECT view_name, text FROM user_views
where dbms_lob.instr(text,'child_report_id')>=1;
-- ORA-00997: illegal use of LONG datatype
But as always there is another way getting it done. All you need is a table and some SQL: Info: The table can be dropped afterwards.
1
2
3
4
5
6
7
8
9
CREATE TABLE TMP_MYVIEWS as
select view_name, TO_LOB(text) text
FROM user_views;
select *
from TMP_MYVIEWS
where dbms_lob.instr(text,'child_report_id')>=1
drop table TMP_MYVIEWS;
Result:
Strange is the fact that you can’t do it with pure SQL:
1
2
3
4
5
6
7
8
select view_name
from
(
select view_name, TO_LOB(text) as text1
FROM user_views
)
where dbms_lob.instr(text1,'child_report_id')>=1
-- ORA-00932: inconsistent datatypes: expected - got LONG
Update 05.02.2016:
I got a SQL only example provided by Kevan Gelling:
1
2
3
4
5
SELECT *
FROM user_views
WHERE INSTR( DBMS_XMLGEN.GETXML( 'SELECT text
FROM user_views
WHERE view_name = ''' || view_name || '''' ), 'child_report_id' ) > 0 ;