Resolving Queries into a List of `object name.field` I work on a team that has an SSRS setup with roughly ~1300 (and growing) canned reports. As one can imagine, this has presented problems when a breaking change is introduced to a table. Finding all the reports that may touch a table/field is error prone at best.

I'm trying to programmatically build a dependency model. Getting the schema for Owner -&gt; Object -&gt; Fields and how views/tables relate is fairly trivial. 

What I'm struggling with is how to get a resolved list of fields from a given query. I can pull the query from the RDL files, but interpreting those files is far less so. Queries can contain aliases for fields and tables alike, plus there are problems such as select *. I'm trying to avoid a regexp hack, and I really don't want to write a SQL interpreter...

My initial thought was to loop through the RDL files and parse the explain plan output. While technically possible, this doesn't give me a complete list of resolved fields.

Is there any method in which I can use to get the DB analyse a given query and return a list of Object_Name.Field_Name? I don't mind having to do some text processing to pull out the results if necessary.
Oracle 12c provides the procedure DBMS_UTILITY.EXPAND_SQL_TEXT.

EXPAND_SQL_TEXT Procedure

If you have a query, it can expand its text to involve th base tables and its columns.

Example:

SQL&gt; create table t1 (C1 number, c2 number);

Table created.

SQL&gt; create view v1 as select * from t1;

View created.

SQL&gt; create or replace view v1 as select c1 as c3, c2 as c4 from t1;

View created.

SQL&gt; create or replace view v2 as select * from t1;

View created.


To find the tables and columns used for select * from v2:

SET SERVEROUTPUT ON 
DECLARE
  l_clob CLOB;
BEGIN
  DBMS_UTILITY.expand_sql_text (
    input_sql_text  =&gt; 'SELECT * FROM v2',
    output_sql_text =&gt; l_clob
  );

  DBMS_OUTPUT.put_line(l_clob);
END;
/


Output is:

SELECT "A1"."C1" "C1","A1"."C2" "C2" FROM  (SELECT "A2"."C1" "C1","A2"."C2" "C2"
FROM SYS."T1" "A2") "A1"


The rest is up to you though.