Filter "text" column on all_views
Solution 1
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.
Solution 2
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';
Solution 3
Search on TEXT_VC instead of TEXT
SELECT *
FROM ALL_VIEWS
WHERE UPPER(TEXT_VC) LIKE '%FOO%';
Comments
-
Vitor Freitas over 3 years
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)