Records a error in the error logging table. : Utility trigger « Trigger « Oracle PL/SQL Tutorial

1. Oracle PL/SQL Tutorial
2. Trigger
3. Utility trigger

SQL>
SQL>
SQL> CREATE SEQUENCE system_error_id NOCACHE;

Sequence created.

SQL>
SQL> CREATE TABLE system_errors
  2     (system_error_id  NUMBER(10,0), package_name  VARCHAR2(50),
  3      procedure_name  VARCHAR2(50), execution_location  varchar2(20),
  4      oracle_error_text  VARCHAR2(200),
  5      additional_information  VARCHAR2(2000),
  6      call_stack   VARCHAR2(2000), error_stack  VARCHAR2(2000),
  7      insert_time  DATE, insert_user  VARCHAR2(30));

Table created.

SQL>
SQL> COMMENT ON TABLE system_errors IS
  2     'Errors generated by stored packages.';

Comment created.

SQL> COMMENT ON COLUMN system_errors.system_error_id IS
  2     'The system-wide ID to identify a system error. Useful for
  3      determining the order in which errors were encountered and
  4      logged.';

Comment created.

SQL> COMMENT ON COLUMN system_errors.package_name IS 'The package name.';

Comment created.

SQL> COMMENT ON COLUMN system_errors.procedure_name IS 'The procedure/function name.';

Comment created.

SQL> COMMENT ON COLUMN system_errors.execution_location IS 'A reference to a location in the executing code.';

Comment created.

SQL> COMMENT ON COLUMN system_errors.oracle_error_text IS 'The text of the Oracle error message.';

Comment created.

SQL> COMMENT ON COLUMN system_errors.additional_information IS 'Any pertinent information the developer may be trapping by the error handler.';

Comment created.

SQL> COMMENT ON COLUMN system_errors.call_stack IS 'The call stack at the time of the error.';

Comment created.

SQL> COMMENT ON COLUMN system_errors.error_stack IS 'The error stack at the time of the error.';

Comment created.

SQL> COMMENT ON COLUMN system_errors.insert_time IS 'The date and time of record insertion.';

Comment created.

SQL> COMMENT ON COLUMN system_errors.insert_user IS 'The user inserting the record.';

Comment created.

SQL>
SQL> CREATE OR REPLACE PROCEDURE log_error
  2     (p_package_txt   VARCHAR2 DEFAULT 'UNKNOWN',
  3     p_procedure_txt VARCHAR2 DEFAULT 'UNKNOWN',
  4     p_location_txt  VARCHAR2 DEFAULT 'UNKNOWN',
  5     p_error_txt     VARCHAR2 DEFAULT 'UNKNOWN',
  6     p_text_txt      VARCHAR2 DEFAULT 'NONE',
  7     p_commit_bln    BOOLEAN  DEFAULT TRUE,
  8     p_user_txt      VARCHAR2 DEFAULT USER,
  9     p_time_date     DATE     DEFAULT SYSDATE) IS
 10     lv_call_stack_txt VARCHAR2(2000);
 11     lv_error_stack_txt VARCHAR2(2000);
 12     pu_failure_excep EXCEPTION;
 13     PRAGMA EXCEPTION_INIT (pu_failure_excep, -20000);
 14  BEGIN
 15     lv_call_stack_txt := SUBSTR(DBMS_UTILITY.FORMAT_CALL_STACK, 1, 2000);
 16     lv_error_stack_txt := SUBSTR(DBMS_UTILITY.FORMAT_ERROR_STACK, 1, 2000);
 17     INSERT INTO system_errors (system_error_id, package_name, procedure_name,
 18         execution_location, oracle_error_text, additional_information,
 19         call_stack, error_stack, insert_time, insert_user)
 20     VALUES (system_error_id.NEXTVAL, SUBSTR(p_package_txt, 1, 50),
 21         SUBSTR(p_procedure_txt, 1, 50),
 22         SUBSTR(p_location_txt, 1, 20), SUBSTR(p_error_txt, 1, 200),
 23         SUBSTR(p_text_txt, 1, 2000), lv_call_stack_txt,
 24         lv_error_stack_txt, p_time_date, p_user_txt);
 25     IF p_commit_bln THEN
 26        COMMIT;
 27      END IF;
 28  EXCEPTION
 29     WHEN OTHERS THEN
 30        RAISE pu_failure_excep;
 31  END log_error;
 32  /

Procedure created.

SQL>
SQL> drop sequence system_error_id;

Sequence dropped.

SQL>
SQL>
SQL> drop table system_errors;

Table dropped.