SQL text parsing to get table and column names ?

fxmatz · Dec 23, 2013 7:50AM

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 · Dec 23, 2013 8:16AM

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 · Dec 23, 2013 9:07AM

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 · Dec 23, 2013 11:05AM

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 · Dec 23, 2013 6:13PM

Hi,

there are some suggestions?

It would be wonderful.

Best Regards

Ramin Hashimzadeh · Dec 24, 2013 2:36AM

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

Forum Stats

Quick Links

Categories

SQL text parsing to get table and column names ?

Answers

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
/

Data

Databases

Development

Development Tools

Open Source

Programs

Forum Stats

Categories

SQL text parsing to get table and column names ?

Answers

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/

with xml_parse( xml ) as(
select XmlParse(

**'select sysdate as day, e., d.,**

**from emp e, dept d , (select * from salgrade)s**

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
/