Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Execute Immediate with Select Into

86c83fcd-4747-41bf-8eaf-ff0e262190efOct 17 2017 — edited Oct 17 2017

Hi All,

I am using Oracle 11G Release 2.

I created a dynamic pivot table as below where the number of columns can vary.

<CODE>

declare

l_sql varchar2(32767);

i number;

var  varchar2(10);

var1  varchar2(10);

var2  varchar2(10);

BEGIN

i:=1;

l_sql := 'SELECT field1';

for rec in (SELECT FIELD1 FROM TBL1)

LOOP

l_sql:= l_sql  || ',' || VALUE1;

i:=i+1;

END LOOP;

l_sql:= l_sql || ' from dual ';

if i=2 then            

execute immediate l_sql_0 into var,var1;

elsif i=3 then

execute immediate l_sql_0 into var,var1,var2;

end if;

END;

</CODE>

However, I need to save the results into a list of variables which will vary according to the number of columns returned. My execute immediate is not scalable and the query becomes unwieldy such as in a situation where 50 or 100 columns are returned. How can I make the execute immediate command scalable or generate the variable list dynamically?

Kindly note that the variable list is made up of bind variables (oracle apex page items) which I need to pass the results to so I cannot use ref cursors . I appreciate any assistance I can get that will point me in the right direction.

Thank you.

Message was edited by: 86c83fcd-4747-41bf-8eaf-ff0e262190ef

Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Nov 14 2017
Added on Oct 17 2017
4 comments
4,843 views