Systems Engineering and RDBMS

FORALL to insert collection records

Posted by decipherinfosys on May 1, 2009

In yesterday’s blog post, we showed how to use a collection of records to store the result sets. In today’s blog post, we will extend the example to insert records into a table using a collection of records. First of all let us create the table.

SQL> create table test as select * from all_objects where 1 = 0;

Table created.

We have created test table, which is exact replica of all_objects but without any data.  We will use following PL/SQL block to populate test table.

SET SERVEROUTPUT ON
DECLARE

TYPE testRecTab IS TABLE OF all_objects%ROWTYPE INDEX BY PLS_INTEGER;
test_recs testRecTab;

BEGIN

SELECT *
BULK COLLECT INTO test_recs
FROM all_objects;

dbms_output.put_line(‘No. of Objects: ‘ || test_recs.count);

IF test_recs.count > 0 THEN
BEGIN
FORALL i in test_recs.first..test_recs.last
INSERT INTO test values test_recs(i);
EXCEPTION
WHEN OTHERS THEN
Rollback;
dbms_output.put_line(SQLERRM);
END;
COMMIT;
END IF;

END;
/

Here is the output.

No. of Objects: 55910
Rows inserted: 55910

PL/SQL procedure successfully completed.

This is very useful when we are dealing with all the columns of the table. Code looks very precise, neat and clean and does the job with speed. One thing we have to remember and make sure that both tables are exactly same with same column order otherwise you can run into an error.
Now let us check the scenario where we don’t want to deal with all the columns. Here in our example, we want to populate only not null columns (owner,object_name,object_id,created, last_ddl_time). Please look at the definition of test table. In this case, we have to use specific column or attribute of the record.

Here is the code:

SET SERVEROUTPUT ON
DECLARE

TYPE testRecTab IS TABLE OF all_objects%ROWTYPE INDEX BY PLS_INTEGER;
test_recs testRecTab;

BEGIN

SELECT *
BULK COLLECT INTO test_recs
FROM all_objects;

dbms_output.put_line(‘No. of Objects: ‘ || test_recs.count);

IF test_recs.count > 0 THEN
BEGIN
FORALL i in test_recs.first..test_recs.last
INSERT INTO TEST
(
OWNER,OBJECT_NAME,OBJECT_ID,
CREATED,LAST_DDL_TIME,NAMESPACE
)
VALUES
(
test_recs(i).OWNER, test_recs(i).OBJECT_NAME,  test_recs(i).OBJECT_ID,
test_recs(i).CREATED, test_recs(i).LAST_DDL_TIME, test_recs(i).NAMESPACE
);

EXCEPTION
WHEN OTHERS THEN
rollback;
dbms_output.put_line(SQLERRM);
END;

dbms_output.put_line(‘Rows inserted: ‘ || SQL%ROWCOUNT);
COMMIT;

END IF;

END;
/

Above code will run into following implementation restriction error if you are running it for version 10g or below.

PLS-00436: implementation restriction: cannot reference fields of BULK In-BIND

If you test it in version 10g or below, please remove NAMESPACE column from the code as it is introduced in 11g. But in 11g, that limitation is removed so above code will work without any error in 11g. Here is the output when PL/SQL block was run successfully in 11g.

No. of Objects: 55910
Rows inserted: 55910

PL/SQL procedure successfully completed.

For version 10g and below, there is a workaround to avoid PLS-00436 error. Adrian Billington has demonstrated nicely in his article here.

Resources:

  • Oracle 11g PL/SQL Reference Manual – here.
  • Adrian Billington blog post – here.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
%d bloggers like this: