amar on web

Loading huge volume of data into PL/SQL table at one shot (Bulk Collect)

Is it possible to assign huge volume of data to a PL/SQL table, without using the conventional method of looping?

Use Bulk Collects
In the previous article (Bulk Binds), the list with empno's was statically built. With Bulk Collect you can dynamically build the entire list using "bulk collect into" option.

DECLARE
   TYPE Numlist IS TABLE OF emp.empno%TYPE;
   Id Numlist;
BEGIN
   SELECT empno BULK COLLECT INTO Id
     FROM emp
    WHERE sal < 2000;
   FORALL i IN Id.FIRST..Id.LAST
      UPDATE emp SET Sal = 1.1 * Sal
      WHERE mgr = Id(i);
END;
/
Bulk collects updated value return
You can even use Bulk Collects with DML-Commands to return a value to the calling procedure using RETURNING without an additional fetch.
DECLARE
   TYPE Numlist IS TABLE OF emp.empno%TYPE;
   TYPE Bonlist IS TABLE OF emp.sal%TYPE;
   Id Numlist;
   Bl Bonlist;
BEGIN
   SELECT empno BULK COLLECT INTO Id
     FROM emp
    WHERE deptno = 10;
   FORALL i IN Id.FIRST..Id.LAST
      UPDATE emp SET Sal = 1.1 * Sal
      WHERE mgr = Id(i)
   RETURNING Sal BULK COLLECT INTO Bl;
END;
/
In the PL/SQL table "Bonlist" you can now find the updated salaries.

Best viewed in medium text size. Please refresh this page (F5) to view the latest information.
This page was created on 16-dec-2001 and last updated on 16-dec-2001.
please forward all queries to [email protected]