Query the view sources like you query your PL/SQL source

While developing I use the view user_source many times each day to search my PL/SQL code.
In this view the source code of stored PL/SQL and triggers are provided but unfortunately the source of views is missing. Furthermore the dictionary view containing the source very old and thus the source column is of type LONG. And you cannot query on a LONG column :-(.
I have found several solutions to this problem, ranging from creating a new table using TO_LOB to convert the LONG column to using the function dbms_metadata.get_ddl ('VIEW', view_name). I don't want to create another table each time, and the solution with dbms_metadata did not perform for me, so I wrote a solution using a pipelined function, exposing the same interface as user_source.
The skeleton for the pipelined function and the types needed was created by the Pipelined function generator I created a long time ago. It still works! I used the same column definitions as the user_source view. So I ended up with these types:

create  type view_source_rowtype 
 as object 
 ( name varchar2(30) 
 , type varchar2(12) 
 , line number 
 , text varchar2(4000) 
);
/

create  type view_source_table_type as table of view_source_rowtype;
/

In the pipelined function DBMS_SQL is used to query user_views and to extract the view name and the source. The LONG column is read in chunks and each chunk is divided into lines using the Apex function apex_util.string_to_table ( I love this function, it is soo useful!). The separate lines are returned to the TABLE function with PIPE ROW including line number. The source code of the function is too long for this blog post, but you can download all the code HERE.
A view is created for easy access to the function:

create  view view_source as 
   select * 
   from table(view_source_pf());

Now you can query this view:

SQL> select * from view_source where name = 'VIEW_SOURCE';

NAME                           TYPE               LINE TEXT                                                                                               
------------------------------ ------------ ---------- ----------------------------------------------------------------------------------------------------
VIEW_SOURCE                    VIEW                  1 select "NAME","TYPE","LINE","TEXT"                                                                  
VIEW_SOURCE                    VIEW                  2    from table(view_source_pf())                                

You can now even query your PL/SQL and view code in one pass, for example if you want to look for the text label:

select * from 
(
 select * from user_source
 union all
 select * from view_source
) 
where  lower(text) like '%label%';

Happy coding, Dick Dral