DROP TABLE errlog; CREATE TABLE errlog ( errcode INTEGER, errmsg VARCHAR2(4000), created_on DATE, created_by VARCHAR2(100) ); CREATE OR REPLACE PACKAGE err IS c_table CONSTANT PLS_INTEGER := 1; -- Default c_file CONSTANT PLS_INTEGER := 2; c_screen CONSTANT PLS_INTEGER := 3; PROCEDURE handle ( errcode IN PLS_INTEGER := NULL, errmsg IN VARCHAR2 := NULL, logerr IN BOOLEAN := TRUE, reraise IN BOOLEAN := FALSE ); PROCEDURE report_and_stop (err_in IN INTEGER := SQLCODE, msg_in IN VARCHAR2 := NULL); PROCEDURE report_and_go (err_in IN INTEGER := SQLCODE, msg_in IN VARCHAR2 := NULL); PROCEDURE raise ( errcode IN PLS_INTEGER := NULL, errmsg IN VARCHAR2 := NULL ); PROCEDURE log ( errcode IN PLS_INTEGER := NULL, errmsg IN VARCHAR2 := NULL ); PROCEDURE logto ( target IN PLS_INTEGER, dir IN VARCHAR2 := NULL, file IN VARCHAR2 := NULL ); FUNCTION logging_to RETURN PLS_INTEGER; END; / CREATE OR REPLACE PACKAGE BODY err IS g_target PLS_INTEGER := c_table; g_file VARCHAR2 (2000) := 'err.log'; g_dir VARCHAR2 (2000) := NULL; PROCEDURE handle ( errcode IN PLS_INTEGER := NULL, errmsg IN VARCHAR2 := NULL, logerr IN BOOLEAN := TRUE, reraise IN BOOLEAN := FALSE ) IS BEGIN IF logerr THEN log (errcode, errmsg); END IF; IF reraise THEN err.raise (errcode, errmsg); END IF; END; PROCEDURE report_and_stop (err_in IN INTEGER := SQLCODE, msg_in IN VARCHAR2 := NULL) IS BEGIN handle (err_in, msg_in, TRUE, TRUE); END report_and_stop; PROCEDURE report_and_go (err_in IN INTEGER := SQLCODE, msg_in IN VARCHAR2 := NULL) IS BEGIN handle (err_in, msg_in, TRUE, FALSE); END report_and_stop; PROCEDURE raise ( errcode IN PLS_INTEGER := NULL, errmsg IN VARCHAR2 := NULL ) IS l_errcode PLS_INTEGER := NVL (errcode, SQLCODE); l_errmsg VARCHAR2(1000) := NVL (errmsg, SQLERRM); BEGIN IF l_errcode BETWEEN -20999 AND -20000 THEN raise_application_error (l_errcode, l_errmsg); /* Use positive error numbers -- lots to choose from! */ ELSIF l_errcode > 0 AND l_errcode NOT IN (1, 100) THEN raise_application_error (-20000, l_errcode || '-' || l_errmsg); /* Can't EXCEPTION_INIT -1403 */ ELSIF l_errcode IN (100, -1403) THEN RAISE NO_DATA_FOUND; /* Re-raise any other exception. */ ELSIF l_errcode != 0 THEN EXECUTE IMMEDIATE 'DECLARE myexc EXCEPTION; ' || ' PRAGMA EXCEPTION_INIT (myexc, ' || TO_CHAR (err_in) || ');' || 'BEGIN RAISE myexc; END;'; END IF; END; PROCEDURE log ( errcode IN PLS_INTEGER := NULL, errmsg IN VARCHAR2 := NULL ) IS PRAGMA AUTONOMOUS_TRANSACTION; l_sqlcode pls_integer := NVL (errcode, SQLCODE); l_sqlerrm VARCHAR2(1000) := NVL (errmsg, SQLERRM); BEGIN IF g_target = c_table THEN INSERT INTO errlog (errcode, errmsg, created_on, created_by) VALUES ( l_sqlcode, l_sqlerrm, SYSDATE, USER ); ELSIF g_target = c_file THEN DECLARE fid UTL_FILE.file_type; BEGIN fid := UTL_FILE.fopen (g_dir, g_file, 'A'); UTL_FILE.put_line (fid, 'Error log by ' || USER || ' at ' || TO_CHAR (SYSDATE, 'mm/dd/yyyy') ); UTL_FILE.put_line (fid, NVL (errmsg, SQLERRM)); UTL_FILE.fclose (fid); EXCEPTION WHEN OTHERS THEN UTL_FILE.fclose (fid); END; ELSIF g_target = c_screen THEN DBMS_OUTPUT.put_line ('Error log by ' || USER || ' at ' || TO_CHAR (SYSDATE, 'mm/dd/yyyy') ); DBMS_OUTPUT.put_line (NVL (errmsg, SQLERRM)); END IF; COMMIT; EXCEPTION WHEN OTHERS THEN ROLLBACK; END; PROCEDURE logto ( target IN PLS_INTEGER, dir IN VARCHAR2 := NULL, file IN VARCHAR2 := NULL ) IS BEGIN g_target := target; g_file := file; g_dir := dir; END; FUNCTION logging_to RETURN PLS_INTEGER IS BEGIN RETURN g_target; END; END; /