I need to see if any views are referencing any tables that end with a '%_E' or are owned by a user staring with an 'E_%'
To do this I would do a select like from dba_views, BUT, since the text field is a LONG, I cannot do a like...
...how do I get around this ?
you should use PL/SQL, declare a LONG type variable, make a cursor on view_name & text, insert text into your variable, and now you can do : if (longvar like '%E') ...
Thanks for the tip pipo, here is the code for the next guy's reference:
--Select from dba_views (text column) using INSTR:
SELECT owner, view_name, text_length, text
/* Get views with reference to E_ objects */
FOR view_rec IN get_view_text
v_view_owner := view_rec.owner;
v_view_name := view_rec.view_name;
v_text_length := view_rec.text_length;
v_text := view_rec.text;
IF v_view_owner not in ('SYS','SYSTEM')
IF INSTR(v_text, 'E_') > 0
insert into view_text values (v_view_owner,v_view_name,v_text_length);
--DBMS_OUTPUT.put_line(v_view_owner||' '|| v_view_name||' '|| v_text_length);
/* Cursor Trouble */
DBMS_OUTPUT.put_line ('Could not open curosr !');
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.put_line ('No data found');
WHEN OTHERS THEN
DBMS_OUTPUT.put_line ('Some other error');