CODEWITHSUNDEEP
sqloracle-databaseplsql
darshanUser
darshanUser

Reputation: 45

Fetching variable columns in PLSQL

Here is an example of PLSQL code where I am using select query to fetch values of two columns into two variables.

execute immediate 'select c1,c2 from tableName where id = 123' into c1_val,c2_val;

I want to make above code generic where the column names will be given as input. Something like 

execute immediate 'select '||commaSeparatedColumns||' from tableName where id = 123' into <someThing>

Here, I am facing problem with storing the values in pl sql variables as the number of columns are not fixed.

Can someone plz suggest how to achieve this ?

Upvotes: 0

Views: 324

Answers (2)

Aleksej
Aleksej

Reputation: 22949

You can try something like that; it's a bit tricky, based on a sort of nested dynamic sql:

create table test_fetch_table ( id number,c1 number,c2 number,c3 number,c4 number,c5 number,c6 number,c7 number,c8 number,c9 number,c10 number) 

insert into test_fetch_table values ( 123, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10); 

create or replace procedure testFetch (commaSeparatedColumns IN varchar2) is
    c1_val  number;
    c2_val  number;
    c3_val  number;
    c4_val  number;
    c5_val  number;
    c6_val  number;
    c7_val  number;
    c8_val  number;
    c9_val  number;
    c10_val number;
    commaSeparatedVariables varchar2(1000);
    vSQL                    varchar2(1000);
    vPLSQL                  varchar2(1000);
begin
    commaSeparatedVariables := replace (commaSeparatedColumns, ',', '_val,') || '_val';
    vSQL := 'select ' || commaSeparatedColumns || ' into ' || commaSeparatedVariables || ' from test_fetch_table where id=123;';
    vPLSQL := 'declare
                    c1_val  number;
                    c2_val  number;
                    c3_val  number;
                    c4_val  number;
                    c5_val  number;
                    c6_val  number;         
                    c7_val  number;
                    c8_val  number;
                    c9_val  number;
                    c10_val number;    
                begin '
               || vSQL ||               
               ':1  := c1_val;
                :2  := c2_val;
                :3  := c3_val;
                :4  := c4_val;
                :5  := c5_val;
                :6  := c6_val;         
                :7  := c7_val;
                :8  := c8_val;
                :9  := c9_val;
                :10 := c10_val;
                end;';
    execute immediate vPLSQL using OUT c1_val,
                                   OUT c2_val,
                                   OUT c3_val,
                                   OUT c4_val,
                                   OUT c5_val,
                                   OUT c6_val,
                                   OUT c7_val,
                                   OUT c8_val,
                                   OUT c9_val,
                                   OUT c10_val;
    dbms_output.put_line(
             c1_val || ',' ||
             c2_val || ',' ||
             c3_val || ',' ||
             c4_val || ',' ||
             c5_val || ',' ||
             c6_val || ',' ||
             c7_val || ',' ||
             c8_val || ',' ||
             c9_val || ',' ||
             c10_val );       
end;

begin
  testFetch('c1,c2,c3,c4');
end;

In this way you define a sort of interface with 10 fields, but only fetch the fields you need, based on commaSeparatedColumns

Upvotes: 1

Tony Andrews
Tony Andrews

Reputation: 132570

You cannot do this with execute immediate, you will need to use the dbms_sql package.

Here is a good example you can start from - Tom Kyte's print_table script that can run any select statement (with any number of columns) and print the results in a vertical format.

Upvotes: 3

Related Questions