Oracle Database, Ловец ошибок
Пример 1
https://www.youtube.com/watch?v=8WlQqDcvddI
Пример 2
Создаем таблицу для записи сообщений об ошибках.
CREATE TABLE error_logs (
id NUMBER(10) NOT NULL,
prefix VARCHAR2(50),
data VARCHAR2(2000) NOT NULL,
error_level NUMBER(2) NOT NULL,
created_date DATE NOT NULL,
created_by VARCHAR2(50) NOT NULL);
ALTER TABLE error_logs ADD (CONSTRAINT error_logs_pk PRIMARY KEY (id));
CREATE SEQUENCE error_logs_seq;
err.pks
CREATE OR REPLACE PACKAGE dsp AS
-- --------------------------------------------------------------------------
-- Author : DR Timothy S Hall
-- Description : An extension of the DBMS_OUTPUT package.
-- --------------------------------------------------------------------------
PROCEDURE reset_defaults;
PROCEDURE show_output_on;
PROCEDURE show_output_off;
PROCEDURE show_date_on;
PROCEDURE show_date_off;
PROCEDURE line_wrap_on;
PROCEDURE line_wrap_off;
PROCEDURE set_max_width (p_width IN NUMBER);
PROCEDURE set_date_format (p_date_format IN VARCHAR2);
PROCEDURE file_output_on (p_file_dir IN VARCHAR2 DEFAULT NULL,
p_file_name IN VARCHAR2 DEFAULT NULL);
PROCEDURE file_output_off;
FUNCTION get_last_prefix
RETURN VARCHAR2;
FUNCTION get_last_data
RETURN VARCHAR2;
PROCEDURE line (p_data IN VARCHAR2);
PROCEDURE line (p_data IN NUMBER);
PROCEDURE line (p_data IN BOOLEAN);
PROCEDURE line (p_data IN DATE,
p_format IN VARCHAR2 DEFAULT \'DD-MON-YYYY HH24:MI:SS.FF\');
PROCEDURE line (p_prefix IN VARCHAR2,
p_data IN VARCHAR2);
PROCEDURE line (p_prefix IN VARCHAR2,
p_data IN NUMBER);
PROCEDURE line (p_prefix IN VARCHAR2,
p_data IN BOOLEAN);
PROCEDURE line (p_prefix IN VARCHAR2,
p_data IN DATE,
p_format IN VARCHAR2 DEFAULT \'DD-MON-YYYY HH24:MI:SS.FF\');
END dsp;
/
dsp.pkb
CREATE OR REPLACE PACKAGE BODY dsp AS
-- --------------------------------------------------------------------------
-- Author : DR Timothy S Hall
-- Description : An extension of the DBMS_OUTPUT package.
-- Requirements : dsp.pks
-- --------------------------------------------------------------------------
-- Package Variables
g_show_output BOOLEAN := FALSE;
g_show_date BOOLEAN := FALSE;
g_line_wrap BOOLEAN := TRUE;
g_max_width NUMBER(10) := 255;
g_date_format VARCHAR2(32767) := \'DD-MON-YYYY HH24:MI:SS.FF\';
g_file_dir VARCHAR2(32767) := NULL;
g_file_name VARCHAR2(32767) := NULL;
g_last_prefix VARCHAR2(32767) := NULL;
g_last_data VARCHAR2(32767) := NULL;
-- Hidden Methods
PROCEDURE display (p_prefix IN VARCHAR2,
p_data IN VARCHAR2);
PROCEDURE wrap_line (p_data IN VARCHAR2);
PROCEDURE output (p_data IN VARCHAR2);
-- Exposed Methods
-- --------------------------------------------------------------------------
PROCEDURE reset_defaults IS
-- --------------------------------------------------------------------------
BEGIN
g_show_output := FALSE;
g_show_date := FALSE;
g_line_wrap := TRUE;
g_max_width := 255;
g_date_format := \'DD-MON-YYYY HH24:MI:SS.FF\';
END;
-- --------------------------------------------------------------------------
-- --------------------------------------------------------------------------
PROCEDURE show_output_on IS
-- --------------------------------------------------------------------------
BEGIN
g_show_output := TRUE;
END;
-- --------------------------------------------------------------------------
-- --------------------------------------------------------------------------
PROCEDURE show_output_off IS
-- --------------------------------------------------------------------------
BEGIN
g_show_output := FALSE;
END;
-- --------------------------------------------------------------------------
-- --------------------------------------------------------------------------
PROCEDURE show_date_on IS
-- --------------------------------------------------------------------------
BEGIN
g_show_date := TRUE;
END;
-- --------------------------------------------------------------------------
-- --------------------------------------------------------------------------
PROCEDURE show_date_off IS
-- --------------------------------------------------------------------------
BEGIN
g_show_date := FALSE;
END;
-- --------------------------------------------------------------------------
-- --------------------------------------------------------------------------
PROCEDURE line_wrap_on IS
-- --------------------------------------------------------------------------
BEGIN
g_line_wrap := TRUE;
END;
-- --------------------------------------------------------------------------
-- --------------------------------------------------------------------------
PROCEDURE line_wrap_off IS
-- --------------------------------------------------------------------------
BEGIN
g_line_wrap := FALSE;
END;
-- --------------------------------------------------------------------------
-- --------------------------------------------------------------------------
PROCEDURE set_max_width (p_width IN NUMBER) IS
-- --------------------------------------------------------------------------
BEGIN
g_max_width := p_width;
END;
-- --------------------------------------------------------------------------
-- --------------------------------------------------------------------------
PROCEDURE set_date_format (p_date_format IN VARCHAR2) IS
-- --------------------------------------------------------------------------
BEGIN
g_date_format := p_date_format;
END;
-- --------------------------------------------------------------------------
-- --------------------------------------------------------------------------
PROCEDURE file_output_on (p_file_dir IN VARCHAR2 DEFAULT NULL,
p_file_name IN VARCHAR2 DEFAULT NULL) IS
-- --------------------------------------------------------------------------
BEGIN
g_file_dir := p_file_dir;
g_file_name := p_file_name;
END;
-- --------------------------------------------------------------------------
-- --------------------------------------------------------------------------
PROCEDURE file_output_off IS
-- --------------------------------------------------------------------------
BEGIN
g_file_dir := NULL;
g_file_name := NULL;
END;
-- --------------------------------------------------------------------------
-- --------------------------------------------------------------------------
FUNCTION get_last_prefix
RETURN VARCHAR2 IS
-- --------------------------------------------------------------------------
BEGIN
RETURN g_last_prefix;
END;
-- --------------------------------------------------------------------------
-- --------------------------------------------------------------------------
FUNCTION get_last_data
RETURN VARCHAR2 IS
-- --------------------------------------------------------------------------
BEGIN
RETURN g_last_data;
END;
-- --------------------------------------------------------------------------
-- --------------------------------------------------------------------------
PROCEDURE line (p_data IN VARCHAR2) IS
-- --------------------------------------------------------------------------
BEGIN
display (NULL, p_data);
END;
-- --------------------------------------------------------------------------
-- --------------------------------------------------------------------------
PROCEDURE line (p_data IN NUMBER) IS
-- --------------------------------------------------------------------------
BEGIN
display (NULL, p_data);
END;
-- --------------------------------------------------------------------------
-- --------------------------------------------------------------------------
PROCEDURE line (p_data IN BOOLEAN) IS
-- --------------------------------------------------------------------------
BEGIN
line (NULL, p_data);
END;
-- --------------------------------------------------------------------------
-- --------------------------------------------------------------------------
PROCEDURE line (p_data IN DATE,
p_format IN VARCHAR2 DEFAULT \'DD-MON-YYYY HH24:MI:SS.FF\') IS
-- --------------------------------------------------------------------------
BEGIN
line (NULL, p_data, p_format);
END;
-- --------------------------------------------------------------------------
-- --------------------------------------------------------------------------
PROCEDURE line (p_prefix IN VARCHAR2,
p_data IN VARCHAR2) IS
-- --------------------------------------------------------------------------
BEGIN
display (p_prefix, p_data);
END;
-- --------------------------------------------------------------------------
-- --------------------------------------------------------------------------
PROCEDURE line (p_prefix IN VARCHAR2,
p_data IN NUMBER) IS
-- --------------------------------------------------------------------------
BEGIN
display (p_prefix, TO_CHAR(p_data));
END;
-- --------------------------------------------------------------------------
-- --------------------------------------------------------------------------
PROCEDURE line (p_prefix IN VARCHAR2,
p_data IN BOOLEAN) IS
-- --------------------------------------------------------------------------
BEGIN
IF p_data THEN
display (p_prefix, \'TRUE\');
ELSE
display (p_prefix, \'FALSE\');
END IF;
END;
-- --------------------------------------------------------------------------
-- --------------------------------------------------------------------------
PROCEDURE line (p_prefix IN VARCHAR2,
p_data IN DATE,
p_format IN VARCHAR2 DEFAULT \'DD-MON-YYYY HH24:MI:SS.FF\') IS
-- --------------------------------------------------------------------------
BEGIN
display (p_prefix, TO_CHAR(p_data, p_format));
END;
-- --------------------------------------------------------------------------
-- --------------------------------------------------------------------------
PROCEDURE display (p_prefix IN VARCHAR2,
p_data IN VARCHAR2) IS
-- --------------------------------------------------------------------------
l_data VARCHAR2(32767) := p_data;
BEGIN
g_last_prefix := p_prefix;
g_last_data := p_data;
IF g_show_output THEN
IF l_data IS NULL THEN
l_data := \'\';
END IF;
IF p_prefix IS NOT NULL THEN
l_data := p_prefix || \' : \' || l_data;
END IF;
IF g_show_date THEN
l_data := TO_CHAR(SYSTIMESTAMP, g_date_format) || \' : \' || l_data;
END IF;
IF Length(l_data) > g_max_width THEN
IF g_line_wrap THEN
wrap_line (l_data);
ELSE
l_data := SUBSTR(l_data, 1, g_max_width);
output (l_data);
END IF;
ELSE
output (l_data);
END IF;
END IF;
END;
-- --------------------------------------------------------------------------
-- --------------------------------------------------------------------------
PROCEDURE wrap_line (p_data IN VARCHAR2) IS
-- --------------------------------------------------------------------------
l_data VARCHAR2(32767) := p_data;
BEGIN
LOOP
display (NULL, SUBSTR(l_data, 1, g_max_width));
l_data := SUBSTR(l_data, g_max_width + 1);
EXIT WHEN l_data IS NULL;
END LOOP;
END;
-- --------------------------------------------------------------------------
-- --------------------------------------------------------------------------
PROCEDURE output (p_data IN VARCHAR2) IS
-- --------------------------------------------------------------------------
BEGIN
IF g_file_dir IS NULL OR g_file_name IS NULL THEN
DBMS_OUTPUT.put_line(p_data);
ELSE
DECLARE
l_file UTL_FILE.file_type;
BEGIN
l_file := UTL_FILE.fopen (g_file_dir, g_file_name, \'A\');
UTL_FILE.put_line(l_file, p_data);
UTL_FILE.fclose (l_file);
EXCEPTION
WHEN OTHERS THEN
UTL_FILE.fclose (l_file);
END;
END IF;
END;
-- --------------------------------------------------------------------------
END dsp;
/
err.pks
CREATE OR REPLACE PACKAGE err AS
-- --------------------------------------------------------------------------
-- Author : DR Timothy S Hall
-- Description : A simple mechanism for logging error information to a table.
-- Requirements : err.pkb, dsp.pks, dsp.pkb
-- --------------------------------------------------------------------------
PROCEDURE reset_defaults;
PROCEDURE logs_on;
PROCEDURE logs_off;
PROCEDURE set_date_format (p_date_format IN VARCHAR2 DEFAULT \'DD-MON-YYYY HH24:MI:SS\');
PROCEDURE line (p_prefix IN VARCHAR2,
p_data IN VARCHAR2,
p_error_level IN NUMBER DEFAULT 5,
p_error_user IN VARCHAR2 DEFAULT USER);
PROCEDURE line (p_data IN VARCHAR2,
p_error_level IN NUMBER DEFAULT 5,
p_error_user IN VARCHAR2 DEFAULT USER);
PROCEDURE display (p_error_level IN NUMBER DEFAULT NULL,
p_error_user IN VARCHAR2 DEFAULT NULL,
p_from_date IN DATE DEFAULT NULL,
p_to_date IN DATE DEFAULT NUll);
END err;
/
err.pkb
CREATE OR REPLACE PACKAGE BODY err AS
-- --------------------------------------------------------------------------
-- Author : DR Timothy S Hall
-- Description : A simple mechanism for logging error information to a table.
-- Requirements : err.pks, dsp.pks, dsp.pkb
-- --------------------------------------------------------------------------
-- Package Variables
g_logs_on BOOLEAN := TRUE;
g_date_format VARCHAR2(50) := \'DD-MON-YYYY HH24:MI:SS\';
-- Exposed Methods
-- --------------------------------------------------------------------------
PROCEDURE reset_defaults IS
-- --------------------------------------------------------------------------
BEGIN
g_logs_on := TRUE;
g_date_format := \'DD-MON-YYYY HH24:MI:SS\';
END;
-- --------------------------------------------------------------------------
-- --------------------------------------------------------------------------
PROCEDURE logs_on IS
-- --------------------------------------------------------------------------
BEGIN
g_logs_on := TRUE;
END;
-- --------------------------------------------------------------------------
-- --------------------------------------------------------------------------
PROCEDURE logs_off IS
-- --------------------------------------------------------------------------
BEGIN
g_logs_on := FALSE;
END;
-- --------------------------------------------------------------------------
-- --------------------------------------------------------------------------
PROCEDURE set_date_format (p_date_format IN VARCHAR2 DEFAULT \'DD-MON-YYYY HH24:MI:SS\') IS
-- --------------------------------------------------------------------------
BEGIN
g_date_format := p_date_format;
END;
-- --------------------------------------------------------------------------
-- --------------------------------------------------------------------------
PROCEDURE line (p_prefix IN VARCHAR2,
p_data IN VARCHAR2,
p_error_level IN NUMBER DEFAULT 5,
p_error_user IN VARCHAR2 DEFAULT USER) IS
-- --------------------------------------------------------------------------
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
IF g_logs_on THEN
INSERT INTO error_logs
(id,
prefix,
data,
error_level,
created_date,
created_by)
VALUES
(error_logs_seq.NEXTVAL,
p_prefix,
p_data,
p_error_level,
SYSDATE,
p_error_user);
COMMIT;
END IF;
END;
-- --------------------------------------------------------------------------
-- --------------------------------------------------------------------------
PROCEDURE line (p_data IN VARCHAR2,
p_error_level IN NUMBER DEFAULT 5,
p_error_user IN VARCHAR2 DEFAULT USER) IS
-- --------------------------------------------------------------------------
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
line (p_prefix => NULL,
p_data => p_data,
p_error_level => p_error_level,
p_error_user => p_error_user);
END;
-- --------------------------------------------------------------------------
-- --------------------------------------------------------------------------
PROCEDURE display (p_error_level IN NUMBER DEFAULT NULL,
p_error_user IN VARCHAR2 DEFAULT NULL,
p_from_date IN DATE DEFAULT NULL,
p_to_date IN DATE DEFAULT NUll) IS
-- --------------------------------------------------------------------------
CURSOR c_errors IS
SELECT *
FROM error_logs
WHERE error_level = NVL(p_error_level, error_level)
AND created_by = NVL(p_error_user, created_by)
AND created_date >= NVL(p_from_date, created_date)
AND created_date <= NVL(p_to_date, created_date)
ORDER BY id;
BEGIN
FOR cur_rec IN c_errors LOOP
dsp.line(cur_rec.prefix, cur_rec.data);
END LOOP;
END;
-- --------------------------------------------------------------------------
END err;
/
execute err.line(\'This is an error\');
@list_error_logs.sql
set feedback off
alter session set nls_timestamp_format=\'DD-MON-YYYY HH:MI:SS\';
set feedback on
set verify off
set linesize 640
column id format 99999
column prefix format a20
column data format a30
column created_date format a20
column created_by format a10
select
id,
prefix,
data,
error_level,
created_date,
created_by
from
error_logs
where
nvl(prefix, \'-\') = decode(upper(\'&1\'), \'ALL\', nvl(prefix, \'-\'), \'&1\')
order by
id
;
SQL> @list_error_logs.sql all
ID PREFIX DATA ERROR_LEVEL CREATED_DATE CREATED_BY
------ -------------------- ------------------------------ ----------- -------------------- ----------
1 This is an error 5 03.08.2012 21:38:26 SYS
1 row selected.
CREATE OR REPLACE PROCEDURE exception_job_proc AS
BEGIN
-- Force an error.
RAISE_APPLIcATION_ERROR(-20000, \'Forced error in exception_job_proc\');
EXCEPTION
WHEN OTHERS THEN
ERR.line(p_prefix => \'exception_job_proc\',
p_data => SQLERRM);
END exception_job_proc;
/
EXEC exception_job_proc
SQL> @list_error_logs.sql exception_job_proc
ID PREFIX DATA ERROR_LEVEL CREATED_DATE CREATED_BY
------ -------------------- ------------------------------ ----------- -------------------- ----------
2 exception_job_proc ORA-20000: Forced error in exc 5 03.08.2012 23:42:01 SYS
eption_job_proc
1 row selected.