SAVE EXCEPTIONS and SQL%BULK_EXCEPTION

CREATE TABLE exception_test (
  id  NUMBER(10) NOT NULL
);

set serveroutput on;

DECLARE
TYPE exc_tab
IS
TABLE OF exception_test%ROWTYPE;
l_exc exc_tab := exc_tab();
l_error_count NUMBER;
E_EXC_ERRORS EXCEPTION;
PRAGMA EXCEPTION_INIT(e_exc_errors, -24381);
BEGIN
— Fill the collection.
FOR I IN 1 .. 100
LOOP
L_EXC.extend;
l_exc(l_exc.last).id := i;
END LOOP;
–Error Statments stated below
L_EXC(50).id := NULL;
L_EXC(51).id := NULL;
–End of Error Statments
EXECUTE IMMEDIATE ‘TRUNCATE TABLE exception_test’;
— Perform a bulk operation.
BEGIN
FORALL i IN l_exc.first .. l_exc.last SAVE EXCEPTIONS
INSERT INTO EXCEPTION_TEST VALUES l_exc
(i
);
EXCEPTION
WHEN e_exc_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;
/

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s