Get list of columns and alias name by a query string in Oracle

Question

I'm using Pl/SQL with Oracle Database 11g.

I want to get a list of columns with alias by a query string. There is a way to get all the column names of a query, using dbms_sql.describe_columns2 but only I get alias.

For example:

DECLARE
    l_cursor NUMBER := dbms_sql.open_cursor;
    l_ignore NUMBER;
    l_desc dbms_sql.desc_tab2;
    l_cnt NUMBER;
BEGIN
    dbms_sql.parse( l_cursor, 'select a.col1 column1, a.col2 column2 from table_test a', dbms_sql.native );
    dbms_sql.describe_columns2( l_cursor, l_cnt, l_desc );
    FOR i IN 1 .. l_cnt LOOP
        dbms_output.put_line(l_desc(i).col_name);
    END LOOP;
    dbms_sql.close_cursor( l_cursor );
END;
/

Returns:

column1
column2

Is there any way to get the values a.col1, or a.col2 with alias in the query?

Not with dbms_sql. You might have some luck with regexp_substr, however. Why do you need the underlying column names, rather than the column names as they've been aliased to in the query? And what would you want to see if the query was more complicated, e.g. with a subquery along the lines of select c.col1, c.col2 from (select a.col1, b.col2 from table1 a inner join table2 b on a.col3 = b.col3) c? — Boneist
– Boneist, Commented Dec 22, 2017 at 10:26
Most probably you will need to write your own parser for this purpose. But I'm curious - what do you need it for? — Marcin Wroblewski
– Marcin Wroblewski, Commented Dec 22, 2017 at 10:26
I don't think this is possible. Imagine you have a query like SELECT a.col1 || a.col2 AS ... or SELECT CASE col1 when col2 then col3 else col4 end AS .... Which column names would you expect? — Wernfried Domscheit
– Wernfried Domscheit, Commented Dec 22, 2017 at 10:34

Jon Heller · Accepted Answer · 2017-12-23 06:43:07Z

The column names used in a SQL statement can be retrieved using Rob van Wijk's custom view DBA_DEPENDENCY_COLUMNS.

Install the view as SYS and grant select on dba_dependency_columns to <your user>;.

The view only works on objects. Create a VIEW on the SELECT statement and then the dependencies will be available. For example:

create table table_test(col1 number, col2 number);

create or replace view test_view as
select a.col1 column1, a.col2 column2 from table_test a;

select referenced_column
from sys.dba_dependency_columns
where owner = user
    and name = 'TEST_VIEW'
order by 1;

Results:

REFERENCED_COLUMN
-----------------
COL1
COL2

The above results get the "list of columns". But part of your answer also implies you may want to get the "column expressions". That would be a completely different task, but is possible. Parsing SQL can be ridiculously difficult so it might help to explain exactly what you want and why you want it.

Collectives™ on Stack Overflow

Get list of columns and alias name by a query string in Oracle

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related