AmZone

Web Hosting by Netfirms | Free Domain Names by Netfirms

AmZone

A m Z o n e

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.

Press the Back button of you Browser to go to previous page
Home