Oracle Bulk Collection FORALL Bind


	CODE Oracle Code Library


	JOBS Find Or Post Oracle Jobs


	FORUM Oracle Discussion & Chat

Search the Reference Library pages:

Free
Oracle Magazine
Subscriptions
and Oracle White Papers

Oracle Bulk Collection & FORALL

Version 11.1

Array Processing

Note: Without the bulk bind, PL/SQL sends a SQL statement to the SQL engine for each record that is inserted, updated, or deleted leading to context switches that hurt performance.

BULK COLLECT

BULK COLLECT Syntax

FETCH BULK COLLECT <cursor_name> BULK COLLECT INTO <collection_name>
LIMIT <numeric_expression>;

or

FETCH BULK COLLECT <cursor_name> BULK COLLECT INTO <array_name>
LIMIT <numeric_expression>;

set timing on

DECLARE
CURSOR a_cur IS
SELECT program_id
FROM airplanes;
BEGIN
FOR cur_rec IN a_cur LOOP
    NULL;
END LOOP;
END;
/

DECLARE
CURSOR a_cur IS
SELECT program_id
FROM airplanes;

TYPE myarray IS TABLE OF a_cur%ROWTYPE;
cur_array myarray;
BEGIN
OPEN a_cur;
LOOP
    FETCH a_cur BULK COLLECT INTO cur_array LIMIT 100;
    EXIT WHEN a_cur%NOTFOUND;
END LOOP;
CLOSE a_cur;
END;
/

DECLARE
CURSOR a_cur IS
SELECT program_id
FROM airplanes;

TYPE myarray IS TABLE OF a_cur%ROWTYPE;
cur_array myarray;
BEGIN
OPEN a_cur;
LOOP
    FETCH a_cur BULK COLLECT INTO cur_array LIMIT 500;
    EXIT WHEN a_cur%NOTFOUND;
END LOOP;
CLOSE a_cur;
END;
/

DECLARE
CURSOR a_cur IS
SELECT program_id
FROM airplanes;

TYPE myarray IS TABLE OF a_cur%ROWTYPE;
cur_array myarray;
BEGIN
OPEN a_cur;
LOOP
    FETCH a_cur BULK COLLECT INTO cur_array LIMIT 1000;
    EXIT WHEN a_cur%NOTFOUND;
END LOOP;
CLOSE a_cur;
END;
/

-- try with a LIMIT clause of 2500, 5000, and 10000. What do you see?

FORALL

FORALL Syntax

FORALL <index_name> IN <lower_boundary> .. <upper_boundary>
<sql_statement>
SAVE EXCEPTIONS;

FORALL <index_name> IN INDICES OF <collection>
[BETWEEN <lower_boundary> AND <upper_boundary>]
<sql_statement>
SAVE EXCEPTIONS;

FORALL <index_name> IN INDICES OF <collection>
VALUES OF <index_collection>
<sql_statement>
SAVE EXCEPTIONS;

FORALL Insert

CREATE TABLE servers2 AS
SELECT *
FROM servers
WHERE 1=2;

DECLARE
CURSOR s_cur IS
SELECT *
FROM servers;

TYPE fetch_array IS TABLE OF s_cur%ROWTYPE;
s_array fetch_array;
BEGIN
OPEN s_cur;
LOOP
    FETCH s_cur BULK COLLECT INTO s_array LIMIT 1000;

    FORALL i IN 1..s_array.COUNT
    INSERT INTO servers2 VALUES s_array(i);

    EXIT WHEN s_cur%NOTFOUND;
END LOOP;
CLOSE s_cur;
COMMIT;
END;
/

FORALL Update

SELECT DISTINCT srvr_id
FROM servers2
ORDER BY 1;

DECLARE
TYPE myarray IS TABLE OF servers2.srvr_id%TYPE
INDEX BY BINARY_INTEGER;

d_array myarray;
BEGIN
d_array(1) := 608;
d_array(2) := 610;
d_array(3) := 612;

FORALL i IN d_array.FIRST .. d_array.LAST
UPDATE servers2
SET srvr_id = 0
WHERE srvr_id = d_array(i);

