I have over 1000 views and I want to run a search which will display the names of the views containing the string abc in its SQL. How do I search all stored procedures/SQL including that of my views? When I run the command:
SELECT *
FROM all_source
WHERE text LIKE '%abc%'
it returns me source code in which the string abc is present. But this does not include views.
This gets easier in 12c, where you can use
select *
from all_views v
where lower(v.text_vc) like '%abc%';
This assumes the text string you are looking for is in the first 4000 characters. You might also have the report include any view where text_length is > 4000 along with a warning.
In earlier versions (or to avoid the 4000 character restriction), you might try a PL/SQL loop like this:
begin
dbms_output.put_line('Owner View name');
dbms_output.put_line('------------------------------ -------------------------------');
for r in (
select v.owner, v.view_name, v.text
from all_views v
where v.owner <> 'SYS'
)
loop
if lower(r.text) like '%abc%' then
dbms_output.put_line(rpad(r.owner,31) || r.view_name);
end if;
end loop;
end;
PL/SQL implicitly converts the SQL LONG value to a 32K PL/SQL string.
(In my tests in 12.2.0.1.0, this failed with ORA-06502: PL/SQL: numeric or value error at the select statement when my cursor included SYS.DBA_SCHEDULER_RUNNING_JOBS or SYS."_user_stat", even though other views with longer text were handled successfully, and I'm not sure why. There may be some issue with this I'm not seeing.)