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> drop table system_errors;
Table dropped.