Forum Stats

  • 3,874,338 Users
  • 2,266,725 Discussions


SQL text parsing to get table and column names ?

fxmatz Member Posts: 230
edited Dec 24, 2013 2:36AM in SQL & PL/SQL

Hi Friends,

is there a solution on 11i or 12c DB's to get the table and column names of the result columns from a complex SQL text ?

Here a little demo:


select a.cola,

         b.coly   alias_b.colb,

         (select x.colx from tablex)   alias_x.colx

    from tablea a,

         (select coly from tabley y where b

    where exists (select 1 from tablex x where




  table   column


  tablea  cola

  tablex  colx

  tabley  coly

I have seen a solution a while ago ..

One possibility would be with util_xml but this goes only for non-complex SQL's.

Many thanks.



  • Ramin Hashimzadeh
    Ramin Hashimzadeh Member Posts: 1,631 Silver Trophy
    edited Dec 23, 2013 8:18AM

    where did you execute the SQL??? In dictionary "DBA_DEPENDENCIES" you can get it if you are executing SQL from procedure/function ....

    From SQL you can get column names using XML such as

    select *
          from xmltable(
                '/ROWSET/ROW' passing dbms_xmlgen.getxmltype('select 1 a, 2 b, t2.dummy from dual t1, dual t2'));

    but table names


    Ramin Hashimzade

  • Anton Scheffer
    Anton Scheffer Member Posts: 1,950 Gold Trophy

    No, I don't thing there is such a solution. But if you have seen it I must be wrong.

    What would you expect from a query like this by the way

    select col1 + col2 x from tablex

  • fxmatz
    fxmatz Member Posts: 230

    I did  execute it in SqlPlus:

    with xml_parse( xml ) as(
    select XmlParse(
       'select sysdate as day, e.*, d.*,
               (select comm from bonus) x,
        from emp e, dept d , (select * from salgrade)s
             e.deptno = d.deptno and empno = :1' ) from dual
          from   xml_parse p,
                     passing p.xml
                     table_name varchar2(30) path 'TABLE',
                     column_name varchar2(30) path 'COLUMN'
    ) t

    TABLE_NAME                COLUMN_NAME                               
    ------------------------------ ------------------------------            
    EMP                       EMPNO                                     
    EMP                       ENAME                                     
    EMP                       JOB                                       
    EMP                       MGR                                       
    EMP                       HIREDATE                                  
    EMP                       SAL                                       
    EMP                       COMM                                      
    EMP                       DEPTNO                                    
    DEPT                      DEPTNO                                    
    DEPT                      DNAME                                     
    DEPT                      LOC                                                        

    But this is not the right result!

    @Ramin you are right, but the XML path is depending from the SQL statement ..


  • fxmatz
    fxmatz Member Posts: 230


    there are some suggestions?

    It would be wonderful.

    Best Regards

  • Ramin Hashimzadeh
    Ramin Hashimzadeh Member Posts: 1,631 Silver Trophy


    Here is one idea how can you do that to take table names from SQL :

    SQL> create or replace procedure proc_parse_sql(sql_text varchar2) is
      2    s varchar2 (10000);
      3  begin
      4    s := 'create or replace procedure proc_temp is
      5            c sys_refcursor;
      6          begin
      7            open c for '||sql_text||';
      8            close c;
      9          end;';
     10    execute immediate s;
     11    for r in (select t.referenced_owner, t.referenced_name, t.referenced_type
     12                from user_dependencies t
     13                where name = 'PROC_TEMP'
     14                 and t.referenced_type = 'TABLE') loop
     15       dbms_output.put_line('referenced_owner=>' ||r.referenced_owner||
     16                            '  referenced_name=>' ||r.referenced_name||
     17                            '  referenced_type=>' ||r.referenced_type);
     19    end loop;
     20  end;
     21  /
    Procedure created
    SQL> set serveroutput on;
    SQL> exec proc_parse_sql('<your sql text here>');


    Ramin Hashimzade

This discussion has been closed.