I have one procedure within a procedure I have to create a table and insert into that table, insertion values are from associative array,
how do i perform this by using dynamic sql. Please suggest me. Thanks in Advance :)
If the list of values is small, you can easily just loop around the array and insert the values row at a time, eg
SQL> declare
2 type numlist is table of number index by pls_integer;
3 n numlist;
4 begin
5 execute immediate 'create table t ( x int )';
6 for i in 1 .. 10 loop
7 n(i) := i;
8 end loop;
9
10 for i in 1 .. n.count loop
11 execute immediate 'insert into t values (:val)' using n(i);
12 end loop;
13 end;
14 /
PL/SQL procedure successfully completed.
SQL>
SQL> select * from t;
X
----------
1
2
3
4
5
6
7
8
9
10
10 rows selected.
But if the array is large, you would probably want to use PLSQL to dynamically write some PLSQL. In that situation, we probably need to know the type definition in advance, eg
SQL> drop table t purge;
Table dropped.
SQL>
SQL> create or replace
2 package pkg is
3 type numlist is table of number index by pls_integer;
4 end;
5 /
Package created.
SQL>
SQL>
SQL> declare
2 n pkg.numlist;
3 begin
4 execute immediate 'create table t ( x int )';
5 for i in 1 .. 10 loop
6 n(i) := i;
7 end loop;
8
9 execute immediate
10 'declare
11 x pkg.numlist := :val;
12 begin
13 forall i in 1 .. x.count
14 insert into t values (x(i));
15 end;
16 ' using n;
17 end;
18 /
PL/SQL procedure successfully completed.