-
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:
DECLARE
v_view_name VARCHAR2(30);
v_view_owner VARCHAR2(30);
v_text_length NUMBER;
v_text LONG;
CURSOR get_view_text
IS
SELECT owner, view_name, text_length, text
FROM dba_views;
BEGIN
/* Get views with reference to E_ objects */
FOR view_rec IN get_view_text
LOOP
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')
THEN
IF INSTR(v_text, 'E_') > 0
THEN
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);
END IF;
END IF;
IF get_view_text%NOTFOUND
THEN
/* Cursor Trouble */
DBMS_OUTPUT.put_line ('Could not open curosr !');
END IF;
END LOOP;
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.put_line ('No data found');
WHEN OTHERS THEN
DBMS_OUTPUT.put_line ('Some other error');
COMMIT;
END;
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|