Oracle Database Autonomous Transactions Using autonomous transaction for logging errors
Example
The following procedure is a generic one which will be used to log all errors in an application to a common error log table.
CREATE OR REPLACE PROCEDURE log_errors
(
p_calling_program IN VARCHAR2,
p_error_code IN INTEGER,
p_error_description IN VARCHAR2
)
IS
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
INSERT INTO error_log
VALUES
(
p_calling_program,
p_error_code,
p_error_description,
SYSDATE,
USER
);
COMMIT;
END log_errors;
The following anonymous PLSQL block shows how to call the log_errors procedure.
BEGIN
DELETE FROM dept WHERE deptno = 10;
EXCEPTION
WHEN OTHERS THEN
log_errors('Delete dept',sqlcode, sqlerrm);
RAISE;
END;
SELECT * FROM error_log;
CALLING_PROGRAM ERROR_CODE ERROR_DESCRIPTION ERROR_DATETIME DB_USER
Delete dept -2292 ORA-02292: integrity constraint violated - child record found 08/09/2016 APEX_PUBLIC_USER
