Handling Exception in Bulk Collect

DECLARE
TYPE array is TABLE OF t%ROWTYPE
INDEX BY BINARY_INTEGER ;
data array;
errors NUMBER ;
l_cnt NUMBER := 0;
dml_errors EXCEPTION;

PRAGMA EXCEPTION_INIT (dml_errors, -24381) ;

CURSOR c is SELECT * FROM t;
BEGIN
OPEN c;
LOOP
FETCH c BULK COLLECT INTO data LIMIT 100;
BEGIN
FORALL i IN 1..data.COUNT
SAVE EXCEPTIONS
INSERT INTO t2 values data(i) ;
EXCEPTION
WHEN dml_errors THEN
errors := SQL%BULK_EXCEPTIONS.COUNT ;
l_cnt := l_cnt + 1 ;
FOR i IN 1..errors LOOP
dbms_output.put_line('Error occurred during iteration ' ||SQL%BULK_EXCEPTIONS(i).ERROR_INDEX ||'. Oracle error is ' || SQL%BULK_EXCEPTIONS i).ERROR_CODE );
END LOOP;
END;
EXIT WHEN c%NOTFOUND ;
END LOOP;
CLOSE c;
dbms_output.put_line(l_cnt || ' Total Errors.');
END ;

Comments

Popular posts from this blog

PO & Receipt Query

Supplier SQL query

Inventory Item Attributes, Categories, Vendor & Costs