LEHS - Log- and Exception Handling System for ORACLE

Overview

Deprecated

Index

Help

LEHS - Log- and Exception Handling System for ORACLE

FRAMES NO FRAMES

SUMMARY: FIELD | TYPE | METHOD

DETAIL: FIELD | TYPE | METHOD

Package pkg_lehs_system

This Package is for the maintanance of the Log-System

General

All logs will be taken for a Log-Application. This is the Top-Level Object of all loggings.
There exists 1 System Log-Application:

LEHS
Every Action taken by the Log-System itself will be logged to this Log-Application.

By defining a Log-Application you can manage the following Parameters of this Log-Application

Exception Log-Level
In what Log-Level will Exceptions be logged. If this will be omitted then Exceptions will be logged with the least available Log-Level of the Log-Applicaiton.
Logging DB-Users
Which DB-User is allowed to log to this Log-Application and get the Logging-Data by the Logging-View
Log-Appenders
Where should the Logs be written and the maximum Log-Level for each Log-Appender.
Log-Appender Parameters
Optional Parameters for the Log-Appenders (except the maximum Log-Level). For Instance a minimum Log-Level, Retention Time, etc.
Log-Levels
Which Log-Levels will the Log-Application provide to the Application Developers
Programmable Log-Levels
Which Log-Levels are assigned to the fixed programmable Log-Levels (FATAL,ERROR,WARN ,INFO,DEBUG,TRACE)
Log Topics
Which Log-Topics will the Log-Application provide to the Application Developers
Log Exception
Which Exception Messages will the Log-Application provide to the Application Developers

Log Application

As described at the beginning of this Section this is the Top-Object of the Log-System. Every Parameters will refer to a Log-Application. A Log-Application will always be uppercased automatically at the Creation Time. Also the maximum Length of a Log Application are limited to 30 Characters.

There's just one Parameter you can set directly on a Log-Application

Exception Log-Level
To which Log-Level should Exceptions be logged? Be aware of setting this Log-Level higher than the maximum Log-Level of the Appenders because Errors would then never be logged.
If this Parameter will be omitted (by setting it explicitly to NULL) then the least available Log-Level of the Log-Application will be taken to log Exceptions.

Logging DB-Users

Following the principle of the least Privilege (as recommended by Oracle) NO User can access a newly created Application.
By granting a Privilege to a User you can enable this User to access the Log-Application either to read only the Log-Entries made by the Appender PKG_LEHS_TABLE_APPENDER (PKG_LEHS_SYSTEM.grant_privs (pi_read_only => TRUE), this is the Default when granting a Privilege) or the User can also write Log-Messages to this Log-Application (PKG_LEHS_SYSTEM.grant_privs (pi_read_only => FALSE))

When you grant the Privilege to a Role, every User which has been granted this Role is able to use the Log-Application in the Way described above.
When you grant the Privilege to the User PUBLIC then all Users of the Database are able to use the Log-Application in the Way described above.

By Default, Users with the Role DBA or LEHS_ADMIN are able to see all Information of any Log-Application.

Grant Dependencies

The're exists a Role named A_ROLE, the User A_USER has been granted this Role. The Log-Application A_LOG_APPLICATION has Privileges for the User PUBLIC (read only), the Role A_ROLE (read/write) and the User A_USER (read only). If the User has at least one read/write Privilege on one of these Grants in the Chain, the User has the Ability to write to this Log-Application (also when the User itself has only read only Privileges to this Application because the Role A_ROLE, where the User has been granted to, has read/write Permissions).

Log Appenders

Without specifying Log-Appender, where Log-Messages will be written, no Log-Message will ever be visible to the Users. This is the Task for the Log-Appenders.

A Log-Appender is a Package with at least a Procedure called log with only one Parameter of Datatype PKG_LEHS_APPENDER.pr_log_line. This Procedure will be called by LEHS for every Log-Entry and every registered Log-Appender to the Log-Application.

The're existing 5 pre-programmed Log-Appenders you can register to every Log-Application

Package Destination

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