COMMIT;
END;
/

SELECT srvr_id
FROM servers2
WHERE srvr_id = 0;

FORALL Delete

set serveroutput on

DECLARE
TYPE myarray IS TABLE OF servers2.srvr_id%TYPE
INDEX BY BINARY_INTEGER;

d_array myarray;
BEGIN
d_array(1) := 614;
d_array(2) := 615;
d_array(3) := 616;

FORALL i IN d_array.FIRST .. d_array.LAST
DELETE servers2
WHERE srvr_id = d_array(i);

COMMIT;

FOR i IN d_array.FIRST .. d_array.LAST LOOP
dbms_output.put_line('Iteration #' || i || ' deleted ' ||
SQL%BULK_ROWCOUNT(i) || ' rows.');
END LOOP;
END;
/

SELECT srvr_id
FROM servers2
WHERE srvr_id IN (614, 615, 616);

Performance Demos

Performance Comparison

CREATE TABLE t1 (pnum INTEGER, pname VARCHAR2(15));
CREATE TABLE t2 AS SELECT * FROM t1;

CREATE OR REPLACE PROCEDURE perf_compare(iterations PLS_INTEGER) IS

TYPE NumTab IS TABLE OF t1.pnum%TYPE INDEX BY PLS_INTEGER;
TYPE NameTab IS TABLE OF t1.pname%TYPE INDEX BY PLS_INTEGER;
pnums NumTab;
pnames NameTab;

a INTEGER;
b INTEGER;
c INTEGER;
BEGIN
FOR j IN 1..iterations LOOP -- load index-by tables
    pnums(j) := j;
    pnames(j) := 'Part No. ' || TO_CHAR(j);
END LOOP;

a := dbms_utility.get_time;

FOR i IN 1..iterations LOOP -- use FOR loop
    INSERT INTO t1 VALUES (pnums(i), pnames(i));
END LOOP;

b := dbms_utility.get_time;

FORALL i IN 1 .. iterations -- use FORALL statement
INSERT INTO t2 VALUES (pnums(i), pnames(i));

c := dbms_utility.get_time;

dbms_output.put_line('Execution Time (secs)');
dbms_output.put_line('---------------------');
dbms_output.put_line('FOR loop: ' || TO_CHAR((b - a)/100));
dbms_output.put_line('FORALL: ' || TO_CHAR((c - b)/100));
COMMIT;
END perf_compare;
/

set serveroutput on

exec perf_compare(500);
exec perf_compare(5000);
exec perf_compare(50000);

Bulk Collection Demo Table

CREATE TABLE parent (
part_num NUMBER,
part_name VARCHAR2(15));

CREATE TABLE child AS
SELECT *
FROM parent;

Create And Load Demo Data

DECLARE
j PLS_INTEGER := 1;
k parent.part_name%TYPE := 'Transducer';
BEGIN
FOR i IN 1 .. 200000
LOOP
    SELECT DECODE(k, 'Transducer', 'Rectifier',
    'Rectifier', 'Capacitor',
    'Capacitor', 'Knob',
    'Knob', 'Chassis',
    'Chassis', 'Transducer')
    INTO k
    FROM DUAL;

    INSERT INTO parent VALUES (j+i, k);
END LOOP;
COMMIT;
END;
/

SELECT COUNT(*) FROM parent;
SELECT COUNT(*) FROM child;

Slow Way

CREATE OR REPLACE PROCEDURE slow_way IS

BEGIN
FOR r IN (SELECT * FROM parent)
LOOP
    -- modify record values
    r.part_num := r.part_num * 10;
    -- store results
    INSERT INTO child
    VALUES
    (r.part_num, r.part_name);
END LOOP;
COMMIT;
END slow_way;
/

set timing on

exec slow_way -- 07.71

Fast Way 1

Fetch into user defined array

CREATE OR REPLACE PROCEDURE fast_way IS

TYPE myarray IS TABLE OF parent%ROWTYPE;
l_data myarray;

CURSOR r IS
SELECT part_num, part_name
FROM parent;

