logging_solution.sql
 Back to Parent Page
/*********** Logging solution for Oracle db ***********/
/**************  Logging tables ***********************/
CREATE TABLE "LOG_SESSION"
("IID" NUMBER NOT NULL,
 "DATE_STAMP" DATE NOT NULL,
 "DURATION"   NUMBER DEFAULT 0,
 "USER_NAME"  VARCHAR2(64) NOT NULL,
 "COMMENTS"   VARCHAR(255) DEFAULT 'Uninitialized',
 CONSTRAINT "IP_LOGSESSION" PRIMARY KEY("IID"))
 TABLESPACE "USERS" PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
 STORAGE ( INITIAL 10M NEXT 2M MINEXTENTS 1 MAXEXTENTS 121
           PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1)
/
CREATE TABLE "LOG_DETAILS"
("IID" NUMBER NOT NULL,
 "PID" NUMBER NOT NULL,
 "MSG_TYPE"   VARCHAR2(1) NOT NULL,
 "DATE_STAMP" DATE NOT NULL,
 "COMMENTS"   LONG,
 CONSTRAINT "IP_LOG_DETAILS" PRIMARY KEY("IID"),
 CONSTRAINT "FK_LOG_DETAILS" FOREIGN KEY("PID") REFERENCES "LOG_SESSION"("IID") ON DELETE CASCADE,
 CONSTRAINT "CHECK_MSG_TYPE" CHECK(MSG_TYPE='E' OR MSG_TYPE='W' OR MSG_TYPE='I'))
 TABLESPACE "USERS" PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
 STORAGE ( INITIAL 50M NEXT 5M MINEXTENTS 1 MAXEXTENTS 121
           PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1)
/
CREATE INDEX "I_LOG_DETAIL" ON "LOG_DETAILS"("PID") TABLESPACE "INDX" STORAGE(INITIAL 10 M)
/
CREATE TABLE "PROTOCOL"(
 "IID" NUMBER NOT NULL,
 "PID" NUMBER NOT NULL,
 "DATE_STAMP" DATE NOT NULL,
 "ACTION" VARCHAR2(1) NOT NULL,
 "TABLE_NAME" VARCHAR2(32) NOT NULL,
 "KEY_VALUE" VARCHAR2(64) NOT NULL,
 "FIELD_CHANGES" "T_FIELD_CHANGES" DEFAULT T_FIELD_CHANGES(),
 CONSTRAINT "IP_PROTOCOL" PRIMARY KEY("IID"),
 CONSTRAINT "CHECK_ACTION" CHECK(ACTION='I' OR ACTION='U' OR ACTION='D'),
 CONSTRAINT "FK_PROTOCOL1" FOREIGN KEY("PID") REFERENCES "LOG_SESSION"("IID") ON DELETE CASCADE)
TABLESPACE "USERS" PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
STORAGE ( INITIAL 512M NEXT 64M MINEXTENTS 1 MAXEXTENTS 121
          PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1)
NESTED TABLE FIELD_CHANGES STORE AS PROTOCOL_STORE(
     (PRIMARY KEY(NESTED_TABLE_ID, FIELD_NAME)))
/
CREATE INDEX "I_PROTOCOL_PID" ON "PROTOCOL"("PID") TABLESPACE "INDX" STORAGE(INITIAL 75 M)
/
CREATE INDEX "I_PROTOCOL_TBL" ON "PROTOCOL"("TABLE_NAME") TABLESPACE "INDX" STORAGE(INITIAL 34 M)
/
CREATE INDEX "I_PROTOCOL_DATE" ON "PROTOCOL"("DATE_STAMP") TABLESPACE "INDX" STORAGE(INITIAL 24 M)
/
CREATE INDEX "I_PROTOCOL_ACTION" ON "PROTOCOL"("ACTION") TABLESPACE "INDX" STORAGE(INITIAL 3500 K)
/
CREATE INDEX "I_PROTOCOL_KEY" ON "PROTOCOL"("SAFE_TO_NUMBER"("KEY_VALUE")) TABLESPACE "INDX" STORAGE(INITIAL 75 M)
/
CREATE OR REPLACE PACKAGE "LOGPROCS"
IS
/*---------------------------------------------------------
    Package to support loging by sessions
    Developed by Alex Feigin 2001 ã.
  ---------------------------------------------------------*/
 V_LOG_ID NUMBER:=-1;
 V_START_TIME DATE:=SYSDATE;
 PROCEDURE  START_LOGGING;
 PROCEDURE  POST_DETAIL_MSG(MSG_TYPE VARCHAR2,V_COMMENT VARCHAR2);
 PROCEDURE  POST_LOGGING(V_COMMENT VARCHAR2);
 PROCEDURE  REG_DB_CHANGES(V_ACTION VARCHAR2,V_TABLE_NAME VARCHAR2,V_KEY_VALUE VARCHAR2, V_CHANGES T_FIELD_CHANGES);
 PROCEDURE  PUSH_LOGGING(F_LOG_ID IN OUT NUMBER,F_START_TIME IN OUT DATE);
 PROCEDURE  POP_LOGGING(F_LOG_ID NUMBER,F_START_TIME DATE);
