Filter "text" column on all_views

Question

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)   

Answer 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.