Inserting from elements of an array without using for loop

Question

I have the following custom type:

CREATE OR REPLACE TYPE INJURED_PERSON
AS
  OBJECT
  (
    PIN VARCHAR2(7),
    FNAME VARCHAR2(20),
    LNAME VARCHAR2(20),
    PATRONYMIMC VARCHAR2(20),
    POSITIONID NUMBER(8,0),
    REPORTID NUMBER(10,0),
    OUTCOME NUMBER(1,0) );

And another custom type that serves as a table of the above type:

CREATE OR REPLACE TYPE INJURED_PEOPLE 
       AS TABLE OF INJURED_PERSON;

On the other hand I have a stored procedure that accepts only one parameter of type INJURED_PEOPLE. Inside the procedure I want to get the INJURED_PERSONs in the Input parameter and insert them to a table.I know that I can loop through the array (my input parameter) and insert one by one. But I would like to do something like this:

INSERT INTO MY_TAB SELECT something FROM MyInputParam.Pin

Is this possible in Oracle?

Dmitry Nikiforov · Accepted Answer · 2014-04-18 10:47:17Z

1

Probably you are looking for something (google "Oracle table functions"):

SQL> create table my_tab(pin VARCHAR2(7))
  2  /


SQL> declare
  2   arr INJURED_PEOPLE := INJURED_PEOPLE();
  3  begin
  4   arr.extend(2);
  5   arr(1) :=  INJURED_PERSON('APIN',null,null,null,null,null,null);
  6   arr(2) :=  INJURED_PERSON('BPIN',null,null,null,null,null,null);
  7   INSERT INTO my_tab SELECT x.PIN FROM table(arr) x;
  8  end;
  9  /

SQL> select * from my_tab;

PIN                                                                             
-------                                                                         
APIN                                                                            
BPIN

answered Apr 18, 2014 at 10:47

Dmitry Nikiforov

3,06816 silver badges14 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Inserting from elements of an array without using for loop

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related