Searching Oracle View Source w/ Wildcard Queries

Last week I actually had the opportunity to get my hands dirty and in doing so ran into an annoyance with searching through source our custom application views within Oracle.  Because it took me longer than my customary several minute all-powerful Google search to come up with an answer that satisfied me, I decided to write a blog entry to keep track of what I did (for future reference) as well as to help others that might come across the same frustration I did.

As any respectable PL/SQL developer knows, packages and stored procedure source can be searched using a VARCHAR typed column that is provided in the ALL_SOURCE and USER_SOURCE views that are owned by SYS.  Source for views however is only exposed via a LONG column in the SYS owned DBA_VIEWS view.  The LONG data type of course is unfortunately not searchable using wildcard predicates.  For example, even the simplest query like the one depicted below that is searching for TO_CHAR function references across all views in the database returns a data type inconsistency error:

QUERY: SELECT * FROM DBA_VIEWS DBA_VIEWS WHERE TEXT '%TO_CHAR%'

ERROR: ORA-00932-inconsistent datatypes expected number got long.

I found many solutions out there that describe creating a copy of DBA_VIEWS but essentially converting the LONG column using the TO_LOB function when the new table is built.  For example, as described here you could build the copy and then query as follows:

        insert into dba_vw
      select owner, view_name, text_length,
      to_lob(text),type_text_length, type_text, 
      oid_text_length,oid_text, view_type_owner, 
      view_type, superview_name 
    from dba_views;
     
    select *from dba_vwwhere text like '%TO_CHAR%';

I didn't like this solution though as I hate creating tables as that requires extra work to get to the solution that I really want and also have to remember to drop the table after I'm done with it.  After much searching I was able to find another solution referenced here that the DBMS_METADATA.GET_DDL function to convert the data in a sub-select thereby making it available to be queried using a wildcard predicate.

    select view_name
    from ( select view_name, 
                  dbms_metadata.get_ddl ('VIEW', view_name) text
           from user_views where rownum <= 100000)
    where upper (text) like '%TO_CHAR%';

While I've found a few other references to the solution above, by in large part most of the proposed solutions go with the create a new table approach which again I didn't like and thus why I'm posting it here so that I can find it again easily and so it might benefit others as well.