Tags:

Show all views including a specific string in the source code

Von Tobias Arnhold 2.02.2016
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:
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.
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:
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:
SELECT *
FROM user_views
WHERE INSTR( DBMS_XMLGEN.GETXML( 'SELECT text
FROM user_views
WHERE view_name = ''' || view_name || '''' ), 'child_report_id' ) > 0 ;

Post Tags:

1 One Comment " Show all views including a specific string in the source code "

Kevan Gelling 04 February, 2016 10:28

You can use this DBMS_XMLGEN trick on small views (length <= 4000) for a SQL solution:


SELECT *
FROM user_views
WHERE INSTR( DBMS_XMLGEN.GETXML( 'SELECT text
FROM user_views
WHERE view_name = ''' || view_name || '''' ), 'dmf_policies' ) > 0 ;