The LISTAGG analytic function, introduced in Oracle 11g Release 2, greatly simplifies string aggregations within SQL queries.
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;
Use the share button below if you liked it.
It makes me smile, when I see it.