Skip to content
Home » Oracle » How Oracle Search View Text in ALL_VIEWS

How Oracle Search View Text in ALL_VIEWS

ALL_VIEWS

Dictionary view ALL_SOURCE contains only several object types like PROCEDURE, PACKAGE BODY, FUNCTION, TYPE except VIEW. If you want to search some text for view definition, 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

As you can see, we cannot use LIKE to filter the column value. There're more explanations about solving ORA-00932.

Solution

For 11g and earlier 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.

4 thoughts on “How Oracle Search View Text in ALL_VIEWS”

  1. TEXT_VC looks very good to be used but what if the view text is more than 4000, still do we have to use cursor and DBMS_METADATA.GET_DDL or, is there any better approach

    1. Our purpose in this post is to search for some specific strings among views in a more efficient way. Getting all DDL of views and inspect them might be far less efficient. Although 4000 characters of limit in TEXT_VC, I believe it can cover 99% views. So, yes, TEXT_VC looks very good to be used.

      1. On what basis can we assume 99% views are under 4000 character limit?
        Do we have solution to the query asked which covers 100% percent views ?

        1. “99%” that I estimated above is just a ballpark figure according to my experience.

          To have a 100% coverage, TEXT column in ALL_VIEWS is still there, which is a LONG without length limit and can be converted into CLOB through CTAS, just like we did in the first solution.

Leave a Reply

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