Wednesday, April 01, 2009

PL/SQL instrument utility u$err package

Purpose
-------
1. PL/SQL utility to Instrument and Trace.
2. Err_Trap.

debug.f() write to db server trace file
u$err.trc() write to utility.error_log table, with autonomous transaction.

u$err.err() write error stack to utility.error_log table, and DBMS_OUTPUT to std_io.

File: file://A:/PLSQL/debugf/u$err_pkg.sql

Setup
-----

drop table utility.error_log;
create table utility.error_log
(err_code varchar2(10), err_module varchar2(30), err_action varchar2(30),
create_date date default SYSDATE, create_user varchar2(30) default USER,
err_text varchar2(4000)
)
partition by range (create_date) INTERVAL(NUMTOYMINTERVAL(1, 'MONTH'))
(
PARTITION p0 VALUES LESS THAN (TO_DATE('1-1-2009', 'DD-MM-YYYY'))
)
;

grant select on utility.error_log to qa_admin;


Code
----

CREATE OR REPLACE PACKAGE utility.u$err AS

/***
DEFUG.F : log trace information
***/

PROCEDURE trc (p_msg IN VARCHAR2);

PROCEDURE err (p_msg IN VARCHAR2 default '');

END;
/

CREATE OR REPLACE PACKAGE BODY utility.u$err AS

/***
Goal: 1. Trace, 2. Err_Trap

Who| Date | What
Charlie(Yi 木匠) | 12-June-2008 | combine FORMAT_ERROR_BACKTRACE and DBMS_UTILITY.FORMAT_ERROR_STACK to one string

DEFUG.F

set serveroutput on size 200000

alter package utility.u$err compile;
grant execute on utility.u$err to public;
create or replace public synonym u$err for utility.u$err;

File: A:\PLSQL\debugf\u$err_pkg.sql

***/

PROCEDURE trc (p_msg IN VARCHAR2)
IS
PRAGMA AUTONOMOUS_TRANSACTION;
ls_module varchar2(32767);
ls_action varchar2(32767);
ls_code varchar2(10);
BEGIN
DBMS_APPLICATION_INFO.READ_MODULE (
module_name => ls_module,
action_name => ls_action);
ls_code := to_char(sqlcode);

INSERT INTO error_log (err_code,err_module,err_action,err_text)
VALUES(ls_code,substr(ls_module,1,30),substr(ls_action,1,30), p_msg);
COMMIT;
DBMS_OUTPUT.PUT_LINE('trc:'||ls_code||':'||ls_action||':'||ls_module||':'||p_msg);
EXCEPTION
WHEN OTHERS THEN
ROLLBACK;
DBMS_OUTPUT.PUT_LINE('Error in log_err: '||SUBSTR(SQLERRM,1,120));
END;

PROCEDURE err (p_msg IN VARCHAR2)
IS
ls_err varchar2(32767);
BEGIN
ls_err := SubStr(p_msg||'.'||DBMS_UTILITY.FORMAT_ERROR_BACKTRACE||'<---'||
chr(10)||DBMS_UTILITY.FORMAT_ERROR_STACK, 1, 2000);
debug.f(ls_err);
u$err.trc (ls_err);
DBMS_APPLICATION_INFO.SET_MODULE(Null, Null);
EXCEPTION
WHEN OTHERS THEN
u$err.trc (SubStr(DBMS_UTILITY.FORMAT_ERROR_STACK,1,500));
DBMS_OUTPUT.PUT_LINE('Error stack in log_err: '||SUBSTR(SQLERRM,1,120));
END;

END;
/

alter package utility.u$err compile;
grant execute on utility.u$err to public;
create or replace public synonym u$err for utility.u$err;



Test u$err.err and u$err.trc
----
exec u$err.err;

declare
li pls_integer;
procedure p_in
is
begin
li := 5 / 0;
exception
when others then
u$err.err;
raise;
end;

begin
u$err.trc('start');
p_in;
u$err.trc('end');
exception
when others then
u$err.err('outer error');
u$err.trc('before raise');
raise;
u$err.trc('after raise');
end;
/

-- Query data
select * from
(
select * from utility.error_log
-- PARTITION FOR(to_date('11-mar-2009','dd-mon-yyyy'))
order by create_date desc
) where rownum < 10;


Housekeeping
----
--Purge old log
delete utility.error_log where create_date < sysdate - 100;
commit;

ALTER TABLE utility.error_log DROP PARTITION FOR(to_date('11-mar-2009','dd-mon-yyyy'));



Reference
---------
Oracle PL/SQL Best Practices - Steven Feuerstein
http://www.oreilly.com/catalog/9780596514105
Post a Comment