Skip to Main Content
  • Questions
  • create and insert table in same procedure

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Ningappa.

Asked: March 10, 2017 - 5:50 am UTC

Last updated: March 10, 2017 - 7:48 am UTC

Version: 3.0

Viewed 10K+ times! This question is

You Asked

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 :)

and Connor said...

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.




Is this answer out of date? If it is, please let us know via a Comment

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library