Show all views including a specific string in the source code

1 Minuten zum lesen

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: show-all-views-including-specific-string-01

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 ;

Tags:

Kategorien:

Aktualisiert: