Dynamic column name in Oracle SQL Query 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

[sourcecode language="sql"]
create table test4(c1 varchar2(20), c2 varchar2(20), c3 varchar2(20))
[/sourcecode]

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

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

[sourcecode language="sql"]
create table test3(col_name varchar2(2), flag number)
[/sourcecode]

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.

[sourcecode language="sql"]
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[/sourcecode]