BEGIN
OPEN r;
LOOP
    FETCH r BULK COLLECT INTO l_data LIMIT 1000;

    FOR j IN 1 .. l_data.COUNT
LOOP
    l_data(j).part_num := l_data(j).part_num * 10;
END LOOP;

    FORALL i IN 1..l_data.COUNT
    INSERT INTO child VALUES l_data(i);

    EXIT WHEN r%NOTFOUND;
END LOOP;
COMMIT;
CLOSE r;
END fast_way;
/

set timing on

exec fast_way -- 00.50

set timing off

SELECT 7.71/0.50 FROM DUAL;

Fast Way 2

Fetch into user defined PL/SQL table

CREATE OR REPLACE PROCEDURE fast_way IS

TYPE PartNum IS TABLE OF parent.part_num%TYPE
INDEX BY BINARY_INTEGER;
pnum_t PartNum;

TYPE PartName IS TABLE OF parent.part_name%TYPE
INDEX BY BINARY_INTEGER;
pnam_t PartName;

BEGIN
SELECT part_num, part_name
BULK COLLECT INTO pnum_t, pnam_t
FROM parent;

FOR i IN pnum_t.FIRST .. pnum_t.LAST
LOOP
pnum_t(i) := pnum_t(i) * 10;
END LOOP;

FORALL i IN pnum_t.FIRST .. pnum_t.LAST
INSERT INTO child
(part_num, part_name)
VALUES
(pnum_t(i), pnam_t(i));
COMMIT;
END fast_way;
/

set timing on

exec fast_way -- 0.62

Fast Way 3

Fetch into DBMS_SQL defined array

CREATE OR REPLACE PROCEDURE fast_way IS

TYPE parent_rec IS RECORD (
part_num   dbms_sql.number_table,
part_name dbms_sql.varchar2_table);

p_rec parent_rec;

CURSOR c IS
SELECT part_num, part_name
FROM parent;

l_done BOOLEAN;

BEGIN
OPEN c;
LOOP
    FETCH c BULK COLLECT INTO p_rec.part_num, p_rec.part_name
    LIMIT 500;
    l_done := c%NOTFOUND;

FOR i IN 1 .. p_rec.part_num.COUNT
    LOOP
      p_rec.part_num(i) := p_rec.part_num(i) * 10;
END LOOP;

    FORALL i IN 1 .. p_rec.part_num.COUNT
INSERT INTO child
    (part_num, part_name)
VALUES
    (p_rec.part_num(i), p_rec.part_name(i));

    EXIT WHEN (l_done);
END LOOP;
COMMIT;
CLOSE c;
END fast_way;
/

set timing on

exec fast_way -- 0.51

Fast Way 4

Affect of triggers on performance of cursor loops vs. array processing

TRUNCATE TABLE child;

set timing on

exec slow_way;
exec fast_way;

set timing off

TRUNCATE TABLE child;

CREATE OR REPLACE TRIGGER bi_child
BEFORE INSERT
ON child
FOR EACH ROW
BEGIN
NULL;
END bi_child;
/

set timing on

exec slow_way;
-- Elapsed: 00:05:54.36

exec fast_way;
-- Elapsed: 00:00:01.96

Fast Way 5

Insert into multiple tables

TRUNCATE TABLE child;

RENAME child TO child1;

CREATE TABLE child2 AS
SELECT * FROM child1;

CREATE OR REPLACE PROCEDURE fast_way IS

TYPE myarray IS TABLE OF parent%ROWTYPE;
l_data myarray;

CURSOR r IS
SELECT part_num, part_name
FROM parent;

BEGIN
OPEN r;
LOOP
    FETCH r BULK COLLECT INTO l_data LIMIT 1000;

    FOR j IN 1 .. l_data.COUNT LOOP
      l_data(j).part_num := l_data(j).part_num * 10;
    END LOOP;

    FORALL i IN 1..l_data.COUNT
    INSERT INTO child1 VALUES l_data(i);

    FORALL i IN 1..l_data.COUNT
    INSERT INTO child2 VALUES l_data(i);

    EXIT WHEN r%NOTFOUND;
END LOOP;
COMMIT;
CLOSE r;
END fast_way;
/

