In a BI environment some source data comes from views that are hundreds of columns long. The data of lot of the columns is set by multilines or even multipage expressions.
For analysis and documentation of these views I'd like to tabularize the columnsnames and their corresponding subquery clause term.
I understand to extract the column names of a view:
SELECT table_name view_name, column_name
FROM user_tab_columns WHERE table_name = :nameOfView ORDER BY column_id;
Further I know to extract the DDL of the entire view with
select dbms_metadata.get_ddl('VIEW','QP_LIST_LINES_V', 'APPS') from dual;In views with a short column list I simple manually parsed the CREATE VIEW-DLL by it's column alias names and their respective column expressions in the subquery clause. Then I did a copy/paste of the column expression into their matching spreadsheet cells. This method is nomore joyfull for very long and complex views and this isconcept is doomed to fail.
Thus I'd like to automate that by PL/SQL procedure or kind of a DDL-extracting view with the columns tabularized.
Is there a way to get the n.th column expression of a subquery by a function or subroutine?
As you might guess I'm quite recultant to write a proper parser on my own to handle this job. Rather I'd like to use kind of a build-in SQL-parser.
How are the odds to get this done? What direction I have to move?