ORA-00932 when search in all_views

"ORA-00932: inconsistent datatypes: expected NUMBER got LONG" is returned when try to search in TEXT column of ALL_VIEW dictionary view.

SQL> select * from all_views  2  where upper(text) like '%SOMETHING%'
  3  /
where upper(text) like '%SOMETHING%'
      *
ERROR at line 2:
ORA-00932: inconsistent datatypes: expected NUMBER got LONG

TEXT column has LONG datatype which is superseeded with BLOB. Quick solution is to use TO_LOB function. But it works only within INSERT ... AS SELECT statement. So, create global temporary table for this purpose:

create global temporary table views_list (
  owner varchar2(30) not null
  ,view_name varchar2(30) not null
  ,text_length number
  ,text clob
);

insert into views_list 
select owner, view_name, text_length, to_lob(text) text
from all_views
where owner not like 'SYS%' -- assuming we exclude SYS views only
;

select * from views_list
where upper(text) like '%SOMETHING%'
;

Another solution is to create function which converts LONG to VARCHAR2 or CLOB.