BULK INSERT cursor’s data INTO A TABLE – Oracle10g, 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));
