/* Formatted on 2016/02/11 11:45 (Formatter Plus v4.8.8) */
SET SERVEROUTPUT ON
CREATE TABLE bulk_exception (
id NUMBER(10) NOT NULL
);
DECLARE
TYPE t_tab IS TABLE OF bulk_exception%ROWTYPE;
l_tab t_tab := t_tab ();
l_error_count NUMBER;
ex_dml_errors EXCEPTION;
PRAGMA EXCEPTION_INIT (ex_dml_errors, -24381); -- To initialize exception
BEGIN
-- Fill the collection.
FOR i IN 1 .. 100
LOOP
l_tab.EXTEND;
l_tab (l_tab.LAST).ID := i;
END LOOP;
-- Cause a failure.
l_tab (50).ID := NULL;
l_tab (51).ID := NULL;
EXECUTE IMMEDIATE 'TRUNCATE TABLE bulk_exception';
-- Perform a bulk operation.
BEGIN
FORALL i IN l_tab.FIRST .. l_tab.LAST SAVE EXCEPTIONS
INSERT INTO bulk_exception
VALUES l_tab (i);
EXCEPTION
WHEN ex_dml_errors
THEN
l_error_count := SQL%BULK_EXCEPTIONS.COUNT; -- error count
DBMS_OUTPUT.put_line ('Number of failures: ' || l_error_count);
FOR i IN 1 .. l_error_count
LOOP
DBMS_OUTPUT.put_line
( 'Error: '
|| i
|| ' Array Index: '
|| SQL%BULK_EXCEPTIONS (i).ERROR_INDEX
|| ' Message: '
|| SQLERRM
(-SQL%BULK_EXCEPTIONS (i).ERROR_CODE)
);
END LOOP;
END;
END;
SET SERVEROUTPUT ON
CREATE TABLE bulk_exception (
id NUMBER(10) NOT NULL
);
DECLARE
TYPE t_tab IS TABLE OF bulk_exception%ROWTYPE;
l_tab t_tab := t_tab ();
l_error_count NUMBER;
ex_dml_errors EXCEPTION;
PRAGMA EXCEPTION_INIT (ex_dml_errors, -24381); -- To initialize exception
BEGIN
-- Fill the collection.
FOR i IN 1 .. 100
LOOP
l_tab.EXTEND;
l_tab (l_tab.LAST).ID := i;
END LOOP;
-- Cause a failure.
l_tab (50).ID := NULL;
l_tab (51).ID := NULL;
EXECUTE IMMEDIATE 'TRUNCATE TABLE bulk_exception';
-- Perform a bulk operation.
BEGIN
FORALL i IN l_tab.FIRST .. l_tab.LAST SAVE EXCEPTIONS
INSERT INTO bulk_exception
VALUES l_tab (i);
EXCEPTION
WHEN ex_dml_errors
THEN
l_error_count := SQL%BULK_EXCEPTIONS.COUNT; -- error count
DBMS_OUTPUT.put_line ('Number of failures: ' || l_error_count);
FOR i IN 1 .. l_error_count
LOOP
DBMS_OUTPUT.put_line
( 'Error: '
|| i
|| ' Array Index: '
|| SQL%BULK_EXCEPTIONS (i).ERROR_INDEX
|| ' Message: '
|| SQLERRM
(-SQL%BULK_EXCEPTIONS (i).ERROR_CODE)
);
END LOOP;
END;
END;
No comments:
Post a Comment