Variable column name in Oracle SQL using dbms_xmlgen

Consider scenario where we need to use dynamic column name in query.
You might have tried using select query in place of column list something like

select (select distinct column_name from table1 where column_name2=’some_value‘) from table2

Based on some condition we will select column name from one table(test3 table here) and query another table(test 4) for the fetched column.

Consider table test4 with following data

create table test4(c1 varchar2(20), c2 varchar2(20), c3 varchar2(20))

c1                  c2         c3             c4
-----------------------------------------------
col1_val   col2_val   col3_val   col4_val

And another table test3 with column names of test4  as rows here.

 create table test3(col_name varchar2(2), flag number)
col_name   flag
-------------------
c1                0
c2                1
c3                0
based on flag value we want to select the column name. We want to achieve something like
select (select col_name from test3 where flag = 1) from test4  [this doesn't works]
We will use oracle xml functions getxml and extractvalue to achieve result. dbms_xmlgen.getxml takes SQL query as its argument and returns result in xml format.This function allows us to dynamically create sql query, we can use dynamic column or table name in it.
 select to_char (extractvalue (xmltype (dbms_xmlgen.getxml('select '||
    to_char(extractvalue(xmltype(dbms_xmlgen.getxml('select col_name  from test3 where flag =1')),'/ROWSET/ROW/COL_NAME')) || ' col from test4'
 )),'/ROWSET/ROW/COL')) col_value from dual
About these ads

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

%d bloggers like this: