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.
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
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.
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 ?
“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.