set timing on

exec fast_way

Partial Collections

Part of Collection Demo

CREATE TABLE test (
deptno NUMBER(3,0),
empname VARCHAR2(20));

INSERT INTO test VALUES (100, 'Morgan');
INSERT INTO test VALUES (200, 'Allen');
INSERT INTO test VALUES (101, 'Lofstrom');
INSERT INTO test VALUES (102, 'Havemeyer');
INSERT INTO test VALUES (202, 'Norgaard');
INSERT INTO test VALUES (201, 'Lewis');
INSERT INTO test VALUES (103, 'Scott');
INSERT INTO test VALUES (104, 'Foote');
INSERT INTO test VALUES (105, 'Townsend');
INSERT INTO test VALUES (106, 'Abedrabbo');
COMMIT;

SELECT * FROM test;

CREATE OR REPLACE PROCEDURE collection_part IS
TYPE NumList IS VARRAY(10) OF NUMBER;
depts NumList := NumList(100,200,101,102,202,201,103,104,105,106);
BEGIN
FORALL j IN 4..7 -- use only part of varray
DELETE FROM test WHERE deptno = depts(j);
COMMIT;
END collection_part;
/

SELECT * FROM test;

Sparse Collection

Note: A sparse collection is one from which elements have been deleted.

Sparse Collection Demo using IN INDICES OF

ALTER TABLE child
ADD CONSTRAINT uc_child_part_num
UNIQUE (part_num)
USING INDEX;

DECLARE
TYPE typ_part_name IS TABLE OF parent%ROWTYPE;
v_part typ_part_name;
BEGIN
SELECT *
BULK COLLECT INTO v_part
FROM parent;

FOR rec IN 1 .. v_part.LAST()
LOOP
    IF v_part(rec).part_name != 'Rectifier' THEN
      v_part.delete(rec);
    END IF;
END LOOP;

FORALL i IN 1 .. v_part.COUNT
INSERT INTO child
VALUES
v_part(i);

COMMIT;
END;
/

DECLARE
TYPE typ_part_name IS TABLE OF parent%ROWTYPE;
v_part typ_part_name;
BEGIN
SELECT *
BULK COLLECT INTO v_part
FROM parent;

FOR rec IN 1 .. v_part.LAST
LOOP
    IF v_part(rec).part_name != 'Rectifier' THEN
      v_part.delete(rec);
    END IF;
END LOOP;

FORALL idx IN INDICES OF v_part
INSERT INTO child
VALUES
v_part(idx);

COMMIT;
END;
/

SELECT COUNT(*) FROM parent;

SELECT COUNT(*) FROM child;

Using INDICES OF and VALUES OF with Non-Consecutive Index Values

CREATE TABLE valid_orders (
cust_name VARCHAR2(32),
amount NUMBER(10,2));

CREATE TABLE big_orders AS
SELECT * FROM valid_orders WHERE 1 = 0;

CREATE TABLE rejected_orders AS
SELECT * FROM valid_orders WHERE 1 = 0;

DECLARE
-- collections to hold a set of customer names and amounts
SUBTYPE cust_name IS valid_orders.cust_name%TYPE;
TYPE cust_typ IS TABLE OF cust_name;
cust_tab cust_typ;

SUBTYPE order_amount IS valid_orders.amount%TYPE;
TYPE amount_typ IS TABLE OF NUMBER;
amount_tab amount_typ;

-- collections to point into the CUST_TAB collection.
TYPE index_pointer_t IS TABLE OF PLS_INTEGER;

big_order_tab index_pointer_t := index_pointer_t();

rejected_order_tab index_pointer_t := index_pointer_t();

PROCEDURE setup_data IS
BEGIN
-- Set up sample order data, with some invalid and 'big' orders
cust_tab := cust_typ('Company1', 'Company2', 'Company3',
'Company4', 'Company5');

amount_tab := amount_typ(5000.01, 0, 150.25, 4000.00, NULL);
END setup_data;

BEGIN
setup_data;

dbms_output.put_line('--- Original order data ---');
FOR i IN 1..cust_tab.LAST
LOOP
    dbms_output.put_line('Cust#' || i || ', '|| cust_tab(i) ||
    ': $'||amount_tab(i));
