Search Database Views

gistfile1.pls

declare
  l_search varchar2(1000) := 'search term';
  l_count  number := 0;
  l_char   varchar2(32767);
begin
  /* remember to turn dbms_output on to see the results */
  dbms_output.put_line('Searching for: "' || l_search || '"');
  
  for rec in (
    select * from all_views where text_length < 32767
  )
  loop
    l_char := rec.text;
    if instr(upper(l_char), upper(l_search)) > 0 then
      dbms_output.put_line('Match found: ' || rec.view_name);
      l_count := l_count + 1;
    end if;
  end loop;
  
  dbms_output.put_line('End of search. Total matches found: ' || l_count);
end;