SQL text parsing to get table and column names ?

fxmatz

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.

Ramin Hashimzadeh

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

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

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

Hi,

there are some suggestions?

It would be wonderful.

Best Regards

Ramin Hashimzadeh

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