Search for string in view definitions

Sometime it happens that you need to search definitions of the views for some string. Problem is that Oracle 9 works badly with texts bigger then 32KB.

Try this script:


DECLARE

xSearchStr varchar2(100) := '...here_some_string_without_%...';

CURSOR listOfViews is
select OWNER, VIEW_NAME, TEXT_LENGTH
from all_views
--where owner in (...here list of owners....)  --if you need to limit owners
;

xowner       varchar2(50);
xview_name   varchar2(50);
xtext_length number;
xtext        all_views.text%type;

textView clob;

begin

open listOfViews;
loop
fetch listOfViews
into xowner, xview_name, xtext_length;
exit when listOfViews%notfound;

if xtext_length < 32767 then
select text
into xtext
from all_views
where owner = xowner
and view_name = xview_name;

if upper(xtext) like upper('%' || xSearchStr || '%') then
dbms_output.put_line(xview_name);
end if;
else
dbms_output.put_line(xview_name || ' - source code of view too long: ' || xtext_length);
end if;

end loop;

end;

Leave a Reply

Your email address will not be published. Required fields are marked *