END LOOP;

-- Delete invalid orders (where amount is null or 0)
FOR i IN 1..cust_tab.LAST
LOOP
    IF amount_tab(i) is null or amount_tab(i) = 0 THEN
      cust_tab.delete(i);
      amount_tab.delete(i);
    END IF;
END LOOP;

dbms_output.put_line('---Data with deleted invalid orders---');

FOR i IN 1..cust_tab.LAST LOOP
    IF cust_tab.EXISTS(i) THEN
      dbms_output.put_line('Cust#' || i || ', ' || cust_tab(i) ||
      ': $'||amount_tab(i));
    END IF;
END LOOP;

-- Since the subscripts of our collections are not consecutive,
-- we use use FORRALL...INDICES OF to iterate the subscripts
FORALL i IN INDICES OF cust_tab
INSERT INTO valid_orders
(cust_name, amount)
VALUES
(cust_tab(i), amount_tab(i));

-- Now let's process the order data differently extracting
-- 2 subsets and storing each subset in a different table.
setup_data; -- Reinitialize the CUST_TAB and AMOUNT_TAB collections

FOR i IN cust_tab.FIRST .. cust_tab.LAST
LOOP
    IF amount_tab(i) IS NULL OR amount_tab(i) = 0 THEN
      -- add a new element to the collection
      rejected_order_tab.EXTEND;
      -- record original collection subscript
      rejected_order_tab(rejected_order_tab.LAST) := i;
    END IF;

    IF amount_tab(i) > 2000 THEN
      -- Add a new element to the collection
      big_order_tab.EXTEND;
      -- record original collection subscript
      big_order_tab(big_order_tab.LAST) := i;
    END IF;
END LOOP;

-- run one DML statement on one subset of elements,
-- and another DML statement on a different subset.
FORALL i IN VALUES OF rejected_order_tab
INSERT INTO rejected_orders VALUES (cust_tab(i), amount_tab(i));

FORALL i IN VALUES OF big_order_tab
INSERT INTO big_orders VALUES (cust_tab(i), amount_tab(i));

COMMIT;
END;
/

-- Verify that the correct order details were stored
SELECT cust_name "Customer", amount "Valid order amount"
FROM valid_orders;

SELECT cust_name "Customer", amount "Big order amount"
FROM big_orders;

SELECT cust_name "Customer", amount "Rejected order amount"
FROM rejected_orders;

Exception Handling

Bulk Collection Exception Handling

CREATE TABLE tmp_target AS SELECT table_name, num_rows
FROM all_tables
WHERE 1=2;

ALTER TABLE tmp_target
ADD CONSTRAINT cc_num_rows
CHECK (num_rows > 0);

CREATE OR REPLACE PROCEDURE forall_errors IS

TYPE myarray IS TABLE OF tmp_target%ROWTYPE;
l_data myarray;

CURSOR c IS
SELECT table_name, num_rows
FROM all_tables;

errors PLS_INTEGER;

dml_errors EXCEPTION;
PRAGMA EXCEPTION_INIT(dml_errors, -24381);

BEGIN
OPEN c;
LOOP
    FETCH c BULK COLLECT INTO l_data LIMIT 100;

    -- SAVE EXCEPTIONS means don't stop if some DELETES fail
    FORALL i IN 1..l_data.COUNT SAVE EXCEPTIONS
    INSERT INTO tmp_target VALUES l_data(i);

    -- If any errors occurred during the FORALL SAVE EXCEPTIONS,
    -- a single exception is raised when the statement completes.
    EXIT WHEN c%NOTFOUND;
