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 amar_padhi@fastmail.fm