LEHS - Log- and Exception-Handling System for ORACLE®

Who am I?

Concepts

		Tasks done by the LEHS Admins Create a new Log-Application / Manage existing Log-Applications Register Log-Appenders and set the appropriate Parameters Define Log-Levels and the programable Log-Levels Set the Privileges to the Log-Applications
		Tasks done by the Developers Log Messages by the Application Log Exceptions by the Application Define Exception Messages Define Topics Create Log-Transactions and Search-Keywords to these Log-Transactions

Log Application

This can be done by calling

	PKG_LOG_SYSTEM.add_application ('<Log Application>', TRUE)

There is an optional Attribute a Log-Application will get:

• Exception Log-Level
  All Exceptions, which will be handled by LEHS by calling PKG_LEHS.log_exception (without giving a Log-Level) will be logged with this Log-Level.

This Attribute (Exception Log-Level) can be changed by calling

	PKG_LOG_SYSTEM.change_application ('<Log Application>', TRUE, '<Exception Log-Level>')

Log Appender

LEHS provides you 5 predefined Appender Packages you can use with your Log-Application:

Appender Package	Description
PKG_LEHS_ALERT_LOG_APPENDER	Writes the Log-Message to the Alert-Log of the Database
PKG_LEHS_APP_INFO_APPENDER	Writes Parts of a Log-Message to the View V$SESSION by the Package DBMS_APPLICATION
PKG_LEHS_DBMS_OUTPUT_APPENDER	Writes the Log-Message to the Console by the Package DBMS_OUTPUT. Be aware of setting the Log-Buffer of this Package just before you start your Application (on SQL*Plus by calling SET SERVEROUTPUT ON SIZE UNLIMITED).
PKG_LEHS_TABLE_APPENDER	Writes the Log-Message to the Table LEHS_LOG_TABLE_BASIC which a User can query by the View LEHS_LOGS (in accordance to its Privileges of the Log-Application)
PKG_LEHS_TRACE_FILE_APPENDER	Writes the Log-Message to the Trace-File of the actual Session

In fact, these 5 Log Appenders relate to old Log Destinations of LEHS Version 1.3. With the new Open Appender Architecture (look at LEHS Appender Packages) you are able to write your own Appender Package and registers it as an Appender to your Log-Application.

Please refer to the Configuration Phase of the Log-Appenders to see, how to register and configure Appenders.

Appender Parameters

Log Format

• %a
  Name of the Log-Application
• %d[opt. Format]
  Log-Time with an optional Format. If the Format is omitted the Default-Format FMDD.MMFM.RRRR FMHH24FM:MI:SS:FF6 will be taken.
• %M
  producing Method
• %p
  used Log-Level
• %T
  Log-Topic
• %t
  Log-Transaction
• %m
  Log-Message

Log Levels

