Filter "text" column on all_views

You can't convert to a clob on the fly via a select statement unfortunately. to_lob function works with INSERT statements, but that would mean you'd need to setup a separate table and do inserts into using to_lob.

You can do assignment conversions to varchar in pl/sql, and most of the time you'll find that the text_length in all_views is < 32767, so this will cover "most" cases, although its not a nice as just selecting:

declare  l_search varchar2(1000) := 'union';  l_char varchar2(32767);begin  for rec in (select * from all_views where text_length < 32767)  loop    l_char := rec.text;    if (instr(l_char, l_search) > 0) then      dbms_output.put_line('Match found for ' || rec.owner || '.' || rec.view_name);    end if;  end loop;end;

Here I'm searching the text field for the string 'union'.

Hope that helps.

You can't manipulate LONG columns easily in SQL unfortunately.

For your present problem, as a workaround, you could use the *_DEPENDENCIES views to find all views dependent upon a table:

SELECT *   FROM all_dependencies  WHERE type = 'VIEW'    AND referenced_owner = 'TABLE_OWNER'   AND referenced_name = 'YOUR_TABLE';

Search on TEXT_VC instead of TEXT

SELECT *  FROM ALL_VIEWS  WHERE UPPER(TEXT_VC) LIKE '%FOO%';