error_log.ddl

/*----------------------------------------------------------------------------------------
-- FILE NAME        : error_log.ddl
-- Generated By     : Ex!lant Technologies Pvt Ltd.
-- Description      : Generic Error Logging utility
-- Date             : May 18, 2012
-- Author           : Ex!lant Development Team
-- Modification History:
------------------------------------------------------------------------------------------
--      When            Who                     What
------------------------------------------------------------------------------------------
--      18/05/2012      Ex! Dev Team            Initial Version
----------------------------------------------------------------------------------------*/
SET HEAD ON PAGES 3000 LINES 300 FEEDBACK ON

DROP TABLE error_log;
DROP PUBLIC SYNONYM error_log;
DROP SEQUENCE error_seq;
DROP PUBLIC SYNONYM error_seq;
DROP PROCEDURE p_log_error;
DROP PUBLIC SYNONYM p_log_error;

CREATE SEQUENCE error_seq 
START WITH 1 INCREMENT BY 1 CACHE 100 NOMAXVALUE;

CREATE OR REPLACE PUBLIC SYNONYM error_seq FOR error_seq;

CREATE TABLE error_log
( error_id        NUMBER NOT NULL,
  error_cd        INTEGER NOT NULL,
  error_msg       VARCHAR2 (4000) NOT NULL,
  program_err_msg VARCHAR2 (1000) NOT NULL,
  backtrace       CLOB,
  callstack       CLOB,
  cre_dt          DATE DEFAULT SYSDATE NOT NULL,
  program_nm      VARCHAR2 (30) NOT NULL,
  cre_user        VARCHAR2 (30) DEFAULT USER NOT NULL,
  CONSTRAINT pk_error_log PRIMARY KEY ( cre_dt,program_nm,error_id ) USING INDEX TABLESPACE ext_data_ts LOCAL
) 
PARTITION BY RANGE(cre_dt) 
INTERVAL(NUMTOYMINTERVAL(1, 'MONTH'))
 ( PARTITION p0 VALUES LESS THAN (TO_DATE('01-01-2012','DD-MM-YYYY')) COMPRESS)
TABLESPACE ext_data_ts;


CREATE OR REPLACE PUBLIC SYNONYM error_log FOR error_log;

CREATE OR REPLACE PROCEDURE p_log_error ( in_v_err_msg IN  error_log.program_err_msg%TYPE,
                                          in_v_program_nm IN error_log.program_nm%TYPE )
AUTHID CURRENT_USER AS
    l_code   PLS_INTEGER := SQLCODE;
    l_mesg  VARCHAR2(32767) := SQLERRM; 
    PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
    INSERT INTO error_log ( error_id,
                            error_cd,
                            error_msg,
                            program_err_msg,
                            backtrace,
                            callstack,
                            program_nm
                          )
        VALUES ( error_seq.NEXTVAL,
                 l_code,
                 l_mesg, 
                 TRIM(in_v_err_msg),  
                 SYS.DBMS_UTILITY.format_error_backtrace,
                 SYS.DBMS_UTILITY.format_call_stack,
                 in_v_program_nm);
   COMMIT;
END;
/

CREATE OR REPLACE PUBLIC SYNONYM p_log_error FOR p_log_error;

GRANT EXECUTE ON p_log_error TO PUBLIC;
GRANT SELECT ON error_seq TO PUBLIC;
GRANT INSERT ON error_log TO PUBLIC;