Oracle stored procedure using array as parameter for table insert

Question

I've been searching for a clear example of this, without luck. Sorry if it's already been answered.

I'm trying to do something pretty simple - a stored procedure that will take the input and insert them into a table. I want it to take an array of multiple rows and do the insert all at once.

I thought this would be simple, but I haven't found an example that shows me.

In a lot of examples, I see people create a function to return the array - is what I have to do?

So far I have:

CREATE OR REPLACE TYPE my_type  is table of  ( name varchar2(20), phone varchar2(10));

CREATE OR REPLACE PROCEDURE customer.insert_mydata(v_my_data my_type )
AS
BEGIN

   BEGIN
 insert into mytable(Name, phone)
 values (v_my_data.name, v_my_data.phone) ; 
      COMMIT;
   END;
EXCEPTION
   WHEN OTHERS
   THEN
      ROLLBACK;
---error logging      );
END;

¿Did you find errors when using your script? Basically, the approach is similar: creating a explicit object type, then another type as a table of object_type and then, you define the in parameter with the table_of type. If you tell us about errors or failures, I'll try to help out. — Alfabravo, Oct 01 '12 at 17:16

Nick Krasnov · Answer 1 · 2012-10-02T14:18:51.080

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)
AS
BEGIN
  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 created

SQL> 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 completed

SQL> commit;

Commit complete

SQL> select * from some_table;

NAME                 PHONE
-------------------- ----------
Name                 (123)23
Name                 (123)23
Name                 (123)23
Name                 (123)23
Name                 (123)23
Name                 (123)23
Name                 (123)23
Name                 (123)23
Name                 (123)23
Name                 (123)23
Name                 (123)23

11 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;

Thanks! definitely looks like what I need. I do get this error when creating the procedure insert_mydata: PROCEDURE CUSTOMER.INSERT_MYDATA On line: 6 PLS-00436: implementation restriction: cannot reference fields of BULK In-BIND table of records — user1707389, Oct 01 '12 at 18:10
Thanks - still getting the same error using either method of insert. I'm on 10g 10.2.0.4.0. — user1707389, Oct 01 '12 at 20:06
Strange... Ok, try `For .. loop .. end construct`. I've updated the answer. — Nick Krasnov, Oct 01 '12 at 20:11
FOR..LOOP is much slower than forall or one insert because in for..loop there is a context switch between pl/sql and sql engine oracle for every value. Try a very simple solution from my answer. — user1944408, Mar 01 '13 at 00:27

score 0 · Answer 2 · answered Mar 01 '13 at 00:23

0

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

answered Mar 01 '13 at 00:23

user1944408

509
3
12

Oracle stored procedure using array as parameter for table insert

2 Answers2

Linked