Fetching variable columns in PLSQL

Question

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 ?

Are they limmited? or they can be infinity number of columns — sagi
– sagi, Commented Jan 25, 2016 at 12:55
Well , I'm not sure if there's a better way(probably is) but you can use a count variable, and then use a CASE into the variables > CASE WHEN COUNT = 2 then c1_val,c2_val,else CASE WHEN COUNT = 3 ...... — sagi
– sagi, Commented Jan 25, 2016 at 13:00
I got your point .. thank u.. It'll be useful if no better way is suggested.. — darshanUser
– darshanUser, Commented Jan 25, 2016 at 13:05

Tony Andrews · Accepted Answer · 2016-01-25 13:00:36Z

3

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.

answered Jan 25, 2016 at 13:00

Tony Andrews

133k23 gold badges230 silver badges266 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Aleksej · Accepted Answer · 2016-01-25 14:04:07Z

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

Collectives™ on Stack Overflow

Fetching variable columns in PLSQL

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related