Consistent error logging greatly simplifies PL/SQL debugging

 

CREATE TABLE ERRORLOG
(
  ID              NUMBER               NOT NULL,
  TIME            DATE                 NOT NULL,
  CODE            VARCHAR2(9 BYTE)     NOT NULL,
  MESSAGE         VARCHAR2(2000 BYTE)  NOT NULL,
  PACKAGE_NAME    VARCHAR2(100 BYTE),
  PROCEDURE_NAME  VARCHAR2(100 BYTE)   NOT NULL,
  LOCATION        NUMBER,
  PARAMETERS      VARCHAR2(4000 BYTE)
);

CREATE SEQUENCE ERRORLOG_ID_SEQ
  MINVALUE 1
  MAXVALUE 999999999999999999999999999
  INCREMENT BY 1
  START WITH 1
  CACHE 20
  NOORDER
  NOCYCLE ;
  
CREATE OR REPLACE TRIGGER T_ERRORLOG_BI
BEFORE INSERT
ON ERRORLOG
REFERENCING OLD AS OLD NEW AS NEW
FOR EACH ROW
begin
  if :new.id is null then
    select errorlog_id_seq.nextval
      into :new.id
      from dual;
  end if;
end t_errorlog_bi;

ALTER TABLE ERRORLOG ADD (PRIMARY KEY (ID));


CREATE OR REPLACE package pkg_error is  

  procedure log (p_error_code errorlog.code%type,
                 p_error_message errorlog.message%type,
                 p_package errorlog.package_name%type default null,
                 p_procedure errorlog.procedure_name%type,
                 p_location errorlog.location%type default null,
                 p_parameters errorlog.parameters%type default null);  

end pkg_error;

CREATE OR REPLACE package body pkg_error is  

  procedure log (p_error_code errorlog.code%type,
                 p_error_message errorlog.message%type,
                 p_package errorlog.package_name%type default null,
                 p_procedure errorlog.procedure_name%type,
                 p_location errorlog.location%type default null,
                 p_parameters errorlog.parameters%type default null) is  

  pragma autonomous_transaction;  

   begin  

    insert
      into errorlog
           (time,
            code,
            message,
            package_name,
            procedure_name,
            location,
            parameters)
    values (sysdate,
            p_error_code,
            p_error_message,
            p_package,
            p_procedure,
            p_location,
            p_parameters);
    commit;
  end log;  

end pkg_error;

CREATE OR REPLACE FUNCTION hello_world RETURN VARCHAR2 IS
  v_procedure errorlog.procedure_name%TYPE default 'hello_world';
  v_location errorlog.location%TYPE;
BEGIN  

  v_location := 1000;
  return ('hello, world');  

EXCEPTION  

  when others then
    pkg_error.log(p_error_code => substr(sqlerrm,1,9),
    p_error_message => substr(sqlerrm,12),
    p_package => 'FUNCTION',
    p_procedure => v_procedure,
    p_location => v_location);  

END;

http://blogs.techrepublic.com.com/programming-and-development/?p=400

http://www.fast-track.cc/t_lessons_autonomous.htm