Forum Stats

  • 3,854,212 Users
  • 2,264,340 Discussions
  • 7,905,609 Comments

Discussions

SQL text parsing to get table and column names ?

fxmatz
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:

Input::

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 a.id=y.taby_a) b

    where a.id exists (select 1 from tablex x where x.id=a.taba_x

    /

 

Output::

  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.

Tagged:

Answers

  • 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'));
    
    <ROW>
        <A>1</A>
        <B>2</B>
        <DUMMY>X</DUMMY>
    </ROW>
    
    

    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,
               s.grade
        from emp e, dept d , (select * from salgrade)s
        where
             e.deptno = d.deptno and empno = :1' ) from dual
        )
        select
          t.*
          from   xml_parse p,
          XmlTable( '/QUERY/SELECT/SELECT_LIST/SELECT_LIST_ITEM/COLUMN_REF'
                     passing p.xml
                                            columns
                     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                                                        
    GRADE        

    But this is not the right result!


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


    Thanks


  • fxmatz
    fxmatz Member Posts: 230

    Hi,

    there are some suggestions?

    It would be wonderful.

    Best Regards

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

    Hi,

    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);
     18  
     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.