oracle sql dynamically select columns using a parameter table

1
votes

I have a table in oracle with 10 columns, say Table A with col_1, col_2, col_3 etc. I have another table, Table B with rows that has column names from table A co1_1, col_2, col_3. The rows in Table B can vary. 

TABLE A

COL1 COL2 COL3 COL4 COL5 COL6 COL7 COL8 COL9 COL10 

TABLE B
COL1
COL2
COL3

I want to write an oracle sql query which dynamically gets the select columns names based on the column names(rows) in table B.

If table B has 3 rows with corresponding column names then my query should look like this

select col_1, col_2, col_3 from A

If table B has 4 rows then my query should dynamically change to below 

select col_1, col_2, col_3, col_4  from A
3
sqloracledynamic-sql
Can you give more information about the context of this query? If this is for an application, it would be easiest to generate the query from the application, or possibly use a stored procedure that returns a dynamic cursor. If you need this to be done purely in SQL, it's possible but trickier. – Jon Heller
Hi Jon, my goal is to dynamically generate data set based on columns present in another table. I am not very familiar with plsql and I am using an application which supports sql code. At the moment, I am trying to implement the dynamic sql as suggested by Tejash inside a plsql code/function in the with clause (oracle 12c enhancement) and achieve what i want...Could you please let me know if this approach works? and guide me in the right direction. – user1751356
See my answer below. – Jon Heller

3 Answers

2
votes

You need to use the dynamic query.

'SELECT '
|| (SELECT LISTAGG(COLNAME, ',') WITHIN GROUP (ORDER BY COLNAME) FROM TABLEB)
|| ' FROM TABLEA'
0
votes

I think we should go to ALL_TAB_COLUMNS table for finding column names. Like this.

SELECT 
    'SELECT ' 
     ||
    (SELECT  LISTAGG( y.COLNAME, ',') WITHIN GROUP (ORDER BY Y.COLNAME)
     FROM TABLE_B x,ALL_TAB_COLUMNS y
     where x.COLNAME=Y.COLUMN_NAME )  
     || 
     ' FROM Table_A' script 
FROM DUAL;
0
votes

A ref cursor can be used to created dynamic columns. Many languages and applications support ref cursors, and if you add some details about your system someone may know exactly how to integrate them in your environment.

Below is a simple example of how to create a function that returns a ref cursor. How to call it depends on your system.

Sample Schema

--drop table a;
--drop table b;

create table a as
select 1 col1, 2 col2, 3 col3, 4 col4, 5 col5, 6 col6, 7 col7, 8 col8, 9 col9, 10 col10
from dual;

create table b as
select 'COL1' column_name from dual union all
select 'COL2' column_name from dual union all
select 'COL3' column_name from dual;

Function

create or replace function get_dynamic_results return sys_refcursor is
    v_cursor sys_refcursor;
    v_column_list varchar2(4000);
begin
    --Split this into multiple SELECTS if you get an error like:
    -- ORA-01489: result of string concatenation is too long error
    select listagg(column_name, ',') within group (order by column_name) columns
    into v_column_list
    from b;

    open v_cursor for 'select '||v_column_list||' from a';

    return v_cursor;
end;
/