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

String decomposition

A few days ago I needed to split a string into several fields, obviously with a pre-defined separator character (semicolon).

My purpose it was decompose the following text :

‘5/10/2007;;”Ronald Reed “;211001;;-75.00’

in the following fields :

5/10/2007
null
Ronal Reed
211001
null
-75.00

Purity level of packages

Until the Oracle 8i release the purity level was necessary to define by the “PRAGMA RESTRICT_REFRENCES” compiler directive in the specification body. After the version 9i this kind of statement it became obsolete, however in some situations can still be useful or can be necessary understanding other packages already developed that using this type of assert.

The purity level defines what types of actions are allowed for the functions/procedures of a package, and these actions are restricted to two types:

• DML instructions in the database : “Delete”, “Insert”, ”Merge”, “Select”, “Update” , which corresponding the purity levels “WNDS” and “RNDS”.
• Access to another’s package functions or global variables, which corresponding the purity levels “WNDS” and “RNDS”.

Below is presented a complete description of the purity levels:

1. WNDS  –  Writes No Database State i.e. Function does not modify any database tables (No DML)
2. RNDS   –  Reads No Database State  i.e. Function does not read any tables (No select)
3. WNPS  –  Writes No Package State   i.e. Function does not modify any packaged variables (packaged variables are variables declared in a package specification)
4. RNPS   –  Reads No Package State   i.e. Function does not read any packaged variables
5. TRUST – When this purity level is specified all the others mentioned are not considered.

WNDS and RNDS

Let us consider the following package :

CREATE OR REPLACE PACKAGE WEB_IP_HOST AS
 PROCEDURE set_Ip_Address(p_ip_address VARCHAR2 DEFAULT NULL);
 FUNCTION  getIpClass(p_ip_address VARCHAR2)RETURN VARCHAR2;

 PRAGMA RESTRICT_REFERENCES(set_Ip_Address, WNDS, RNDS);
 PRAGMA RESTRICT_REFERENCES(getIpClass, WNDS, RNDS);
END WEB_IP_HOST;

In this example is not allowed to the procedure ” set_Ip_Address” and “getIpClass” function to execute any DML statement.

DEFAULT
Of other part the previous package definition is similar to the following:

CREATE OR REPLACE PACKAGE WEB_IP_HOST  AS
 PROCEDURE set_Ip_Address(p_ip_address VARCHAR2 DEFAULT NULL);
 FUNCTION  getIpClass(p_ip_address VARCHAR2)RETURN VARCHAR2;

 PRAGMA RESTRICT_REFERENCES(DEFAULT, WNDS, RNDS);
END WEB_IP_HOST;

With the “DEFAULT” option instead of the function or procedure name the purity levels specified are applied to all procedures/functions of the package.

Package global variables and their behavior

The purpose of this post it is analyzing the behavior of package global variables and the possibility that currently exists to change their behavior.

For now let’s consider the following package:

CREATE OR REPLACE PACKAGE WEB_IP_HOST IS

-- Global variables Section ---

gs_ip_address VARCHAR2(32) :='127.0.0.1';

gs_hostname   VARCHAR2(32) :='atlantic';

PROCEDURE set_Ip_Address(p_ip_address VARCHAR2 DEFAULT NULL);

PROCEDURE get_Ip_Address;

END WEB_IP_HOST;

CREATE OR REPLACE PACKAGE BODY WEB_IP_HOST IS

PROCEDURE set_Ip_Address(p_ip_address VARCHAR2 DEFAULT NULL) IS

BEGIN

gs_ip_address := p_ip_address;

END set_Ip_Address;

PROCEDURE get_Ip_Address IS

BEGIN

dbms_output.put_line('IP Address ='||gs_ip_address);

END get_Ip_Address;

BEGIN

-- Initialization Section ---

INSERT INTO IP_HOST_LOG(LOG_ID,LOG_DATE,IP_ADDRESS,HOSTNAME)

VALUES(IP_HOST_SEQ.NEXTVAL,SYSDATE,gs_ip_address,gs_hostname);

COMMIT;

END WEB_IP_HOST;

These variables : ”gs_ip_address” and “gs_hostname” are global and their access can be outside of this package because they are defined in the package  specification, otherwise as whether it were defined in the package body they still global but only to be accessed inside this package.