Data movement using cursor and bulk insert – Oracle 11g


If you want to store a collection of data from a cursor into a table, the “BULK COLLECT INTO, FORALL INSERT” in Oracle is a friend. Instead of iterating row-by-row through the cursor, BULK processes data in a batch mode at the background.

The syntax is a bit different in Oracle 10g and 11g. In the examples below, we will insert all the data from cursor into a table

CREATE TABLE MYTEST
( ”OBJECT_NAME” VARCHAR2(128 BYTE),
“OBJECT_ID” NUMBER,
“CREATED” DATE
);

The result set in the cursor has the same columns as the target table. Therefore, in 11g, we can simply code like

TYPE myrecord_tab_type IS TABLE OF MYTEST%ROWTYPE;
myrecord_tab myrecord_tab_type;

FETCH ref_cursor BULK COLLECT INTO myrecord_tab;

FORALL i IN 1 .. myrecord_tab.count
INSERT
INTO mytest
VALUES myrecord_tab(i);

In 10g, the code needs to be different and needs to use DBMS_SQL.XXXX_TABLE.

TYPE myrecord_tab_type IS RECORD
(
NAME DBMS_SQL.VARCHAR2_TABLE,
ID DBMS_SQL.NUMBER_TABLE,
DATE DBMS_SQL.DATE_TABLE
);
myrecord_tab myrecord_tab_type;

FETCH ref_cursor BULK COLLECT INTO myrecord_tab.name, myrecord_tab.id, myrecord_tab.date;

FORALL i IN 1 .. myrecord_tab.name.count
INSERT
INTO mytest(object_name, object_id, created)
VALUES (myrecord_tab.name(i), myrecord_tab.id(i), myrecord_tab.date(i));

Remote Oracle Support,DBAWorkDatabase support, Database services, Oracle ADF

Advertisements

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: