How to Query TEXT in ALL_VIEWS

  • by

ALL_VIEWS

Dictionary view ALL_SOURCE contains only several object types like PROCEDURE, PACKAGE BODY, FUNCTION, TYPE except VIEW. If you want to search the content of VIEW, you should go for ALL_VIEWS.

But the thing is, the column TEXT in ALL_VIEWS is LONG which is a plain old text storing type, it cannot be searched.

SQL> conn hr/hr;
Connected.
SQL> column owner format a10;
SQL> column view_name format a30;
SQL> select owner, view_name from all_views where upper(text) like '%NAME%' order by 1,2;
select owner, view_name from all_views where upper(text) like '%NAME%'
                                                   *
ERROR at line 1:
ORA-00932: inconsistent datatypes: expected CHAR got LONG

We cannot use LIKE to filter the column value.

Solution

For 11g and early releases

The first idea in my mind is to convert TEXT into something searchable. Luckily we have CLOB which is a searchable column type. We can just convert LONG text into CLOB for further operations.

The solution is simple, create a table that contains some information you need by using Create Table As Select (CTAS).

SQL> create table all_views_searchable as select owner, view_name, to_lob(text) text from all_views;

Table created.

In which, TO_LOB() function is the key to convert LONG text into CLOB. Now, we can search the new table instead of ALL_VIEWS with the same statement.

SQL> select owner, view_name from all_views_searchable where upper(text) like '%NAME%' order by 1,2;

OWNER      VIEW_NAME
---------- ------------------------------
CTXSYS     CTX_CLASSES
...

For 12c and later releases

You don't bother doing that, a convenient column in ALL_VIEWS called TEXT_VC allows you to do some searches, which is derived and trimmed from TEXT. For example, ALL_VIEWS in release 19c.

Leave a Reply

Your email address will not be published. Required fields are marked *