I am logging errors that happen during bulk data loading and I get a different error messages for the same error when I use the SAVE EXCEPTION qualifier. I would like to do bulk operations and use the SAVE EXCEPTIONS qualifier so valid records are inserted into the table. I would also like to log any records that failed as well as pass any errors back to the calling process.

I get a more complete message when I don't use SAVE EXCEPTIONS but I would like to use SAVE EXCEPTIONS and get the full message. I haven't been able to find documentation about this situation. Any information would be appreciated.

Thanks,
Tim

CREATE TABLE test_table (a NUMBER NOT NULL);

Table created.

DECLARE
    TYPE t_test_table IS TABLE OF test_table%ROWTYPE;
    l_test_table t_test_table := t_test_table();
BEGIN
    l_test_table.extend;
    l_test_table(1).a := NULL;

    FORALL i IN l_test_table.first .. l_test_table.last --SAVE EXCEPTIONS
        INSERT INTO test_table VALUES l_test_table(i);

    EXCEPTION WHEN OTHERS THEN
        FOR i IN 1..SQL%BULK_EXCEPTIONS.COUNT
        LOOP
            DBMS_OUTPUT.PUT_LINE('SQLERRM: ' || SQLERRM(-SQL%BULK_EXCEPTIONS(i).ERROR_CODE));
        END LOOP;
END;
/

SQLERRM: ORA-01400: cannot insert NULL into ("TEST_SCHEMA"."TEST_TABLE"."A")

DECLARE
    TYPE t_test_table IS TABLE OF test_table%ROWTYPE;
    l_test_table t_test_table := t_test_table();
BEGIN
    l_test_table.extend;
    l_test_table(1).a := NULL;

    FORALL i IN l_test_table.first .. l_test_table.last SAVE EXCEPTIONS
        INSERT INTO test_table VALUES l_test_table(i);

    EXCEPTION WHEN OTHERS THEN
        FOR i IN 1..SQL%BULK_EXCEPTIONS.COUNT
        LOOP
            DBMS_OUTPUT.PUT_LINE('SQLERRM: ' || SQLERRM(-SQL%BULK_EXCEPTIONS(i).ERROR_CODE));
        END LOOP;
END;
/

SQLERRM: ORA-01400: cannot insert NULL into ()