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