CODEWITHSUNDEEP
sqloracle-databaseselectplsqldynamic-sql
Mithil
Mithil

Reputation: 19

How to dynamically pass column names to a query?

Below is my problem and desired solution.

Query1:

Select colnames from table1;

Query1 Result:
 col1
 col2
 col3
 col4

Query2:

Select a1.* 
  from table2 a1;

-- should translate to
select a1.col1, a1.col2, a1.col3, a1.col4 from table2 a1;

My first query will give the list of column names, I need to replace the .* with those column names in my second query. How can I achieve this?

Upvotes: 2

Views: 313

Answers (1)

GMB
GMB

Reputation: 222672

You are looking for dynamic SQL. The idea is to generate the query string from the results of a SQL query. You can then run it with execute immediate.

In your use case, that would look like:

declare
    p_sql varchar2(100);
begin
    select 
        'select ' 
        || listagg('a1.' || colnames, ', ') within group(order by colnames)
        || ' from table2 a1'
    into p_sql
    from table1;

    dbms_output.put_line('sql: ' || p_sql);  -- debug
    execute immediate p_sql;                 -- execute
end;
/

For your sample data, this generates:

dbms_output:
sql: select a1.col1, a1.col2, a1.col3, a1.col4 from table2 a1

Upvotes: 2

Related Questions