CODEWITHSUNDEEP
oracle-databaseplsqloracle11goracleexception
Sachin
Sachin

Reputation: 1003

Execution stops after SQLERRM

I don't know if it is strange or not, but the execution stops/exits just after first encounter of SQLERRM.

I need to saved the exception details into the table made for the same.

Below is the code:

CURRENT_P_SECTOR := 20;
 EXCEPTION
     WHEN OTHERS THEN
       DBMS_OUTPUT.PUT_LINE('dummy 1111111111');
       DBMS_OUTPUT.PUT_LINE('dummy 22222222222');

       SQL_CODE := SQLCODE;

       DBMS_OUTPUT.PUT_LINE('dummy 33333333333');

       SQL_ERROR_M := SQLERRM;

       DBMS_OUTPUT.PUT_LINE('dummy 44444444444');

             DBMS_OUTPUT.PUT_LINE('EXCEPTION ::: ' || SQL_CODE);
             DBMS_OUTPUT.PUT_LINE('DETAILS ARE AS FOLLOWS(At Section: ' || CURRENT_P_SECTOR || ' ) :-' || CHR(10) || SQL_ERROR_M);

               INSERT INTO FIMS_OWNER.BATCH_ERROR_T VALUES(
                    BATCH_ERROR_T_SEQ.NEXTVAL,
                    'INTERFACE_INBOUND_PKG'/*CHANGE TO PARTICULAR PACKAGE NAME IF USED IN ANY PACKAGE*/,
                    'CES_LOAD_PRJ_PRC'/*PROCEDURE NAME*/,
                    CURRENT_P_SECTOR/*PROGRAM SECTOR TILL WHERE SUCCESSFUL EXECUTION */,
                    SQL_CODE/*ORACLE EXCEPTION CODE*/,
                    'Error Details : ' || CHR(10)|| SQL_ERROR_M/*COMPLETE EXCEPTION DETAILS*/,
                    'EXCEPTION',
                    SYSDATE
                     );

And DBMS OUTPUT I am getting is:

dummy 1111111111
dummy 22222222222
dummy 33333333333

Not dummy 44444444444 and beyond, clearly saying that execution is stopping after SQLERRM.

Execution I am explicitly making it throw is Unique constraint violation exception.

[Error] Execution (437: 1): ORA-06502: PL/SQL: numeric or value error: character string buffer too small
ORA-06512: at "FIMS_OWNER.CORE_LOAD_PO_PRC", line 415
ORA-00001: unique constraint (FIMS_OWNER.T_PO_LINE_ITEM_PK) violated
ORA-06512: at line 1

But the same exception is not getting entered in the BATCH_ERROR_T table.

Upvotes: 0

Views: 130

Answers (1)

Sachin
Sachin

Reputation: 1003

I got the answer, the silly mistake i didn't recognize, my length for variable SQL_ERROR_M was less than the error message itself.

Thanks community.

Upvotes: 0

Related Questions