Package	Destination
`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

Log Appender Parameters

Various Log-Appenders need various Parameters. The Name of the Parameter is case sensitive as the Value the Value of the Parameter. Here is the List for the 5 pre-programmed Log-Appenders:

Package Name Parameter Datatype possible Values Description

PKG_LEHS_ALERT_LOG_APPENDER LOG_FORMAT CLOB Format String (look at PKG_LEHS_APPENDER.format_log_message)

PKG_LEHS_APP_INFO_APPENDER MIN_LOG_LEVEL CLOB all available Log-Levels of the Log-Application the minimum Log-Level of the Log-Message. If a Log-Message has a Log-Level below this minimum Log-Level the Log-Message will not be logged by this Appender

ACTION

CLOB

`APPLICATION`		The Name of the Log-Application
`TOPIC`		the Topic of the Log-Message
`TRANSACTION`		the Log-Transaction of the Log-Message
`CALLER`		the Caller of the Log-Message (Format: Owner.Method[Line#])
`MESSAGE`		the Log-Message itself

prints the given Part of the Log-Message (see possible Values) to the Field V$SESSION.action (truncated to 32 Characters)

MODULE

CLOB

`APPLICATION`		The Name of the Log-Application
`TOPIC`		the Topic of the Log-Message
`TRANSACTION`		the Log-Transaction of the Log-Message
`CALLER`		the Caller of the Log-Message (Format: Owner.Method[Line#])
`MESSAGE`		the Log-Message itself

prints the given Part of the Log-Message (see possible Values) to the Field V$SESSION.module (truncated to 48 Characters)

CLIENT_INFO

CLOB

`APPLICATION`		The Name of the Log-Application
`TOPIC`		the Topic of the Log-Message
`TRANSACTION`		the Log-Transaction of the Log-Message
`CALLER`		the Caller of the Log-Message (Format: Owner.Method[Line#])
`MESSAGE`		the Log-Message itself

prints the given Part of the Log-Message (see possible Values) to the Field V$SESSION.client_info (truncated to 64 Characters)

PKG_LEHS_DBMS_OUTPUT_APPENDER LOG_FORMAT CLOB Format String (look at PKG_LEHS_APPENDER.format_log_message)

PKG_LEHS_TABLE_APPENDER RETENTION_TIME NUMBER 0 - 99999 Retention Time (in Minutes) for the Log-Entries

REMOVE_UNUSED_TRANSACTIONS

CLOB

`TRUE`		Remove unused Transactions
`FALSE`		Don't remove unused Transactions (Default)

Should unused Transactions be removed (regardless of the Retention Time) (Default: FALSE).

REMOVE_LOGS_IN_OPEN_TRX

CLOB

`TRUE`		Remove Logs whose Transaction is already opened
`FALSE`		Don't remove Logs whose Transaction is open (Default)

Should Log-Entries with open Transactions be removed by the Cleanup-Procedure (Default: FALSE)

PKG_LEHS_TRACE_FILE_APPENDER LOG_FORMAT CLOB Format String (look at PKG_LEHS_APPENDER.format_log_message)

Log Levels

When requesting a Log-Entry by a PL/SQL-Application, this PL/SQL-Application must tell, to which Log-Level this Log-Entry should be logged.
These Log-Levels can be defined freely to every Log-Application. You can decide if the Log-Application has Log-Levels from '01' to '99' (remember: Log-Levels are always alphanumeric, therefor the Log-Levels are quoted) or if you define Log-Levels like 'John', 'Rambo', 'Sylvester' and 'Stallone'. The fact is, the Developer should have a Knowledge of the Log-Levels defined to this Log-Application because he/she must provide this Log-Level by every Log-Entry he/she makes within his/her PL/SQL-Application.

The following Log-Levels are a good starting Point

FATAL
ERROR
WARN
INFO
DEBUG
TRACE

Programmable Log-Levels

Because to know, which Log-Levels an Log-Application has is not really useful for a 08/15 Developer. Therefor there are 6 Log-Levels which can be used programatically much easier than knowing the real Log-Levels of an Application.
There existing 6 programmable Log-Levels with the programmable Interfaces.

	programmable Log-Level	Interface
1.	`FATAL`	`PKG_LEHS.fatal`
2.	`ERROR`	`PKG_LEHS.error`
3.	`WARN`	`PKG_LEHS.warn`
4.	`INFO`	`PKG_LEHS.info`
5.	`DEBUG`	`PKG_LEHS.debug`
6.	`TRACE`	`PKG_LEHS.trace`

Within an PL/SQL-Application it is much easier to just call: PKG_LEHS.info ('This is an Information Line'); than calling PKG_LEHS.log (pc_info_log_level, 'This is an Information Line').
On the other Hand the Log-Administrator has the ability to change the programmable Log-Levels dynamically to any existing Log-Levels of the Log-Application.

When you use the standard Log-Levels as described above then the Assignment amounts to a 1:1 Mapping.

Log Topics

Just imagine the following Situation: You must write a Documentation about this Log-System. As you can see, when you write the Documentation without any formatting Rules you can do nothing with this Documentation.
One of these formatting Rules is to put some Descriptions within Topics. You can do this also with Log-Entries.

For these Purpose the Log-Topics are stored within a hierarchical Structure with a discrete horizontal Sorting. Just like the following Structure:

 Chapter 1
   Chapter 1.1
     Chapter 1.1.1
     Chapter 1.1.2
   Chapter 1.2
     Chapter 1.2.1
 Chapter 2
   Chapter 2.1
   Chapter 2.2
     Chapter 2.2.1
     Chapter 2.2.2
     Chapter 2.2.3
   Chapter 2.3
 Chapter 3

Just for the Log-Adminstrator: Slow down. The PL/SQL-Application justifies its own Topics with the Log-Application. In normal Case the Log-Adminstrator has not to intervene manually.

Log Transactions

Log-Transactions can be used to separate the Calls of an Application.
Image the following Situation: Procedure A has been started by User X. This call takes about 10 Minutes. 5 Minutes after User X has started User Y also starts the Procedure A. Therefor 2 Instances of the Procedure A runs and they both produces a lot of Log-Message into USER_LEHS_LOGS.
Now the Question: How can you find out, what the first Call of Procedure A has been written and what the second? The Answer: Every Instance of the called Procedure generates a Log-Transaction, which will be constant during the Call. Just before the Procedure ends this Transaction will be closed by the called Procedure. Also, when the Procedure ends due to an Exception.

Subroutines can also create their own Log-Transaction. But when the Scope return to the calling Procedure, you have to join to the old Log-Transaction. Otherwise the following Log-Lines will be logged to the Log-Transaction of the called Subroutine.

By using a Log-Transaction there is just another Advantage. You can set Keywords to a Log-Transactions, which can be queried by the View USER_LEHS_TRANSACTIONS. Why do this? Let me show by a simple Example:
Same Procedure A as above. But this Procedure has 1 Parameter pi_something. User X calls this Procedure with "X" as the Value of the Parameter pi_something. User Y calls this Procedure with "Y" as the Value of the Parameter pi_something.
The Question: When was the last Call of Procedure A with "X" as the Value of Parameter pi_something. This can simple be done if you set the Parameter pi_something as a Keyword to the Log-Transaction.

Attention: The Value of the Keys will be translated during the Query of USER_LEHS_TRANSACTIONS. Means, if you add a Date Key, the Date-Value will be stored and will be automatically converted during the Query of the View USER_LEHS_TRANSACTIONS by with the help of the Session-Parameters NLS_NUMERIC_CHARACTERS, NLS_DATE_FORMAT and NLS_TIMESTAMP_FORMAT.

Exception Messages

Just to destroy an Imagination: The're doesn't exist any Application which works without raising any Exception. For this case Oracle provides User-defined Exceptions with Error Numbers from ORA-20999 to ORA-20000 (means that a PL/SQL-Application can raise 1000 different Exceptions).

This Methodology has some Drawbacks:

Internationalization of Exception Messages
An PL/SQL-Application does normally not care about internationlized Exception Messages. The PL/SQL-Application raises an Exception with a Message defined by the coding guidelines of the Development Departement.
Confusion at Debug-Time
When a PL/SQL-Application uses only one Error-Code (i.E. ORA-20000) for all business log Errors then it would rather be complex when there was an Error and you don't know exactly, where this Exception has been thrown.
No Reuse of Exception Messages
When checking any Parameters against a NULL Value this code looks like this:
```
   ...
   IF pi_param1 IS NULL THEN
     RAISE_APPLICATION_ERROR (-20001, 'Parameter PI_PARAM1 is NULL');
   END IF;
   IF pi_param2 IS NULL THEN
     RAISE_APPLICATION_ERROR (-20001, 'Hero: Parameter PI_PARAM2 is null');
   END IF;
   ...
 
