Is there any way I could filter the text column on oracle's all_views table?
For example:
SELECT *
FROM ALL_VIEWS
WHERE UPPER(TEXT) LIKE '%FOO%';
Exception:
ORA-00932: inconsistent datatypes: expected NUMBER got LONG
00932. 00000 - "inconsistent datatypes: expected %s got %s"
Edit:
DESC ALL_VIEWS
Name Null Type
---------------- -------- --------------
OWNER NOT NULL VARCHAR2(30)
VIEW_NAME NOT NULL VARCHAR2(30)
TEXT_LENGTH NUMBER
TEXT LONG()
TYPE_TEXT_LENGTH NUMBER
TYPE_TEXT VARCHAR2(4000)
OID_TEXT_LENGTH NUMBER
OID_TEXT VARCHAR2(4000)
VIEW_TYPE_OWNER VARCHAR2(30)
VIEW_TYPE VARCHAR2(30)
SUPERVIEW_NAME VARCHAR2(30)
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.