END;
/

CREATE OR REPLACE PACKAGE BODY "LOGPROCS"
IS
/*---------------------------------------------------------
  Procedure to start the sesssion
  ---------------------------------------------------------*/
 PROCEDURE START_LOGGING
  IS
   CNT INTEGER;
   BEGIN
    IF(LOGPROCS.V_LOG_ID<>-1) THEN
     SELECT COUNT(*) INTO CNT FROM LOG_SESSION
      WHERE IID=LOGPROCS.V_LOG_ID;
     IF(CNT<>0) THEN
      raise_application_error(-20106,'Log session has been runned already');
     END IF;
    END IF;
    SELECT LOG_SEQ.NEXTVAL INTO LOGPROCS.V_LOG_ID FROM DUAL;
    LOGPROCS.V_START_TIME:=SYSDATE;
    INSERT INTO LOG_SESSION(IID,DATE_STAMP,USER_NAME)
      VALUES(LOGPROCS.V_LOG_ID,LOGPROCS.V_START_TIME,USER);
       END;

/*---------------------------------------------------------
    Procedure to post details message
    Input parameter :
      MSG_TYPE - message type ('E'-error,'W'-warning,
      'I'-information)
      V_COMMENT - comment
  ---------------------------------------------------------*/
 PROCEDURE POST_DETAIL_MSG(MSG_TYPE VARCHAR2,V_COMMENT VARCHAR2)
  IS
   BEGIN
    INSERT INTO LOG_DETAILS
     VALUES(LOG_DETAILS_SEQ.NEXTVAL,LOGPROCS.V_LOG_ID,MSG_TYPE,SYSDATE,V_COMMENT);
   END;

/*---------------------------------------------------------
  Procedure to finish the session
  Input paramter :
      V_COMMENT - comment
  ---------------------------------------------------------*/
 PROCEDURE POST_LOGGING(V_COMMENT VARCHAR2)
  IS
    V_DURATION NUMBER;
   BEGIN
    V_DURATION:=TRUNC((SYSDATE-LOGPROCS.V_START_TIME)*86400.0);
    UPDATE LOG_SESSION
     SET   COMMENTS=V_COMMENT,
           DURATION=V_DURATION
    WHERE  IID=LOGPROCS.V_LOG_ID;
    V_LOG_ID:=-1;   
   END;

/*---------------------------------------------------------
  Procedure to register changes in protocol table
  Input parameters :
      V_ACTION - DB event type
      ('I'-insert, 'U'-update, 'D'-delete)
      V_TABLE_NAME - table name
      V_KEY_VALUE - key value
      V_CHANGES - changes collection
  ---------------------------------------------------------*/
  PROCEDURE REG_DB_CHANGES(V_ACTION VARCHAR2,V_TABLE_NAME VARCHAR2,V_KEY_VALUE VARCHAR2, V_CHANGES T_FIELD_CHANGES)
   IS
    BEGIN
     INSERT INTO PROTOCOL(IID,PID,DATE_STAMP,ACTION,TABLE_NAME,KEY_VALUE, FIELD_CHANGES)
      VALUES(PROTOCOL_SEQ.NEXTVAL,LOGPROCS.V_LOG_ID,SYSDATE,V_ACTION,V_TABLE_NAME,V_KEY_VALUE, V_CHANGES);
    END;

/*---------------------------------------------------------
  Reset of session ID
  Input parameters :
    F_LOG_ID - current session ID
    F_START_TIME - date/tie of the logging start
 ---------------------------------------------------------*/
 PROCEDURE PUSH_LOGGING(F_LOG_ID IN OUT NUMBER,F_START_TIME IN OUT DATE)
  IS
   BEGIN
    F_LOG_ID:=LOGPROCS.V_LOG_ID;
    F_START_TIME:=LOGPROCS.V_START_TIME;
    LOGPROCS.V_LOG_ID:=-1;
   END;

/*---------------------------------------------------------
  Restore of the session ID
  Input parameter :
    F_LOG_ID - session ID to restore
    F_START_TIME - date/time of the start of the session to restore
 ---------------------------------------------------------*/
 PROCEDURE POP_LOGGING(F_LOG_ID NUMBER,F_START_TIME DATE)
  IS
   BEGIN
    LOGPROCS.V_LOG_ID:=F_LOG_ID;
    LOGPROCS.V_START_TIME:=F_START_TIME;
   END;                                           
END;
/
EXIT
/
 

 Back to Parent Page