SET SERVEROUTPUT ON DECLARE TYPE t_tab IS TABLE OF exception_test%ROWTYPE; l_tab t_tab := t_tab(); l_error_count NUMBER; ex_dml_errors EXCEPTION; PRAGMA EXCEPTION_INIT(ex_dml_errors, -24381); 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 exception_test'; -- Perform a bulk operation. BEGIN FORALL i IN l_tab.first .. l_tab.last SAVE EXCEPTIONS INSERT INTO exception_test VALUES l_tab(i); EXCEPTION WHEN ex_dml_errors THEN l_error_count := SQL%BULK_EXCEPTIONS.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; / Number of failures: 2 Error: 1 Array Index: 50 Message: ORA-01400: cannot insert NULL into () Error: 2 Array Index: 51 Message: ORA-01400: cannot insert NULL into () PL/SQL procedure successfully completed.
Wednesday, April 5, 2017
Save Exception or Bulk Exceptions in bulk collections
Subscribe to:
Post Comments (Atom)
No comments:
Post a Comment