Showing posts with label ORACLE BULK COLLECT INSERT RETURNING PRIMARY KEY. Show all posts
Showing posts with label ORACLE BULK COLLECT INSERT RETURNING PRIMARY KEY. Show all posts

Friday, September 23, 2011

Oracle FORALL Insert RETURNING PRIMARY KEY VALUE

Requirement:
I have a list of values in an Oracle collection Object which I want insert into a table. The table has a primary key column value which will be populated from a Sequence. Once these Primary Keys are populated I need those Primary Key values to insert into another table to create an association/relationship.

Solution:

CREATE TABLE test (test_id VARCHAR2(50), test_name VARCHAR(50), CONSTRAINT t_pk PRIMARY KEY (test_id));
CREATE SEQUENCE test_tbl_pk_seq;

Here is the PL/SQL Block to get the requirement done.

DECLARE
TYPE TBL_TYPE IS TABLE OF VARCHAR2(50);
NAME_TBL TBL_TYPE;
PK_TBL TBL_TYPE;
BEGIN
name_tbl := NEW tbl_type();
pk_tbl := NEW tbl_type();

FOR i IN 1..10
LOOP
name_tbl.EXTEND;
name_tbl(i) := 'NAME-'||i;
END LOOP;

FORALL i IN 1..name_tbl.LAST
INSERT INTO test (test_id, test_name) VALUES ('T-'||test_tbl_pk_seq.NEXTVAL, name_tbl(i)) RETURNING test_id BULK COLLECT INTO pk_tbl;

FOR i IN 1..pk_tbl.LAST
LOOP
DBMS_OUTPUT.PUT_LINE(pk_tbl(i));
END LOOP;
END;