Oracle stored procedure using array as parameter for table insert

Here is an example of want you want to achieve.

Lets create an object type which will contain name and phone attributes like so.

CREATE OR REPLACE TYPE my_object as object(   name varchar2(20),   phone varchar2(10));

Now lets create a collection which elements are of my_object type:

CREATE OR REPLACE TYPE my_table is table of my_object;

And now our procedure that will insert into a particular table the data passed in as a collection:

CREATE OR REPLACE PROCEDURE insert_mydata(v_my_data my_table)ASBEGIN  forall i in 1..v_my_data.count    insert into Some_Table(name, phone)         --       values(v_my_data(i).name, v_my_data(i).phone);END;

Demonstration:

SQL> create table Some_table(  2    name varchar2(20),  3    phone varchar2(10)  4  );Table createdSQL> select * from some_table;NAME                 PHONE-------------------- ----------SQL> SQL> declare  2    l_col_data my_table;  3  begin  4    select my_object('Name'  5                  ,  '(123)23') bulk collect into l_col_data  6      from dual  7    connect by level <=11;  8    9    insert_mydata(l_col_data); 10  end; 11  /PL/SQL procedure successfully completedSQL> commit;Commit completeSQL> select * from some_table;NAME                 PHONE-------------------- ----------Name                 (123)23Name                 (123)23Name                 (123)23Name                 (123)23Name                 (123)23Name                 (123)23Name                 (123)23Name                 (123)23Name                 (123)23Name                 (123)23Name                 (123)2311 rows selected

Answer to the comment

I suppose your Oracle version is prior 11g. So, To get around this error(PLS-00436 "implementation restriction".) you can insert data using in-line view:

  forall i in 1..v_my_data.count    insert into (select name, phone from some_table) <--      values(v_my_data(i).name, v_my_data(i).phone);

Or try not to specify column names of the table in the insert statement if number of columns of the table you are inserting into and inserted values are the same:

  forall i in 1..v_my_data.count    insert into some_table                           <--      values(v_my_data(i).name, v_my_data(i).phone);

OR use the FOR .. LOOP.. END LOOP construct:

  for i in 1..v_my_data.count  loop    insert into Some_Table(name, phone)         --       values(v_my_data(i).name, v_my_data(i).phone);  end loop; 

insert into mytable(Name, phone)select name, phonefrom TABLE(v_my_data);