Error Logging Table
CREATE TABLE error_log
(
log_id NUMBER GENERATED ALWAYS AS IDENTITY,
created_on TIMESTAMP WITH LOCAL TIME ZONE,
created_by VARCHAR2 (100),
errorcode INTEGER,
callstack VARCHAR2 (4000),
errorstack VARCHAR2 (4000),
backtrace VARCHAR2 (4000),
error_info VARCHAR2 (4000)
)Table created.
Totally Minimal API for Error Logging
CREATE OR REPLACE PACKAGE error_mgr
IS
failure_in_forall EXCEPTION;
PRAGMA EXCEPTION_INIT (failure_in_forall, -24381);
PROCEDURE log_error (app_info_in IN VARCHAR2);
END;Package created.
Log the Error!
CREATE OR REPLACE PACKAGE BODY error_mgr
IS
PROCEDURE log_error (app_info_in IN VARCHAR2)
IS
PRAGMA AUTONOMOUS_TRANSACTION;
/* Cannot call this function directly in SQL */
c_code CONSTANT INTEGER := SQLCODE;
BEGIN
INSERT INTO error_log (created_on,
created_by,
errorcode,
callstack,
errorstack,
backtrace,
error_info)
VALUES (SYSTIMESTAMP,
USER,
c_code,
DBMS_UTILITY.format_call_stack,
DBMS_UTILITY.format_error_stack,
DBMS_UTILITY.format_error_backtrace,
app_info_in);
COMMIT;
END;
END;Package Body created.
Try it Out
DECLARE
l_company_id INTEGER;
BEGIN
IF l_company_id IS NULL
THEN
RAISE VALUE_ERROR;
END IF;
EXCEPTION
WHEN OTHERS
THEN
error_mgr.log_error ('Company ID is NULL - not allowed.');
END;Statement processed.
SELECT backtrace, errorstack, callstack FROM error_log| BACKTRACE | ERRORSTACK | CALLSTACK | ORA-06512: at line 6 | ORA-06502: PL/SQL: numeric or value error | ----- PL/SQL Call Stack ----- object line frame object handle number size name 0x174babe00 1 120 anonymous block 0x16c57e1b0 9 192 package body SQL_GXRRLAUFGAKTRUUILZBVJPVTA.ERROR_MGR 0x16a92c090 11 32 anonymous block 0x165ad5e20 1707 88 package body SYS.DBMS_SQL 0x163abe4d0 1209 22000 package body LIVESQL.ORACLE_SQL_EXEC 0x163abe4d0 1331 816 package body LIVESQL.ORACLE_SQL_EXEC 0x163abe4d0 1454 8648 package body LIVESQL.ORACLE_SQL_EXEC 0x163abe4d0 1789 5024 package body LIVESQL.ORACLE_SQL_EXEC 0x163abe4d0 1905 2528 package body LIVESQL.ORACLE_SQL_EXEC 0x163b16c38 1876 3712 package body LIVESQL.ORACLE_SQL_SCHEMA 0x163b1b888 306 112 package body LIVESQL.ORACLE_SQL_SCHEMA_PUB 0x16383cd08 22 1616 anonymous block 0x165f7cbd0 2107 88 package body SYS.DBMS_SYS_SQL 0x16816fc70 1033 936 package body SYS.WWV_DBMS_SQL 0x1683308f8 892 160 package body APEX_050000.WWV_FLOW_DYNAMIC_EXEC 0x1656be9a0 67 248 package body APEX_050000.WWV_FLOW_PROCESS_NATIVE 0x1656be9a0 1101 4560 package body APEX_050000.WWV_FLOW_PROCESS_NATIVE 0x168155418 2017 2784 package body APEX_050000.WWV_FLOW_PLUGIN 0x165a8e320 188 2392 package body APEX_050000.WWV_FLOW_PROCESS 0x165a8e320 428 11720 package body APEX_050000.WWV_FLOW_PROCESS 0x16aaa1d60 4873 32 package body APEX_050000.WWV_FLOW 0x16aaa1d60 4920 14744 package body APEX_050000.WWV_FLOW 0x16aabc840 173 2992 procedure APEX_050000.F 0x1734990a8 2 4328 anonymous block |
|---|