create or replace TYPE m1 force AS OBJECT
(tno number, tname varchar2(10));
create or replace TYPE m1_tt
AS TABLE OF m1;
create table test (tno number, tname varchar2(10));
declare
a number:= 1;
b varchar2(10):= 1;
ty m1_tt;
tem varchar2(100) := 'ty(i).tno, ty(i).tname';
begin
ty := m1_tt();
ty.extend;
ty(1) := m1(10,10);
for i in 1 .. ty.count loop
execute immediate 'insert into test values('||ty(i).tno||','|| ty(i).tname||')';
end loop;
end;
the above piece of code is working.
But, collection variables can be limited, i mean, the table columns can be limited, less or more.
i need to frame the dynamic sql based on the list of collection variables.
collection variables can be vary. more or less.
Example:
execute immediate 'insert into test values(ty(i).tno, ty(i).tname)'
or
execute immediate 'insert into test values(:1, :2)' using ‘ty(i).tno, ty(i).tname’
Any suggestions please..