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;

No comments:

Post a Comment