The log mechanism

The log engine, it’s a mechanism that presents a large variety of solutions.
Every project, software or company have a different way of register the actions of their processes.

Whenever a new version of Oracle is released, one of my concerns is trying to improve my own logging engine with the new features.
But let’s by stages, my log engine is based in the following objects:

• 3 tables :”APPL_LOG”, “SYS_LOG” and “APPL_LOG_TYPE”
• 1 package :”PKG_APPL_LOG”
• 2 sequences:”SEQ_LOG_ID” and “SEQ_SYS_LOG_ID”
• 4 triggers :”TR_APPL_LOG_INS”,”TR_APPL_LOG_UPD”, “TR_SYS_LOG_INS” and “TR_SYS_LOG_UPD”

• Table “APPL_LOG”
  The purpose of this table is to register all process/applications actions. As this table will be increase a lot, i decided to partition by date and sub-partition by type of error.

  CREATE TABLE APPL_LOG
  (LOG_ID        NUMBER,
   LOG_DATE      TIMESTAMP,
   SEQ_ID        NUMBER,
   TYPE_ID       NUMBER,
   APPLICATION   VARCHAR2(256),
   OBJECT        VARCHAR2(32),
   MESSAGE       VARCHAR2(256),
   CREATED_USER  VARCHAR2(64),
   CREATED_DATE  TIMESTAMP,
   MODIFIED_DATE TIMESTAMP,
   MODIFIED_USER VARCHAR2(64)
  )
   PARTITION BY RANGE(LOG_DATE)
   SUBPARTITION BY LIST (TYPE_ID)
   SUBPARTITION TEMPLATE(
   SUBPARTITION critical      VALUES (1)  TABLESPACE TBS1,
   SUBPARTITION error1        VALUES (2)  TABLESPACE TBS2,
   SUBPARTITION warning       VALUES (3) TABLESPACE TBS3,
   SUBPARTITION information   VALUES (4) TABLESPACE TBS4,
   SUBPARTITION monitor       VALUES (5) TABLESPACE TBS5,
   SUBPARTITION debugging     VALUES (6) TABLESPACE TBS6
  )
  (
   PARTITION p_08052011 VALUES LESS THAN (TO_DATE('09/05/2011','DD/MM/YYYY')),
   PARTITION p_09052011 VALUES LESS THAN (TO_DATE('10/05/2011','DD/MM/YYYY')),
   PARTITION p_10052011 VALUES LESS THAN (TO_DATE('11/05/2011','DD/MM/YYYY'))
  );
  
  ALTER TABLE APPL_LOG  ADD CONSTRAINT PK_APPL_LOG_ID PRIMARY KEY (LOG_ID);
  

  Below there are listed the possible types of error:

  1. Critical
  2. Error
  3. Warning
  4. Information
  5. Monitoring
  6. Debug

  CREATE TABLE APPL_LOG_TYPE
  (
    TYPE_ID          NUMBER,
    DESCRIPTION      VARCHAR2(128),
    CREATED_USER     VARCHAR2(64),
    CREATED_DATE     DATE,
    MODIFIED_USER    VARCHAR2(64),
    MODIFIED_DATE    DATE
  );
  ALTER TABLE APPL_LOG_TYPE
    ADD CONSTRAINT PK_TYPE_ID PRIMARY KEY (TYPE_ID);
  
  INSERT INTO APPL_LOG_TYPE(TYPE_ID,DESCRIPTION,CREATED_USER,CREATED_DATE,
  MODIFIED_USER,MODIFIED_DATE)
  VALUES(1,'Critical','DB24',SYSDATE,'DB24',SYSDATE);
  
  INSERT INTO APPL_LOG_TYPE(TYPE_ID,DESCRIPTION,CREATED_USER,CREATED_DATE,
  MODIFIED_USER,MODIFIED_DATE)
  VALUES(2,'Error','DB24',SYSDATE,'DB24',SYSDATE);
  
  INSERT INTO APPL_LOG_TYPE(TYPE_ID,DESCRIPTION,CREATED_USER,CREATED_DATE,
  MODIFIED_USER,MODIFIED_DATE
  VALUES(3,'Warning','DB24',SYSDATE,'DB24',SYSDATE);
  
  INSERT INTO APPL_LOG_TYPE(TYPE_ID,DESCRIPTION,CREATED_USER,CREATED_DATE,
  MODIFIED_USER,MODIFIED_DATE)
  VALUES(4,'Information','DB24',SYSDATE,'DB24',SYSDATE);
  
  INSERT INTO APPL_LOG_TYPE(TYPE_ID,DESCRIPTION,CREATED_USER,CREATED_DATE,
  MODIFIED_USER,MODIFIED_DATE)
  VALUES(5,'Monitoring','DB24',SYSDATE,'DB24',SYSDATE);
  
  INSERT INTO APPL_LOG_TYPE(TYPE_ID,DESCRIPTION,CREATED_USER,CREATED_DATE,
  MODIFIED_USER,MODIFIED_DATE)
  VALUES(6,'Debugging','DB24',SYSDATE,'DB24',SYSDATE);
   

• Table “SYS_LOG”
  This table only will be used in the package “PKG_APPL_LOG” on situations of error: caught by exceptions.Below it’s presented the statement of creation for the table “SYS_LOG”, which only be partitioned by date(“SYS_LOG_DATE”):

  CREATE TABLE SYS_LOG
  (SYS_LOG_ID        NUMBER,
   SYS_LOG_DATE      DATE,
   OBJECT_NAME       VARCHAR2(32),
   MESSAGE           VARCHAR2(256),
   CREATED_USER      VARCHAR2(64),
   CREATED_DATE      DATE,
   MODIFIED_DATE     DATE,
   MODIFIED_USER     VARCHAR2(64)
   )
   PARTITION BY RANGE(SYS_LOG_DATE)
  (
   PARTITION p_sys_08052011 VALUES LESS THAN (TO_DATE('09/05/2011','DD/MM/YYYY')),
   PARTITION p_sys_09052011 VALUES LESS THAN (TO_DATE('10/05/2011','DD/MM/YYYY')),
   PARTITION p_sys_10052011 VALUES LESS THAN (TO_DATE('11/05/2011','DD/MM/YYYY'))
  );
  ALTER TABLE SYS_LOG  ADD CONSTRAINT PK_SYS_LOG_ID PRIMARY KEY (SYS_LOG_ID) LOCAL;
  

• Sequence “SEQ_LOG_ID” and “SEQ_LOG_SYS_ID”
  As it’s obvious the aim of this sequence is which can be  used in the table “APPL_LOG”.

       CREATE SEQUENCE SEQ_LOG_ID
       MINVALUE 1
       MAXVALUE 999999999999999999999999999
       START WITH 1
       INCREMENT BY 1
       CACHE 100;
      

  By another side the sequence “SEQ_LOG_SYS_ID” only will be used in the “SYS_LOG” table.

       CREATE SEQUENCE SEQ_SYS_LOG_ID
       MINVALUE 1
       MAXVALUE 999999999999999999999999999
       START WITH 1
       INCREMENT BY 1
       CACHE 100;
      

• Triggers “TR_APPL_LOG_INS”, “TR_APPL_LOG_UPD”, “TR_SYS_LOG_INS” and “TR_SYS_LOG_UPD”
  The trigger “TR_APPL_LOG_INS” is fired for each row whenever a insert statement is performed on the table “TR_APPL_LOG_INS”,
  and the main idea is to correct the “LOG_ID” value when it is not specified correctly.In the snippet of code for this trigger it’s possible to find at the end, the assignment of the LOG_ID value to the “SEQ_ID” field.

   CREATE OR REPLACE TRIGGER TR_APPL_LOG_INS
   BEFORE INSERT ON APPL_LOG
   FOR EACH ROW
   DECLARE
    ln_log_id NUMBER;
   BEGIN
    IF ( :NEW.LOG IS NULL ) THEN
      SELECT SEQ_LOG_ID.NEXTVAL
             INTO ln_log_id
        FROM DUAL;
     :NEW.LOG_ID=ln_lg_id;
    END IF;
  
    :NEW.SEQ_ID :=:NEW:LOG_ID;
   END;
   

  I thought in the field “SEQ_ID” with a specified goal but currently is not being used
  for anything, just get the same value of the field “LOG_ID”.

  CREATE OR REPLACE TRIGGER TR_APPL_LOG_UPD
  BEFORE UPDATE ON APPL_LOG
  FOR EACH ROW
  BEGIN
    :new.MODIFIED_USER := USER;
    :new.MODIFIED_DATE := TO_DATE(TO_CHAR(SYSDATE,'yy/mm/yyyy hh24:mi:ss'),'yy/mm/yyyy hh24:mi:ss');
  END;
  

  The purpose of the others two triggers : “TR_SYS_LOG_INS” and “TR_SYS_LOG_UPD”, it’s similiar of these but over the table “SYS_LOG”:

   CREATE OR REPLACE TRIGGER TR_SYS_LOG_INS
   BEFORE INSERT ON SYS_LOG
   FOR EACH ROW
   DECLARE
    ln_log_id NUMBER;
   BEGIN
    IF ( :NEW.LOG IS NULL ) THEN
      SELECT SEQ_SYS_LOG_ID.NEXTVAL
             INTO ln_log_id
        FROM DUAL;
     :NEW.SYS_LOG_ID=ln_lg_id;
    END IF;
  
   END;
   

  CREATE OR REPLACE TRIGGER TR_SYS_LOG_UPD
  BEFORE UPDATE ON SYS_LOG
  FOR EACH ROW
  BEGIN
    :new.MODIFIED_USER := USER;
    :new.MODIFIED_DATE := TO_DATE(TO_CHAR(SYSDATE,'yy/mm/yyyy hh24:mi:ss'),'yy/mm/yyyy hh24:mi:ss');
  END;
  

• Package “PKG_APPL_LOG”
  This package “PKG_APPL_LOG” is very simple, only has two procedure, and the only one particularity is the use
  of “pragma AUTONOMOUS_TRANSACTION” in the procedure “APPL_LOG”.

  CREATE OR REPLACE PACKAGE PKG_APPL_LOG AS
  
    PROCEDURE APPL_LOG (
                         ps_user          IN VARCHAR2,
                         pn_type_id       IN NUMBER,
                         ps_application   IN VARCHAR2,
                         ps_object        IN VARCHAR2,
                         ps_message       IN VARCHAR2,
                         ps_log_id        IN OUT NUMBER
                         );
  
  PROCEDURE UPDATE_LOG(
                        pn_log_id         IN NUMBER,
                        ps_log_date       IN DATE     DEFAULT NULL,
                        pn_seq_id         IN NUMBER   DEFAULT NULL,
                        pn_type_id        IN NUMBER   DEFAULT NULL,
                        ps_application    IN VARCHAR2 DEFAULT NULL,
                        ps_object         IN VARCHAR2 DEFAULT NULL,
                        ps_message        IN VARCHAR2 DEFAULT NULL,
                        ps_create_user    IN VARCHAR2 DEFAULT NULL,
                        pd_create_date    IN DATE     DEFAULT NULL
                       ) ;
  
  END PKG_APPL_LOG;
  
  CREATE OR REPLACE PACKAGE BODY PKG_APPL_LOG AS
  
  GKD_DATE_TIME_FORMAT CONSTANT VARCHAR2(32) := 'dd/mm/yyyy hh24:mi:ss';
  GKS_PACKAGE_NAME     CONSTANT VARCHAR2(32) := 'PKG_APPL_LOG';
   GKS_USER_NAME       CONSTANT VARCHAR2(32) := 'LOG';
  
   PROCEDURE APPL_LOG   ( ps_user           IN VARCHAR2,
                          pn_type_id        IN NUMBER,
                          ps_application    IN VARCHAR2,
                          ps_object         IN VARCHAR2,
                          ps_message        IN VARCHAR2,
                          ps_log_id         IN OUT NUMBER
                                    ) IS
      PRAGMA AUTONOMOUS_TRANSACTION;
      LN_LOG_ID                  NUMBER;
      LS_LOG_DATE              VARCHAR2(64) := TO_CHAR(SYSDATE,gkd_date_time_format);
      LKS_PROCEDURE_NAME  CONSTANT VARCHAR2(32) := 'APPL_LOG';
      LS_ERROR_MSG            VARCHAR2(256);
    BEGIN
      SELECT SEQ_LOG_ID.NEXTVAL
        INTO ln_log_id
      FROM DUAL;
  
      INSERT INTO APPL_LOG
                          (LOG_ID,
                           LOG_DATE,
                           SEQ_ID,
                           TYPE_ID,
                           APPLICATION,
                           OBJECT,
                           MESSAGE,
                           CREATED_USER,
                           CREATED_DATE,
                           MODIFIED_USER,
                           MODIFIED_DATE
                          )
      VALUES(
             ln_log_id,
             TO_DATE(ls_log_date,gkd_date_time_format),
             NULL,
             pn_type_id,
             ps_application,
             ps_object,
             ps_message,
             ps_user,
             TO_DATE(ls_log_date,gkd_date_time_format),
             ps_user,
             TO_DATE(ls_log_date,gkd_date_time_format)
            );
  
      COMMIT;
  
      ps_log_id := ln_log_id;
  
    EXCEPTION
      WHEN OTHERS THEN
        ls_error_msg := SUBSTR(SQLERRM, 1, 255);
        ROLLBACK;
  
        SELECT SEQ_SYS_LOG_ID.NEXTVAL INTO ln_log_id
          FROM DUAL ;
  
        INSERT INTO SYS_LOG(
                             SYS_LOG_ID   ,
                             SYS_LOG_DATE ,
                             APPLICATION  ,
                             OBJECT       ,
                             MESSAGE      ,
                             CREATED_USER ,
                             CREATED_DATE ,
                             MODIFIED_USER ,
                             MODIFIED_DATE
  
                           )
                   VALUES(
                           ln_log_id,
                           TO_DATE(ls_log_date,gkd_date_time_format),
                           gks_package_name,
                           lks_procedure_name,
                           ls_error_msg,
                           gks_user_name,
                           TO_DATE(ls_log_date,gkd_date_time_format),
                           gks_user_name,
                           TO_DATE(ls_log_date,gkd_date_time_format)
                           );
  
        COMMIT;
    END APPL_LOG;
  
  PROCEDURE UPDATE_LOG(
                        pn_log_id        IN NUMBER,
                        ps_log_date      IN DATE     DEFAULT NULL,
                        pn_seq_id        IN NUMBER   DEFAULT NULL,
                        pn_type_id       IN NUMBER   DEFAULT NULL,
                        ps_application   IN VARCHAR2 DEFAULT NULL,
                        ps_object        IN VARCHAR2 DEFAULT NULL,
                        ps_message       IN VARCHAR2 DEFAULT NULL,
                        ps_create_user   IN VARCHAR2 DEFAULT NULL,
                        pd_create_date   IN DATE     DEFAULT NULL
                         ) IS
      PRAGMA AUTONOMOUS_TRANSACTION;
   LD_LOG_DATE DATE := TO_DATE(TO_CHAR(SYSDATE,gkd_date_time_format),gkd_date_time_format);
   LKS_PROCEDURE_NAME  CONSTANT VARCHAR2(32) := 'UPDATE_LOG';
   LS_ERROR_MSG            VARCHAR2(256);
   LN_LOG_ID                  NUMBER;
  BEGIN
      UPDATE APPL_LOG SET
        LOG_DATE      = COALESCE(ps_log_date,LOG_DATE),
        SEQ_ID        = COALESCE(pn_seq_id,SEQ_ID),
        TYPE_ID       = COALESCE(pn_type_id,TYPE_ID),
        APPLICATION   = COALESCE(ps_application,APPLICATION),
        OBJECT        = COALESCE(ps_object,OBJECT),
        MESSAGE       = COALESCE(ps_message,MESSAGE),
        CREATED_USER  = COALESCE(ps_create_user,CREATED_USER),
        CREATED_DATE  = COALESCE(pd_create_date,CREATED_DATE),
        MODIFIED_DATE = ld_log_date,
        MODIFIED_USER = USER
      WHERE LOG_ID = pn_log_id ;
  
      COMMIT;
  
    EXCEPTION
      WHEN OTHERS THEN
        ls_error_msg := SUBSTR(SQLERRM, 1, 255);
        ROLLBACK;
  
        SELECT SEQ_SYS_LOG_ID.NEXTVAL INTO ln_log_id
          FROM DUAL ;
  
        INSERT INTO SYS_LOG(
                             SYS_LOG_ID   ,
                             SYS_LOG_DATE ,
                             APPLICATION  ,
                             OBJECT       ,
                             MESSAGE      ,
                             CREATED_USER ,
                             CREATED_DATE ,
                             MODIFIED_DATE,
                             MODIFIED_USER
                           )
                   VALUES(
                           ln_log_id,
                           ld_log_date,
                           gks_package_name,
                           lks_procedure_name,
                           ls_error_msg,
                           gks_user_name,
                           ld_log_date,
                           gks_user_name,
                           ld_log_date
                           );
  
        COMMIT;
  
  END UPDATE_LOG;
  
  END PKG_APPL_LOG;
  

  The exception will only be executed whether there is any error in
  insert statement(e.g. full tablespace or some variables with null value)

  The procedure “APPL_LOG” has a OUT parameter : “pn_log_id”, that represents the identifier of registered action of log, which can be used in the management of the procedure that invokes this log package or in a future query on the table “APPL_LOG”.

  The procedure “UPDATE_LOG” should be used to update any filed of the table “APPL_LOG”, through the log identifier : “LOG_ID”.

 

I hope that this my explanation may have been useful for someone, but I also ask your opinion based on experience and theory in order to improve this post and this engine.

 

 

Segment,
“Sometimes a few words at the right time can inspire you, pick you up and point you toward a future worth living.”