Archive for Remote DBA

Data movement using cursor and bulk insert – Oracle 11g

Posted in Oracle, Uncategorized with tags , , , , , , , on August 22, 2011 by DBAWork

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