Error handling in PL SQL

Run-time errors arise from design faults, coding mistakes, hardware failures, and many other sources. Although you cannot anticipate all possible errors, you can plan to handle certain kinds of errors meaningful to your PL/SQL program.

CREATE OR REPLACE PROCEDURE   SP_EXCEPTION_HANDLING
AS
-- Decalre required variables
L_DOC_DTLS VARCHAR2(4000);
L_ERROR_LINE VARCHAR2(8000);
L_ERROR_MSG VARCHAR2(8000);
 
BEGIN


/*Your procedure code goes here*/   


-- Error handling      
EXCEPTION
      WHEN OTHERS THEN
        
        -- Required parameters for identfying the issue
        L_DOC_DTLS := P_COMPANY_POID || P_PARTY_POID|| P_CHARGE_POID;
        
        -- Error details
        L_ERROR_LINE := DBMS_UTILITY.FORMAT_ERROR_STACK ||' at line ' || $$PLSQL_LINE || ' ERROR_BACKTRACE: ' || L_DOC_DTLS || DBMS_UTILITY.FORMAT_ERROR_BACKTRACE;
        L_ERROR_MSG := SQLERRM; 
        
        -- For logging the error details in GLOBAL_EXCEPTIONS_DATA table.
        -- Parameters to PROC_GLOBAL_EXCEPTIONS_DATA: P_DOC_ID, P_DOC_KEY_POID, P_USER_POID, P_PROC_NAME, P_DEBUG_DATA 
        PROC_GLOBAL_EXCEPTIONS_DATA( P_DOC_ID, P_DOC_KEY_POID, P_USER_POID, 'PROCEDURE_NAME', L_DOC_DTLS || L_ERROR_LINE|| L_ERROR_MSG) ;
        
        -- Send error details via email, if required.
        -- Default email will go to the ADMIN_USER_EMAIL_IDS group defined in the parameter table.
        --    PROC_GLOBAL_MAIL_FORMATTED(NULL, NULL, 'Error In procedure',
        --    'Some error occurred in PROCEDURE_NAME', 'PROCEDURE_NAME', 'ERROR : ' || L_DOC_DTLS || L_ERROR_LINE|| L_ERROR_MSG, NULL);

END;

Read More