Get the tablename and columns selected using a SELECT query

Question

I have a requirement where in I have queries in the form:

select tab1.col1 as column1, tab2.col1 as column1 
from table1 tab1 inner join table2 tab2 on tab1.key1=tab2.key2

I would like to get the three things from this:

table1|col1|column1
table2|col2|comumn2

Also with this if possible I would like to fetch the datatype of the selected columns.

I know this can be done manually, but I have over 2000 select queries and each of these queries are fetching over 100 fields. So the job is getting boring and time consuming.

Any ideas on how I can automate this. I have oracle SQL developer and can even write UNIX shell script if it would serve my job.

Are all the queries this simple? No subqueries, no inline queries, no case-sensitive identifiers, etc? Where, exactly, do you have the queries? Is there a table of SQL statements? Or do you need to parse the SQL statements out of stored procedures? — Justin Cave, Feb 26 '14 at 18:43
No queries are not this simple. There are multiple joins and where conditions, but that shouldn't bother us as we need only the fields in select clause. In rare cases there are a few functions like to_char or upper in the select clause of the queries. All these queries are stored in a directory in UNiX in separate sql files( one query per file) — user3357241, Feb 26 '14 at 18:48
Well, you appear to want the table name from the `FROM` clause so you would need to parse that. What subset of SQL do you need to support? Inline queries? Subqueries in the join? Multiple layers of aliased subqueries? — Justin Cave, Feb 26 '14 at 18:51
I don't see any inline queries and subqueries in here. The queries are simple but huge though. Yes my problem here is how do I get the table name from the FROM clause once i know the alias and how do i find the original column name as well. I thought of creating a temporary table using the select statement which would create a table with aliased column names and datatypes. But here I lose the original tablename and column name. — user3357241, Feb 26 '14 at 18:58

score 0 · Answer 1 · answered Mar 12 '14 at 12:47

you can use dbms_sql to get a description of the resulting columns.. but it will not tell you which table it came from.

but with dbms_sql you can open cursor parse the sql statement using dbms_sql.parse()

you can then get the resulting column definitions using DBMS_SQL.DESCRIBE_COLUMNS3

example

set serveroutput on
DECLARE
  c INTEGER;
  COL_CNT NUMBER;
  DESC_T SYS.DBMS_SQL.DESC_TAB3;
  col_desc sys.dbms_sql.desc_rec3;
BEGIN
  c := dbms_sql.open_cursor;
  dbms_sql.parse(c,'select * from dual', dbms_sql.NATIVE);

  DBMS_SQL.DESCRIBE_COLUMNS3(
      C => C,
      COL_CNT => COL_CNT,
      DESC_T => DESC_T
   );
  DBMS_OUTPUT.PUT_LINE('COL_CNT = ' || COL_CNT);

  FOR i IN   DESC_T.FIRST .. DESC_T.LAST LOOP
    col_desc := DESC_T(i);
    DBMS_OUTPUT.PUT_line(col_desc.col_name);
  END LOOP;
  SYS.DBMS_SQL.CLOSE_CURSOR(c);
END;
/

Get the tablename and columns selected using a SELECT query

1 Answers1