Bulk Collect Sample Example

[sankarch]

DECLARE
TYPE t_id IS TABLE OF t1.ID%TYPE;
TYPE t_val1 IS TABLE OF t1.val%TYPE;
l_id t_id;
l_val t_val1;

CURSOR c1 IS
SELECT ID,val FROM t1;
ERRORS PLS_INTEGER;
err_cd PLS_INTEGER;
BEGIN
OPEN c1;
LOOP
FETCH c1
BULK COLLECT INTO l_id,
l_val LIMIT 5;

DBMS_OUTPUT.put_line ('LIMIT=' || l_id.COUNT);
FORALL i IN 1 .. l_id.COUNT SAVE EXCEPTIONS
INSERT INTO t2(ID,val)
VALUES (l_id (i),l_val (i));
ERRORS := SQL%BULK_EXCEPTIONS.COUNT;
EXIT WHEN c1%NOTFOUND;
END LOOP;

CLOSE c1;
EXCEPTION
WHEN OTHERS THEN
ERRORS := SQL%BULK_EXCEPTIONS.COUNT;
DBMS_OUTPUT.put_line ('ERRORS=' || ERRORS);

FOR j IN 1 .. ERRORS LOOP
err_cd := SQL%BULK_EXCEPTIONS (j).ERROR_INDEX;
DBMS_OUTPUT.put_line ('ID=' || l_id (err_cd) || ' error='
|| SQLERRM (-1 * SQL%BULK_EXCEPTIONS (j).ERROR_CODE));
END LOOP;

IF c1%ISOPEN THEN
CLOSE c1;
END

[Anvesh reddy]

thanks very useful