```
By the Reuse of an Exception Message the Exception Message will be enriched by Placeholder Values (max. 9 from %1 to %9). Within these Placeholder Values the Application can set the correct Value during the Runtime. The Code will be the following
```
   ...
   IF pi_param1 IS NULL THEN
     PKG_LEHS.handle_exception ('MISSING PARAMETER', PKG_LEHS.pt_error_arguments('pi_param1'));
   END IF;
   IF pi_param2 IS NULL THEN
     PKG_LEHS.handle_exception ('MISSING PARAMETER', PKG_LEHS.pt_error_arguments('pi_param2'));
   END IF;
   ...
 
```
The Exception Message will always have the same Structure but the first substitution Value (%1) will be substituted by the first Parameter of the Parameter List (i.E. pi_param1 or pi_param2)
doubled Logging of the Exception Messages
Unlike the unnamed Exceptions named Exceptions are logged only one time in the Block where the named Exception has been raised.
Because a named Exception describes a valid Exception Situation (i.e. a NO_DATA_FOUND Exception has been catched and instead a named Exception will be raised) an Error-Stack Trace in the Log is not neccessary. Indeed when a named Exception will be covered like an unnamed Exception the Log is much complexer to be read.

The Methodology of named Exceptions benefits all of these Drawbacks by defining the Exception Messages within a Table. Every Message is identified by a unique Error Name (instead of meaningless Error Codes) and the NLS-Language of this Error Name (when omitted then AMERICAN is the Default).
Every Exception Message will be defined by the PL/SQL-Application itself and would be synchronized with the Log-System. (similar to the Log-Topics).

Every Exception Message can be parametrized (max. 9 Parameters). These Placeholders are numerically from %1 to %9. At Runtime these Placeholders will be replaced by real Values the PL/SQL-Application sets.

That Oracle can raise the Exception it is neccessary to have an Error Code from ORA-20999 to ORA-20000 that will also be delivered at registering the Exception.

Field Summary
`BINARY_INTEGER`	`pc_default_trx_retention_time` Default Retention Time for a Log Application: 0 (infinite Retention Time)
`CHAR(1)`	`pc_false` global Constant for a decoded Boolean FALSE
`LEHS_LOG_LEVELS_PROGRAMMABLE.programmable_log_level%TYPE`	`pc_log_level_debug` programmable Log-Level: `DEBUG`
`LEHS_LOG_LEVELS_PROGRAMMABLE.programmable_log_level%TYPE`	`pc_log_level_error` programmable Log-Level: `ERROR`
`LEHS_LOG_LEVELS_PROGRAMMABLE.programmable_log_level%TYPE`	`pc_log_level_fatal` programmable Log-Level: `FATAL`
`LEHS_LOG_LEVELS_PROGRAMMABLE.programmable_log_level%TYPE`	`pc_log_level_info` programmable Log-Level: `INFO`
`LEHS_LOG_LEVELS_PROGRAMMABLE.programmable_log_level%TYPE`	`pc_log_level_trace` programmable Log-Level: `TRACE`
`LEHS_LOG_LEVELS_PROGRAMMABLE.programmable_log_level%TYPE`	`pc_log_level_warn` programmable Log-Level: `WARN`
`VARCHAR2(30)`	`pc_owner` Owner of the Log-System
`VARCHAR2(30)`	`pc_packname` Name of the Package
`PKG_LEHS.pst_log_topic`	`pc_topic_exception` Topic for Log Exceptions
`PKG_LEHS.pst_log_topic`	`pc_topic_log_appender` Topic for Log Appender
`PKG_LEHS.pst_log_topic`	`pc_topic_log_topic` Topic for Log Topics
`CHAR(1)`	`pc_true` global Constant for a decoded Boolean TRUE
`VARCHAR2(30)`	`pc_version` Version of LEHS

Type Summary
`TABLE OF CLOB INDEX BY null`	`pt_clob()` List of CLOB

Method Summary
	`activate_application(pi_application PKG_LEHS.pst_application_name)` Activates a Log-Application for Logging.
	`add_application(pi_application PKG_LEHS.pst_application_name, pi_show_in_exception BOOLEAN DEFAULT TRUE)` Create a new Log-Application
	`add_log_level(pi_application PKG_LEHS.pst_application_name, pi_log_level VARCHAR2, pi_log_level_predecessor VARCHAR2)` Adds a new Log-Level to the Application
	`add_log_level(pi_application PKG_LEHS.pst_application_name, pi_log_level VARCHAR2, pi_position BINARY_INTEGER DEFAULT NULL)` Adds a new Log-Level to the Application
	`add_message(pi_application PKG_LEHS.pst_application_name, pi_error_name PKG_LEHS.pst_error_name, pi_error_code BINARY_INTEGER, pi_message CLOB, pi_show_error_stack BOOLEAN DEFAULT TRUE, pi_nls_language VARCHAR2 DEFAULT PKG_LEHS.pc_default_language, pi_log_exception BOOLEAN DEFAULT TRUE)` Add a new Exception Message.
	`add_topic(pi_application PKG_LEHS.pst_application_name, pi_topic VARCHAR2, pi_topic_parent VARCHAR2, pi_topic_predecessor VARCHAR2, pi_use_parent_of_pred BOOLEAN DEFAULT FALSE, pi_log_exception BOOLEAN DEFAULT TRUE)` Add a new Topic
	`add_topic(pi_application PKG_LEHS.pst_application_name, pi_topic VARCHAR2, pi_topic_parent VARCHAR2, pi_position BINARY_INTEGER DEFAULT NULL, pi_log_exception BOOLEAN DEFAULT TRUE)` Add a new Topic
	`change_application(pi_application PKG_LEHS.pst_application_name, pi_show_in_exception BOOLEAN, pi_exception_log_level VARCHAR2)` Changes an existing Log-Application.
	`change_max_log_level(pi_application PKG_LEHS.pst_application_name, pi_appender_name VARCHAR2, pi_max_log_level VARCHAR2)` Change the maximum Log-Level of an Log-Appender
	`change_message(pi_application PKG_LEHS.pst_application_name, pi_error_name PKG_LEHS.pst_error_name, pi_error_code BINARY_INTEGER, pi_message CLOB, pi_show_error_stack BOOLEAN DEFAULT TRUE, pi_nls_language VARCHAR2 DEFAULT PKG_LEHS.pc_default_language)` Changes the Exception Message to an Error Name.
	`deactivate_application(pi_application PKG_LEHS.pst_application_name)` Deactivates a Log-Application for Logging.
`BOOLEAN`	`decode_boolean(pi_encoded_boolean CHAR)` Translates a Character to a Boolean
	`deregister_appender(pi_application PKG_LEHS.pst_application_name, pi_appender_name VARCHAR2)` Deregister an Appender from a Log-Application
`CHAR`	`encode_boolean(pi_decoded_boolean BOOLEAN)` Translates a Boolean to a Character (which can be stored within a Table).
`BOOLEAN`	`exists_appender(pi_application PKG_LEHS.pst_application_name, pi_appender_name VARCHAR2)` Does there exist the Appender to the Log-Application?
`BOOLEAN`	`exists_application(pi_application PKG_LEHS.pst_application_name)` Checks for the Existance of a Log-Application
`BOOLEAN`	`exists_log_level(pi_application PKG_LEHS.pst_application_name, pi_log_level VARCHAR2)` Checks for the Existance of a Log-Level to an existing Log-Application
`BOOLEAN`	`exists_message(pi_application PKG_LEHS.pst_application_name, pi_error_name PKG_LEHS.pst_error_name, pi_nls_language VARCHAR2 DEFAULT PKG_LEHS.pc_default_language)` Does there exist an Exception Message to the given Error Name?
`BOOLEAN`	`exists_topic(pi_application PKG_LEHS.pst_application_name, pi_topic VARCHAR2)` Does the given Topic exist within the given Application
`pt_clob`	`get_metadata(pi_application PKG_LEHS.pst_application_name, pi_topics VARCHAR2 DEFAULT pc_false, pi_exceptions VARCHAR2 DEFAULT pc_false)` Get the Metadata of the given Log-Application.
`VARCHAR2`	`get_version()` Returns the Version of LEHS
	`grant_privs(pi_application PKG_LEHS.pst_application_name, pi_grantee VARCHAR2, pi_read_only BOOLEAN DEFAULT TRUE)` Grants the Privilege to use the Log-Application to a DB-User or DB-Role.
	`move_log_level(pi_application PKG_LEHS.pst_application_name, pi_log_level VARCHAR2, pi_log_level_predecessor VARCHAR2)` Moves the Position of a Log-Level.
	`move_log_level(pi_application PKG_LEHS.pst_application_name, pi_log_level VARCHAR2, pi_position BINARY_INTEGER DEFAULT NULL)` Moves the Position of a Log-Level.
	`move_topic(pi_application PKG_LEHS.pst_application_name, pi_topic VARCHAR2, pi_topic_parent VARCHAR2, pi_topic_predecessor VARCHAR2, pi_use_parent_of_pred BOOLEAN DEFAULT FALSE)` Move an existing Topic to another Position
	`move_topic(pi_application PKG_LEHS.pst_application_name, pi_topic VARCHAR2, pi_topic_parent VARCHAR2, pi_position BINARY_INTEGER DEFAULT NULL)` Move an existing Topic to another Position
	`register_appender(pi_application PKG_LEHS.pst_application_name, pi_appender_name VARCHAR2, pi_max_log_level VARCHAR2, pi_package_owner VARCHAR2, pi_package_name VARCHAR2)` Register an Appender to a Log-Application.
	`remove_application(pi_application PKG_LEHS.pst_application_name)` Removes a Log-Application.
	`remove_log_level(pi_application PKG_LEHS.pst_application_name, pi_log_level VARCHAR2)` Removes a Log-Level from an Application
	`remove_message(pi_application PKG_LEHS.pst_application_name, pi_error_name PKG_LEHS.pst_error_name, pi_nls_language VARCHAR2 DEFAULT PKG_LEHS.pc_default_language)` Removes an Exception Message by an Error Name
	`remove_message(pi_application PKG_LEHS.pst_application_name, pi_error_code BINARY_INTEGER, pi_nls_language VARCHAR2 DEFAULT PKG_LEHS.pc_default_language)` Removes an Exception Message by an Error Code
	`remove_topic(pi_application PKG_LEHS.pst_application_name, pi_topic VARCHAR2)` Remove an existing Topic from a Log-Application
	`revoke_privs(pi_application PKG_LEHS.pst_application_name, pi_grantee VARCHAR2)` Revokes the Privileges to use the Log-Application from a DB-User or DB-Role
	`set_appender_parameter(pi_application PKG_LEHS.pst_application_name, pi_appender_name VARCHAR2, pi_parameter_name VARCHAR2, pi_parameter_value VARCHAR2)` Sets an Appender Parameter.
	`set_appender_parameter(pi_application PKG_LEHS.pst_application_name, pi_appender_name VARCHAR2, pi_parameter_name VARCHAR2, pi_parameter_value CLOB)` Sets an Appender Parameter.
	`set_appender_parameter(pi_application PKG_LEHS.pst_application_name, pi_appender_name VARCHAR2, pi_parameter_name VARCHAR2, pi_parameter_value NUMBER)` Sets an Appender Parameter.
	`set_appender_parameter(pi_application PKG_LEHS.pst_application_name, pi_appender_name VARCHAR2, pi_parameter_name VARCHAR2, pi_parameter_value DATE)` Sets an Appender Parameter.
	`set_appender_parameter(pi_application PKG_LEHS.pst_application_name, pi_appender_name VARCHAR2, pi_parameter_name VARCHAR2, pi_parameter_value TIMESTAMP_UNCONSTRAINED)` Sets an Appender Parameter.
	`set_appender_parameter(pi_application PKG_LEHS.pst_application_name, pi_appender_name VARCHAR2, pi_parameter_name VARCHAR2, pi_parameter_value DSINTERVAL_UNCONSTRAINED)` Sets an Appender Parameter.
	`set_appender_parameter(pi_application PKG_LEHS.pst_application_name, pi_appender_name VARCHAR2, pi_parameter_name VARCHAR2, pi_parameter_value YMINTERVAL_UNCONSTRAINED)` Sets an Appender Parameter.
	`set_log_level_debug(pi_application PKG_LEHS.pst_application_name, pi_log_level VARCHAR2)` Sets the programmable Log-Level DEBUG
	`set_log_level_error(pi_application PKG_LEHS.pst_application_name, pi_log_level VARCHAR2)` Sets the programmable Log-Level ERROR
	`set_log_level_fatal(pi_application PKG_LEHS.pst_application_name, pi_log_level VARCHAR2)` Sets the programmable Log-Level FATAL
	`set_log_level_info(pi_application PKG_LEHS.pst_application_name, pi_log_level VARCHAR2)` Sets the programmable Log-Level INFO
	`set_log_level_trace(pi_application PKG_LEHS.pst_application_name, pi_log_level VARCHAR2)` Sets the programmable Log-Level TRACE
	`set_log_level_warn(pi_application PKG_LEHS.pst_application_name, pi_log_level VARCHAR2)` Sets the programmable Log-Level WARN
	`unset_appender_parameter(pi_application PKG_LEHS.pst_application_name, pi_appender_name VARCHAR2, pi_parameter_name VARCHAR2)` Remove an Appender Parameter.
	`unset_log_level_debug(pi_application PKG_LEHS.pst_application_name)` Removes the programmable Log-Level DEBUG
	`unset_log_level_error(pi_application PKG_LEHS.pst_application_name)` Removes the programmable Log-Level ERROR
	`unset_log_level_fatal(pi_application PKG_LEHS.pst_application_name)` Removes the programmable Log-Level FATAL
	`unset_log_level_info(pi_application PKG_LEHS.pst_application_name)` Removes the programmable Log-Level INFO
	`unset_log_level_trace(pi_application PKG_LEHS.pst_application_name)` Removes the programmable Log-Level TRACE
	`unset_log_level_warn(pi_application PKG_LEHS.pst_application_name)` Removes the programmable Log-Level WARN

Field Detail

pc_owner

  public VARCHAR2(30) pc_owner

Owner of the Log-System

pc_version

  public VARCHAR2(30) pc_version

Version of LEHS

pc_topic_log_appender

  public PKG_LEHS.pst_log_topic pc_topic_log_appender

Topic for Log Appender

pc_topic_log_topic

  public PKG_LEHS.pst_log_topic pc_topic_log_topic

Topic for Log Topics

pc_topic_exception

  public PKG_LEHS.pst_log_topic pc_topic_exception

Topic for Log Exceptions

pc_packname

  public VARCHAR2(30) pc_packname

Name of the Package

pc_true

  public CHAR(1) pc_true

global Constant for a decoded Boolean TRUE

pc_false

  public CHAR(1) pc_false

global Constant for a decoded Boolean FALSE

pc_log_level_fatal

  public LEHS_LOG_LEVELS_PROGRAMMABLE.programmable_log_level%TYPE pc_log_level_fatal

programmable Log-Level: FATAL

pc_log_level_error

  public LEHS_LOG_LEVELS_PROGRAMMABLE.programmable_log_level%TYPE pc_log_level_error

programmable Log-Level: ERROR

pc_log_level_warn

  public LEHS_LOG_LEVELS_PROGRAMMABLE.programmable_log_level%TYPE pc_log_level_warn

programmable Log-Level: WARN

pc_log_level_info

  public LEHS_LOG_LEVELS_PROGRAMMABLE.programmable_log_level%TYPE pc_log_level_info

programmable Log-Level: INFO

pc_log_level_debug

  public LEHS_LOG_LEVELS_PROGRAMMABLE.programmable_log_level%TYPE pc_log_level_debug

programmable Log-Level: DEBUG

pc_log_level_trace

  public LEHS_LOG_LEVELS_PROGRAMMABLE.programmable_log_level%TYPE pc_log_level_trace

programmable Log-Level: TRACE

pc_default_trx_retention_time

  public BINARY_INTEGER pc_default_trx_retention_time

Default Retention Time for a Log Application: 0 (infinite Retention Time)

Type Detail

pt_clob

  public TABLE OF CLOB INDEX BY null pt_clob()

List of CLOB

Method Detail

encode_boolean

  public CHAR encode_boolean(pi_decoded_boolean BOOLEAN)

Translates a Boolean to a Character (which can be stored within a Table).

Parameters:

pi_decoded_boolean - Boolean Value

Returns:

TRUE will be Y
FALSE will be N
NULL will be translated to NULL

decode_boolean

  public BOOLEAN decode_boolean(pi_encoded_boolean CHAR)

Translates a Character to a Boolean

Returns:

Y will be translated to TRUE
N will be translated to FALSE
NULL will be translated to NULL
all other Characters will be translated to FALSE

add_application

  public  add_application(pi_application PKG_LEHS.pst_application_name, 
                          pi_show_in_exception BOOLEAN DEFAULT TRUE)

Create a new Log-Application

Parameters:: pi_application - Name of the new Application; pi_show_in_exception - Should the Name of the Application be shown in the Exception Message
Throws:: ORA-20001 - [MISSING PARAMETER] missing mandatory Parameter; ORA-20011 - [APPLICATION ALREADY EXISTS] Application already exists

exists_application

  public BOOLEAN exists_application(pi_application PKG_LEHS.pst_application_name)

Checks for the Existance of a Log-Application

Parameters:

pi_application - Application Name to be checked

Returns:

TRUE ... Application exists
FALSE ... Application doesn't exist

change_application

  public  change_application(pi_application PKG_LEHS.pst_application_name, 
                             pi_show_in_exception BOOLEAN, 
                             pi_exception_log_level VARCHAR2)

Changes an existing Log-Application.

Parameters:: pi_application - Name of the existing Application; pi_show_in_exception - Should the Name of the Application be shown in the Exception Message; pi_exception_log_level - Log-Level of the thrown and logged Exceptions
Throws:: ORA-20001 - [MISSING PARAMETER] missing mandatory Parameter; ORA-20002 - [APPLICATION NOT EXISTS] Application doesn't exist; ORA-20012 - [LOG LEVEL NOT EXISTS] Log-Level doesn't exist

deactivate_application

  public  deactivate_application(pi_application PKG_LEHS.pst_application_name)

Deactivates a Log-Application for Logging.

Parameters:: pi_application - Name of the existing Application
Throws:: ORA-20001 - [MISSING PARAMETER] missing mandatory Parameter; ORA-20002 - [APPLICATION NOT EXISTS] Application doesn't exist

activate_application

  public  activate_application(pi_application PKG_LEHS.pst_application_name)

Activates a Log-Application for Logging.

Parameters:: pi_application - Name of the existing Application
Throws:: ORA-20001 - [MISSING PARAMETER] missing mandatory Parameter; ORA-20002 - [APPLICATION NOT EXISTS] Application doesn't exist

remove_application

  public  remove_application(pi_application PKG_LEHS.pst_application_name)

Removes a Log-Application.

Be careful: By removing the Log-Application all Information (except the written Log-Entries to this Application by the Table Appender Package PKG_LEHS_TABLE_APPENDER) will be removed!

Parameters:: pi_application - Log-Application to be removed
Throws:: ORA-20001 - [MISSING PARAMETER] missing mandatory Parameter; ORA-20002 - [APPLICATION NOT EXISTS] Application doesn't exist

register_appender

  public  register_appender(pi_application PKG_LEHS.pst_application_name, 
                            pi_appender_name VARCHAR2, 
                            pi_max_log_level VARCHAR2, 
                            pi_package_owner VARCHAR2, 
                            pi_package_name VARCHAR2)

By registering an Appender-Package every Log-Entry with a Log-Level below or equal to the maximum Log-Level of the Appender will be logged by the Logger-Procedure of this Appender.

Parameters:: pi_application - Log-Application the Appender should be registered to; pi_appender_name - Unique Name of the Appender (unique to each Log-Application); pi_max_log_level - maximum Log-Level of the Log-Entries for this Appender; pi_package_owner - Owner of the Appender Package; pi_package_name - Name of the Appender Package
Throws:: ORA-20001 - [MISSING PARAMETER] missing mandatory Parameter; ORA-20002 - [APPLICATION NOT EXISTS] Application doesn't exist; ORA-20004 - [APPENDER REGISTER ERROR] Error while calling the Register Procedure of the Appender-Package; ORA-20010 - [NO LOGGER METHOD FOR APPENDER] the Appender-Package doesn't have a Logger-Procedure; ORA-20012 - [LOG LEVEL NOT EXISTS] the given maximum Log-Level doesn't exist; ORA-20013 - [INSUFFICIENT PRIVILEGES ON APPENDER PACKAGE] Insufficient Privileges on Appender-Package or Package doesn't exist; ORA-20015 - [APPENDER ALREADY EXISTS] The given Appender-Name already exists to this Log-Application; ORA-20015 - [APPENDER PACKAGE ALREADY REGISTERED] The Appender-Package has already been registered to this Log-Application

deregister_appender

  public  deregister_appender(pi_application PKG_LEHS.pst_application_name, 
                              pi_appender_name VARCHAR2)

Deregister an Appender from a Log-Application

Parameters:: pi_application - Log-Application the Appender should be deregistered from; pi_appender_name - Name of the Log-Appender which should be deregistered
Throws:: ORA-20001 - [MISSING PARAMETER] missing mandatory Parameter; ORA-20014 - [APPENDER NOT EXISTS] Appender doesn't exist

exists_appender

  public BOOLEAN exists_appender(pi_application PKG_LEHS.pst_application_name, 
                                 pi_appender_name VARCHAR2)

Does there exist the Appender to the Log-Application?

Parameters:

pi_application - Log-Application

pi_appender_name - Name of the Log-Appender

Returns:

TRUE ... Log-Appender exists
FALSE ... Log-Appender doesn&apot;t exist

change_max_log_level

  public  change_max_log_level(pi_application PKG_LEHS.pst_application_name, 
                               pi_appender_name VARCHAR2, 
                               pi_max_log_level VARCHAR2)

Change the maximum Log-Level of an Log-Appender

Parameters:: pi_application - Log-Application; pi_appender_name - Name of the Log-Appender; pi_max_log_level - maximum Log-Level of the Log-Entries for this Appender
Throws:: ORA-20001 - [MISSING PARAMETER] missing mandatory Parameter; ORA-20014 - [APPENDER NOT EXISTS] Appender doesn't exist; ORA-20012 - [LOG LEVEL NOT EXISTS] the given maximum Log-Level doesn't exist