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