1. 6 alphanmeric Log-Levels (Recommendation by LOG4J)
   These 6 Log-Levels are (in Severity Order)
   1. FATAL
      All Errors, which causes the Application to be broken immediatly will use this Log-Level to be logged (if possible) (for Instance a faulted Memory Structure)
   2. ERROR
      All Errors, which causes the Application to be stopped will use this Log-Level (for Instance a missing mandatory Parameter)
   3. WARN
      All Errors, which causes the Application not to be stoppend immediatly will use this Log-Level (for Instance a Web-Service, which can't be obtained)
   4. INFO
      An Information by the Application (for Instance a basic Calculation Step)
   5. DEBUG
      An Information for the Developer for debugging the Application (for Instance the Value of a Variable)
   6. TRACE
      A Trace Information for the Developer for debugging the Application (for Instance the Position within a Calculation)
2. 100 numeric Log-Levels (Recommendation by Apache)
   The Log-Levels are numeric from 0 to 99 which describes the Severity (0 ... fatal Exception; 99 ... only a trace message)

1. positional Reference
   

   	PKG_LEHS_SYSTEM.add_log_level ('<Log Application>', '<Log Level>', <Position>)

2. predecessing Reference
   

   	PKG_LEHS_SYSTEM.add_log_level ('<Log Application>', '<Log Level>' '<predecessing Log Level>')
   	(the first Log-Level of a Hierarchy will be addressed by a NULL-Predecessor)

This Hierarchy takes effect when you define the maximum Log-Level of a Log-Appender. When you define a maximum Log-Level of ERROR, only Messages with the Log-Level FATAL and ERROR will be logged by this Log-Appender. All other Messages with a Log-Level higher than ERROR will be rejected.

On the other Hand, if you define a maximum Log-Level of DEBUG all Log Messages with the Log-Level FATAL, ERROR, WARN, INFO and DEBUG will be logged. The Messages with the Log-Level TRACE will be rejected.

Programmable Log Levels

For any of these 6 Log-Levels there exists a programmatic Interface which can be used by the Developers:

	programmable Log-Level Interface Setter Unsetter FATAL PKG_LEHS.fatal PKG_LEHS_SYSTEM.set_log_level_fatal PKG_LEHS_SYSTEM.unset_log_level_fatal ERROR PKG_LEHS.error PKG_LEHS_SYSTEM.set_log_level_error PKG_LEHS_SYSTEM.unset_log_level_error WARN PKG_LEHS.warn PKG_LEHS_SYSTEM.set_log_level_warn PKG_LEHS_SYSTEM.unset_log_level_warn INFO PKG_LEHS.info PKG_LEHS_SYSTEM.set_log_level_info PKG_LEHS_SYSTEM.unset_log_level_info DEBUG PKG_LEHS.debug PKG_LEHS_SYSTEM.set_log_level_debug PKG_LEHS_SYSTEM.unset_log_level_debug TRACE PKG_LEHS.trace PKG_LEHS_SYSTEM.set_log_level_trace PKG_LEHS_SYSTEM.unset_log_level_trace

This Method is much simplier to use than the general Implementation PKG_LEHS.log, where you have to set the Log-Level manually. Another advantage is the flexibilty. A Log-Administrator can reset the assigned Log-Level fluently.

Log Privileges

There exists at least 2 Security Privileges:

• read/write Permissions
  The privileged User/Role is able to create Log Messages and to read the created Log Messages and Log Transactions at the Log-Table (by the Views LEHS_LOGS and LEHS_TRANSACTIONS)
• read only Permissions
  The privileged User/Role is only able to read the created Log Messages and Log Transactions at the Log-Table# (by the Views LEHS_LOGS and LEHS_TRANSACTIONS)

CREATE USER a_user IDENTIFIED BY a_user;
CREATE ROLE a_role;
GRANT a_role TO a_user;

exec LEHS.PKG_LEHS_SYSTEM.grant_privs ('LOG_APPLICATION', 'A_USER', TRUE);
exec LEHS.PKG_LEHS_SYSTEM.grant_privs ('LOG_APPLICATION', 'A_ROLE', FALSE);
exec LEHS.PKG_LEHS_SYSTEM.grant_privs ('LOG_APPLICATION', 'PUBLIC', TRUE);

A Privilege can be granted by calling:

	PKG_LEHS_SYSTEM.grant_privs ('<Log Application>', '<User/Role>', <read/write (FALSE) or read-only (TRUE)>);

A granted Privilege can be revoked by calling:

	PKG_LEHS_SYSTEM.revoke_privs ('<Log Application>', '<User/Role>');

Get the Metadata Information

• Create the Log-Application
• Define the Log-Levels
• Set the programmable Log-Levels
• Set the Log-Appenders with all its Parameters
• Set the Privileges

• Add new or modify existing Exception Messages
• Add new or modify existing Log Topics

	SELECT * FROM TABLE(PKG_LEHS_SYSTEM.get_metadata('<Log Application>'[,'<include Log Topics (Y/N)>'[,'<include Exception Messages (Y/N)>']]));