END LOOP;
EXCEPTION
WHEN dml_errors THEN
    errors := SQL%BULK_EXCEPTIONS.COUNT;
    dbms_output.put_line('Number of DELETE statements that
    failed: ' || errors);

    FOR i IN 1 .. errors
    LOOP
      dbms_output.put_line('Error #' || i || ' at '|| 'iteration
      #' || SQL%BULK_EXCEPTIONS(i).ERROR_INDEX);
      dbms_output.put_line('Error message is ' ||
      SQLERRM(-SQL%BULK_EXCEPTIONS(i).ERROR_CODE));
    END LOOP;
WHEN OTHERS THEN
    RAISE;
END forall_errors;
/

SQL> exec forall_errors;

SQL> SELECT * FROM tmp_target;

Exception Handling Demo

CREATE OR REPLACE PROCEDURE array_exceptions IS

-- cursor for processing load_errors
CURSOR le_cur IS
SELECT *
FROM load_errors
FOR UPDATE;

TYPE myarray IS TABLE OF test%ROWTYPE;
l_data myarray;

CURSOR c IS
SELECT sub_date, cust_account_id, carrier_id, ticket_id, upd_date
FROM stage
FOR UPDATE SKIP LOCKED;

errors PLS_INTEGER;

cai   test.cust_account_id%TYPE;
cid   test.carrier_id%TYPE;
ecode NUMBER;
iud   stage.upd_date%TYPE;
sd    test.sub_date%TYPE;
tid   test.ticket_id%TYPE;
upd   test.upd_date%TYPE;
BEGIN
OPEN c;
LOOP
    FETCH c BULK COLLECT INTO l_data LIMIT 50000;

    FORALL i IN 1..l_data.COUNT SAVE EXCEPTIONS
    INSERT INTO test VALUES l_data(i);

    EXIT WHEN c%NOTFOUND;
END LOOP;
COMMIT; -- Exits here when no exceptions are raised
EXCEPTION
WHEN OTHERS THEN
    -- get the number of errors in the exception array
    errors := SQL%BULK_EXCEPTIONS.COUNT;

    -- insert all exceptions into the load_errors table
    FOR j IN 1 .. errors LOOP
      ecode := SQL%BULK_EXCEPTIONS(j).ERROR_CODE;
      sd := TRUNC(l_data(SQL%BULK_EXCEPTIONS(j).ERROR_INDEX).sub_date);
      cai := l_data(SQL%BULK_EXCEPTIONS(j).ERROR_INDEX).cust_account_id;
      cid := l_data(SQL%BULK_EXCEPTIONS(j).ERROR_INDEX).carrier_id;
      tid := l_data(SQL%BULK_EXCEPTIONS(j).ERROR_INDEX).ticket_id;

      INSERT INTO load_errors
      (error_code, sub_date, cust_account_id, carrier_id, ticket_id)
      VALUES
      (ecode, sd, cai, cid, tid);
    END LOOP;

    -- for each record in load_errors process those that can
    -- be handled and delete them after successful handling
    FOR le_rec IN le_cur LOOP
      IF le_rec.error_code = 1 THEN
        SELECT upd_date
        INTO iud
        FROM test
        WHERE cust_account_id = le_rec.cust_account_id
        AND carrier_id = le_rec.carrier_id
        AND ticket_id = le_rec.ticket_id;

        IF iud IS NULL THEN
          RAISE;
        ELSIF iud < le_rec.upd_date THEN
          UPDATE test
          SET upd_date = le_rec.upd_date
          WHERE sub_date = le_rec.sub_date
          AND cust_account_id = le_rec.cust_account_id
          AND carrier_id = le_rec.carrier_id
          AND ticket_id = le_rec.ticket_id;
        ELSE
          RAISE;
        END IF;
      END IF;
    END LOOP;
    COMMIT; -- Exits here when any existing found.
END array_exceptions;
/

Native Dynamic SQL

Dynamic SQL Inside a FORALL Statement

CREATE TABLE tmp_target AS SELECT rownum ID, table_name, num_rows
FROM all_tables
WHERE rownum < 101;

DECLARE
TYPE NumList IS TABLE OF NUMBER;
rownos NumList;

TYPE NameList IS TABLE OF VARCHAR2(30);
tnames NameList;
BEGIN
rownos := NumList(2,4,6,8,16);

FORALL i IN 1..5
EXECUTE IMMEDIATE ' UPDATE tmp_target SET id = id * 1.1
WHERE id = :1
RETURNING table_name INTO :2'
USING rownos(i) RETURNING BULK COLLECT INTO tnames;

FOR j IN 1..5
LOOP
dbms_output.put_line(tnames(j));
END LOOP;
END;
/

Array Of Records Demo

You cannot bulk collect into an ARRAY OF RECORDS. You can into a RECORD OF ARRAYS.....

This demo intentionally generates an error. Familiarize yourself with the error and message so you will recognize it

CREATE OR REPLACE TYPE uw_sel_row AS OBJECT (
part_num NUMBER, part_name VARCHAR2(15));
/

CREATE OR REPLACE PROCEDURE wrong_way IS
TYPE uw_sel_tab IS TABLE OF uw_sel_row;
uw_selection uw_sel_tab;
BEGIN
SELECT uw_sel_row(part_num, part_name)
BULK COLLECT INTO uw_selection
FROM parent;

FOR i IN 1..uw_selection.count
LOOP
uw_selection(i).part_num := uw_selection(i).part_num * 10;
END LOOP;

FORALL i IN 1..uw_selection.COUNT
INSERT INTO child
VALUES
(uw_selection(i).part_num, uw_selection(i).part_name);
COMMIT;
END wrong_way;
/

sho err

drop type uw_sel_row;

CREATE OR REPLACE PROCEDURE right_way IS

TYPE uw_sel_row IS TABLE OF parent%ROWTYPE;
uw_selection uw_sel_row;
BEGIN
SELECT part_num, part_name
BULK COLLECT INTO uw_selection
FROM parent;

FOR i IN 1..uw_selection.count
LOOP
uw_selection(i).part_num := uw_selection(i).part_num * 10;
END LOOP;

FORALL i IN 1..uw_selection.COUNT
INSERT INTO child VALUES uw_selection(i);
COMMIT;
END right_way;
/

Bulk Collect Into DBMS_SQL Data Types

Bulk Collect with DBMS_SQL Data Types

CREATE TABLE t AS
SELECT *
FROM all_objects
WHERE 1=0;

CREATE OR REPLACE PROCEDURE nrows_at_a_time(p_array_size PLS_INTEGER) IS
l_owner          dbms_sql.VARCHAR2_table;
l_object_name    dbms_sql.VARCHAR2_table;
l_subobject_name dbms_sql.VARCHAR2_table;
l_object_id      dbms_sql.NUMBER_table;
l_data_object_id dbms_sql.NUMBER_table;
l_object_type    dbms_sql.VARCHAR2_table;
l_created        dbms_sql.DATE_table;
l_last_ddl_time dbms_sql.DATE_table;
l_timestamp      dbms_sql.VARCHAR2_table;
l_status         dbms_sql.VARCHAR2_table;
l_temporary      dbms_sql.VARCHAR2_table;
l_generated      dbms_sql.VARCHAR2_table;
l_secondary      dbms_sql.VARCHAR2_table;

CURSOR c IS
SELECT *
FROM all_objects
WHERE SUBSTR(object_name,1,1) BETWEEN 'A' AND 'W';
BEGIN
OPEN c;
LOOP
    FETCH c BULK COLLECT INTO
    l_owner, l_object_name, l_subobject_name, l_object_id,
    l_data_object_id, l_object_type, l_created,
    l_last_ddl_time, l_timestamp, l_status, l_temporary,
    l_generated, l_secondary
    LIMIT p_array_size;

    FORALL i in 1 .. l_owner.COUNT
      INSERT INTO t
      (owner, object_name, subobject_name, object_id,
       data_object_id, object_type, created, last_ddl_time,
       timestamp, status, temporary, generated, secondary)
      VALUES
      (l_owner(i), l_object_name(i), l_subobject_name(i),
       l_object_id(i), l_data_object_id(i),
       l_object_type(i), l_created(i), l_last_ddl_time(i),
       l_timestamp(i), l_status(i), l_temporary(i),
       l_generated(i), l_secondary(i));
    EXIT WHEN c%NOTFOUND;
END LOOP;
COMMIT;
CLOSE c;
END nrows_at_a_time;
/

Related Topics

Home : Code Library : Sponsors : Privacy : Terms of Use : Contact Us