CODEWITHSUNDEEP
arraysoracle-databaseparameters
user1707389
user1707389

Reputation: 99

Oracle stored procedure using array as parameter for table insert

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;

Upvotes: 3

Views: 36631

Answers (2)

user1944408
user1944408

Reputation: 569

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

Upvotes: 1

Nick Krasnov
Nick Krasnov

Reputation: 27251

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;

Upvotes: 13

Related Questions