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

Audience

This Usage Guide is for Developers who decided to enhance their Applications with LEHS.

The're just 3 Prerequisites that must be done before using LEHS by the Application:

  1. LEHS must be installed on the Database by a DBA (see Install LEHS).
  2. The Log-Application, which is intended to be used by the Application, must be configured by a LEHS Admin (see Configure LEHS)
  3. the Schema, where the Application runs (which will use LEHS), must be granted with read/write Permissions to this Log-Application (see Configure LEHS)

Write Logs

The're existing 2 Ways of writing Logs to the Log-Application:

How to interprete the Line Number

LEHS works within stored Procedures (Procedures, Functions and Packages) as well as within Triggers and anonymous PL/SQL Blocks. Because LEHS uses the Function DBMS_UTILITY.format_call_stack and DBMS_UTILITY.format_error_backtrace as well as DBMS_UTILITY.format_error_stack to identify the affected Line-Number of the log-raising Code you have to interpret this Line-Number correctly.

Log Exceptions

LEHS works with user named Exceptions. What the Hell are "user named Exceptions"?

Ok, you already know user defined Exceptions and user defined Error Messages: 

DECLARE
  e_not_null EXCEPTION; -- user defined Exception
  PRAGMA EXCEPTION_INIT (e_not_null, -1401);
BEGIN
  INSERT INTO some_table (
    not_null_column
  ) VALUES (
    NULL
  );
EXCEPTION
  WHEN e_not_null THEN
    -- user defined Error Message  
    RAISE_APPLICATION_ERROR (-20000, 'Some userdefined Exception', FALSE);
END;
For the user defined Error Messages you can take a long range of Exception Codes (from -20999 to -20000). 1000 Error Codes! But on most Applications I have seen only the Code -20000 has been taken for any Exception that has been raised by this Application. That's not good for any Error hunting Session.
On the other Hand if you have a really big Application you might come into affliction with only 1000 Error Codes (that what I had troubles with). Imagine you define an Exception for just even every kind of Error your Application can raise. Also an Exception for every mandatory Parameter like this code: 
CREATE OR REPLACE PROCEDURE test_it (
  pi_param1 IN VARCHAR2
 ,pi_param2 IN DATE
) IS
BEGIN
  IF pi_param1 IS NULL THEN
    RAISE_APPLICATION_ERROR (-20000, 'Mandatory Parameter pi_param1 IS NULL');
  END IF;

  IF pi_param2 IS NULL THEN
    RAISE_APPLICATION_ERROR (-20000, 'Mandatory Parameter pi_param2 IS NULL');
  END IF;

  ...
END test_it;
And now imagine you have to write your Exception Messages in several Languages (*mega grin*).

Now I tell you what user named Exceptions are. This is a recommendation by Stephen Feuerstein. I really apologize you to book one of his great Courses about PL/SQL Programming. He's just a Genius on this area.

Just define a Name for every Exception you want to raise and join it with any free Exception Code when you raise it. This is the short Version. Just see some Example: 

CREATE OR REPLACE PROCEDURE test_it (
  pi_param1 IN VARCHAR2
 ,pi_param2 IN DATE
) IS 
  c_param1_is_null CONSTANT VARCHAR2(30) := 'PARAM1_IS_NULL';
  c_param2_is_null CONSTANT VARCHAR2(30) := 'PARAM2_IS_NULL';

  PROCEDURE raise_user_exception (
    pi_error_name IN VARCHAR2
  ) IS
  BEGIN
    IF pi_error_name IN (c_param1_is_null, c_param2_is_null) THEN
      RAISE_APPLICATION_ERROR (-20000, 'Mandatory Parameter pi_param1 IS NULL');
    END IF;
  END raise_user_exception;  
BEGIN
  IF pi_param1 IS NULL THEN
    raise_user_exception (c_param1_is_null);  
  END IF;

  IF pi_param2 IS NULL THEN
    raise_user_exception (c_param2_is_null);  
  END IF;  
END test_it;
Ok, you would say "no Advantage for me as Developer. Far from it I have to code more lines". Yeah, but imagine, if you set this raising Procedure outside of your Program (make it global) then you can take this code for dozens of Procedures just to check the mandatory Parameters. So, this Procedure already exists within LEHS.

The next Point is the flexibility of such Exception Messages. If you can see above the Exception Message itself is for both always the same. Wouldn't it be fine if the Exception Message is for all these Checks (mandatory Parameter) is always the same except of the Name of the checked Parameter itself? Let's do it: 

CREATE OR REPLACE PROCEDURE test_it (
  pi_param1 IN VARCHAR2
 ,pi_param2 IN DATE
) IS 
  c_param1_is_null CONSTANT VARCHAR2(30) := 'PARAM1_IS_NULL';
  c_param2_is_null CONSTANT VARCHAR2(30) := 'PARAM2_IS_NULL';

  PROCEDURE raise_mandatory_check (
    pi_error_name IN VARCHAR2
   ,pi_parameter   IN VARCHAR2
  ) IS
    v_message VARCHAR2;  
  BEGIN
    v_message := 'Mandatory Parameter ' || pi_parameter || ' IS NULL';
	
    IF pi_error_name IN (c_param1_is_null, c_param2_is_null) THEN
      RAISE_APPLICATION_ERROR (-20000, v_message);
    END IF;
  END raise_user_exception;  
BEGIN
  IF pi_param1 IS NULL THEN
    raise_mandatory_check (c_param1_is_null, 'pi_param1');  
  END IF;

  IF pi_param2 IS NULL THEN
    raise_mandatory_check (c_param2_is_null, 'pi_param2');  
  END IF;  
END test_it;
We have defined a flexible user named Exception Message.

So, if we join these all Examples together the Code for LEHS looks like this: 

CREATE OR REPLACE PROCEDURE test_it (
  pi_param1 IN VARCHAR2
 ,pi_param2 IN DATE
) IS
  c_log_application CONSTANT PKG_LEHS.pst_application_name := 'LOG_APPLICATION';
  c_parameter_missing CONSTANT PKG_LEHS.pst_error_name := 'MISSING MANDATORY PARAMETER';
  
BEGIN
  -- Initialize the Error Message
  PKG_LEHS.add_message (
    pi_application => c_log_application
   ,pi_error_name  => c_parameter_missing
   ,pi_error_code  => -20000
   ,pi_message     => 'Mandatory Parameter %1 is NULL'
                      -- %1 will be replaced by the first Entry of the Error-Arguments List
  );

  PKG_LEHS.add_message (
    pi_application  => c_log_application
   ,pi_error_name   => c_parameter_missing
   ,pi_error_code   => -20000
   ,pi_message      => 'Zwingender Parameter %1 ist NULL'
   ,pi_nls_language => 'GERMAN'
  );

  PKG_LEHS.debug (c_log_application, 'Check for mandatory Parameters');
  
  -- Do the Checks (Assertions)
  PKG_LEHS.assert (
    pi_assertion       => pi_param1 IS NOT NULL
   ,pi_application     => c_log_application
   ,pi_error_name      => c_parameter_missing
   ,pi_error_arguments => PKG_LEHS.pt_error_arguments('pi_param1')
  );   

  PKG_LEHS.assert (
    pi_assertion       => pi_param2 IS NOT NULL
   ,pi_application     => c_log_application
   ,pi_error_name      => c_parameter_missing
   ,pi_error_arguments => PKG_LEHS.pt_error_arguments('pi_param2')
  );   
END test_it;
Ok, you might say "That's too much code! What's the advantage for the Developer?". Let' have a look on a short advantage List:

Assertions

Assertions can help the Developer to keep the Logic simple within his/her Application.

The first Argument is a BOOLEAN-Condition. If this this Condition fails the Assertion will raise the Exception the Developer has specified. In fact PKG_LEHS.assert is only a Combination of a IF-Statement with the Procedure PKG_LEHS.handle_exception or PKG_LEHS.handle_exception_and_log.

By Default a raised Exception will also be logged with the Log-Level defined for the Log-Application. If you want to supress this Behaviour you can set the Parameter pi_log to FALSE. Then only the Exception will be raised without any logging. Except you define a Log-Topic, then the failed Assertion will always be logged (because it makes no sense to give a Log-Topic without any logging).

Handle non user defined Exceptions

So, now you know how to raise a user defined named Exception. But what happens if Oracle raises an Exception by itself (i.E. you make a SELECT INTO and forget to handle the Exception NO_DATA_FOUND). For this Oracle gives you a very powerful enhancement to catch all Exceptions: 
DECLARE
  c_log_application CONSTANT PKG_LEHS.pst_application_name := 'LOG_APPLICATION';
  
  v_dummy BINARY_INTEGER;
BEGIN
  -- the following SELECT will cause the Exception NO_DATA_FOUND
  SELECT 1
    INTO v_dummy
    FROM dual
   WHERE x = 'Y';
EXCEPTION
  WHEN OTHERS THEN
    -- Log the Exception
    PKG_LEHS.log_exception (c_log_application);
    RAISE;
END;
As you can see above LEHS has a simple Solution to log the raised Exception. Simply call PKG_LEHS.log_exception and the raised Exception will be logged to the Line where the Exception has been raised. In the Example above the Line-Number is 7. This works also for Exceptions that are raised within Procedures, Functions and Triggers.

Any user defined named Exception, which will also be caught by WHEN OTHERS THEN, will not be logged because a Developer can decide at the time where the Exception will be raised if he/she also wants to log or not. Except you set the Parameter pi_log_app_exception to TRUE. This is useful for some Situations where you want to handle a user defined named Exception by PKG_LEHS.log_excepiton (see Topic Exception Messages).

Note: You have to re-raise the caught Exception by yourself. Otherwise the Exception will not be propagated to the calling PL/SQL-Block. The general Recommendation is, that you end every Procedure/Function with this general Exception Handling Code: 

CREATE OR REPLACE PROCEDURE prc_test IS
  c_log_application CONSTANT PKG_LEHS.pst_application_name := 'LOG_APPLICATION';
BEGIN
  -- do some stuff
  NULL;
EXCEPTION
  WHEN OTHERS THEN
    PKG_LEHS.log_exception (c_log_application);
    RAISE;
END prc_test;

When you put this Part of Code in nearby every Procedure/Function you automatically build your own Error-Stack with the Logging Routines.

Log Transactions

Because LEHS is a Log-System for the whole Database many Applications can use LEHS at the same time. The Log-Entries for the Table would be ordered by a Log-ID (look at View LEHS_LOGS.log_id, a Sequence for every Log-Entry). Ordering by this Log-ID shows you the Log-Entries in the correct Order.

But this Log-ID can't help if 2 Programs running at the same time to the same Application. Both Programs will fill the Log-Table but you have no chance to see, which Log-Entry has been created by which Program. For this Situation a Log-Transaction will help. Every Procedure creates a new Log-Transaction and the Log-Entries will be logged to this Log-Transaction ID. 

DECLARE
  c_log_application PKG_LEHS.pst_application_name := 'LOG_APPLICATION';
  
  -- Define a Variable to get the Log-Transaction ID  
  v_log_transaction BINARY_INTEGER;
BEGIN
  -- Start a new Log-Transaction
  v_log_transaction := PKG_LEHS.start_log_transaction (c_log_application);

  -- do some stuff

  -- close the Log-Transaction
  PKG_LEHS.end_log_transaction (c_log_application);
EXCEPTION
  WHEN OTHERS THEN
    PKG_LEHS.log_exception (c_log_application);
    PKG_LEHS.end_log_transaction (c_log_application);
    RAISE;
END;

A Log Transaction will be started by PKG_LEHS.start_log_transaction. The returned Transaction ID will automatically be stored within the Log-Context (Memory where all the needed Variables are stored) and will be logged automatically. This Transaction ID stays at the Log-Context until another call of PKG_LEHS.start_log_transaction has been done or the Log-Transaction has been closed by PKG_LEHS.end_log_transaction. Also, if you call another internal Procedure this Transaction ID will be used.

Join to an open Log Transaction

By calling PKG_LEHS.start_log_transaction the Transaction to the returned ID is in State OPEN until PKG_LEHS.end_log_transaction has been called. When you call another Procedure which also starts a new Log-Transaction you must join to the old Log-Transaction after returning to the calling Procedure. 
CREATE OR REPLACE PACKAGE BODY pkg_test IS
    pc_log_application CONSTANT PKG_LEHS.pst_application_name := 'LOG_APPLICATION';
	
  PROCEDURE inner_proc IS
    v_log_transaction BINARY_INTEGER;
  BEGIN
    v_log_transaction := PKG_LEHS.start_log_transaction (pc_log_application);

    -- do some stuff

    -- at the end
    PKG_LEHS.end_log_transaction (pc_log_application);
  EXCEPTION
    WHEN OTHERS THEN
      PKG_LEHS.log_exception (pc_log_application);
      PKG_LEHS.end_log_transaction (pc_log_application);
      RAISE;
  END inner_proc;

  PROCEDURE public_proc IS
    v_log_transaction BINARY_INTEGER;
  BEGIN
    -- first start the Log Transaction  
    v_log_transaction := PKG_LEHS.start_log_transaction (pc_log_application);

    -- now call the inner procedure
    BEGIN
      inner_proc;
      PKG_LEHS.join_log_transaction (pc_log_application, v_log_transaction);
    EXCEPTION
      WHEN OTHERS THEN
        PKG_LEHS.join_log_transaction (pc_log_application, v_log_transaction);
        RAISE;
    END;

    -- now end the Log Transaction
    PKG_LEHS.end_log_transaction;
  EXCEPTION
    WHEN OTHERS THEN
      PKG_LEHS.log_exception (pc_log_application);
      PKG_LEHS.end_log_transaction (pc_log_application);
      RAISE;
  END public_proc;  
END pkg_test;

Maybe a Developer will now say "What the Hell? Why should I join to a Transaction ID? Why can't I give the Transaction ID by a Parameter to the Log-Call?". The Answer: Simplicity and Performance. It is less complex to focus on the Development than handle complex logging. A Log Transaction is optional, you can use it but it is not obligatory. And: most of the Programs will run within one Log Transaction (also the Sub-Programs). So it is better to save the Transaction ID within the Log Context than giving it all the Time to the Subprograms. Imagine: You always have to give the Log-Transaction as by Parameters to your Sub-Programs. What a mess.

Keep it simple was one of the main focus during the Development of LEHS. LEHS should support the Developer and not embarrass him/her. By the Way: this Conception makes the real Application Code more readable (and maintainable) than giving the Transaction ID by Parameter (or even by setting a Package Variable or something else).

Transaction Keywords

So you see, some coding is needed to use a Log-Transaction. Uh, now we can different the Runtimes of our Programs (to the same Log-Application). But there is another feature you can use by the Log-Transactions. 
CREATE OR REPLACE PROCEDURE prc_test (
  pi_param1 IN VARCHAR2
 ,pi_param2 IN DATE DEFAULT NULL
 ,pi_param3 IN NUMBER DEFAULT 3.1415927
) IS
  c_procname CONSTANT VARCHAR2(30) := 'PRC_TEST';
  c_log_application CONSTANT PKG_LEHS.pst_application_name := 'LOG_APPLICATION';

  v_log_transaction BINARY_INTEGER;
BEGIN
  v_log_transaction := PKG_LEHS.start_log_transaction (c_log_application);

  -- set some Keywords to the Log-Transaction
  PKG_LEHS.add_transaction_key (c_log_application, v_log_transaction, 'PROCNAME', c_procname);
  PKG_LEHS.add_transaction_key (c_log_application, v_log_transaction, 'START_TIME', SYSDATE);
  PKG_LEHS.add_transaction_key (c_log_application, v_log_transaction, 'PI_PARAM1', pi_param1);
  PKG_LEHS.add_transaction_key (c_log_application, v_log_transaction, 'PI_PARAM2', pi_param2);
  PKG_LEHS.add_transaction_key (c_log_application, v_log_transaction, 'PI_PARAM3', pi_param3);

  -- do the Rest of the Stuff

  PKG_LEHS.end_log_transaction (c_log_application);
EXCEPTION
  WHEN OTHERS THEN
    PKG_LEHS.log_exception (c_log_application);
    PKG_LEHS.end_log_transaction (c_log_application);
    RAISE;
END prc_test;

You can register Keywords to your Log-Transaction. This feature is very powerful because it gives you the Possibility to search for the throughput of your Application. If your Application has been started by calling execute PRC_TEST ('first', TO_DATE('20.09.2009','DD.MM.RRRR'), 5.25); you can now use the following: 

SELECT transaction_id
  FROM lehs_transactions TRX
 WHERE TRX.application = 'LOG_APPLICATION'
   AND TRX.key = 'PROCNAME' AND TRX.value = 'PRC_TEST'
   AND EXISTS (SELECT 1
		 FROM lehs_transactions SUBTRX
		WHERE SUBTRX.transaction_id = TRX.transaction_id
		  AND SUBTRX.key = 'PI_PARAM1' AND SUBTRX.value = 'first'
	      )
   AND EXISTS (SELECT 1
		 FROM lehs_transactions SUBTRX
		WHERE SUBTRX.transaction_id = TRX.transaction_id
		  AND SUBTRX.key = 'PI_PARAM2'
		  AND SUBTRX.datatype = 'DATE'
		  AND TO_DATE(SUBTRX.value) = TO_DATE('20.09.2009','DD.MM.RRRR')
	      )
   AND EXISTS (SELECT 1
		 FROM lehs_transactions SUBTRX
		WHERE SUBTRX.transaction_id = TRX.transaction_id
		  AND SUBTRX.key = 'PI_PARAM3'
		  AND SUBTRX.datatype = 'NUMERIC'
		  AND TO_NUMBER(SUBTRX.value) >= 3
	      );

The Example above shows you all Log Transactions to the Log-Application LOG_APPLICATION with the following Characteristics:

In the End this Query shows us the Transaction ID of the throughput of our Program.

A Transaction Key can have one of the following Datatypes:

On the View LEHS_TRANSACTIONS all Transaction Keys with either the Datatype DATE, NUMBER or TIMESTAMP will be transformed to the Character Interpretation by simply converting with TO_CHAR without any formatting Pattern. Therefor it will use the Default of the Session so that you can reconvert this Value simply by calling either TO_DATE, TO_NUMBER or TO_TIMESTAMP (also every Function without a formatting Description so you also use the Default of the Session).

To avoid falling into a Conversion Error you can also query the Parameter Datatype before converting the Value (i.E. AND SUBTRX.datatype = 'NUMERIC').

Exception Messages

We already defined an Exception Message at the Topic Log Exceptions. Now we go deeper.

Exception Messages, which will be administered by LEHS can have the following Features:

Looks like a Marketing Table, isn't it? Ok, look at all these Features within one simple Example: 
DECLARE
  c_application CONSTANT PKG_LEHS.pst_application_name := 'LOG_APPLICATION';
BEGIN
  -- the following Error Definition would be delivered by a Script
  -- so this is called the Admin driven Installation of an Exception Message
  PKG_LEHS.add_message (
    pi_application      => c_application
   ,pi_error_name       => 'UNANTICIPATED ERROR'
   ,pi_error_code       => -20000
   ,pi_message          => 'Unanticipated Error %1 has been raised. Reason: %2'
   ,pi_show_error_stack => TRUE
   ,pi_nls_language     => 'AMERICAN'
  );

  PKG_LEHS.add_message (
    pi_application      => c_application
   ,pi_error_name       => 'UNANTICIPATED ERROR'
   ,pi_error_code       => -20000
   ,pi_message          => 'Unerwarteter Fehler %1 wurde ausgelöst. Begründung: %2'
   ,pi_show_error_stack => TRUE
   ,pi_nls_language     => 'GERMAN'
  );
END;

CREATE OR REPLACE PACKAGE pkg_constant IS
  pc_log_application CONSTANT PKG_LEHS.pst_application_name := 'LOG_APPLICATION';

  pc_unanticipated_error CONSTANT PKG_LEHS.pst_error_name := 'UNANTICIPATED ERROR';
  pc_missing_mandatory_parameter CONSTANT PKG_LEHS.pst_error_name := 'MANDATORY PARAMETER MISSING';

  pc_errgrp_entity1 CONSTANT PKG_LEHS.pst_error_name := 'ERROR GROUP ENTITY 1'
  pc_errgrp_entity2 CONSTANT PKG_LEHS.pst_error_name := 'ERROR GROUP ENTITY 2';
  pe_errgrp EXCEPTION;
  PRAGMA EXCEPTION_INIT (pe_errgrp, -20002);  
END pkg_constant;

CREATE OR REPLACE PACKGE BODY pkg_constant IS
BEGIN
  -- Initialization of the Exception Messages
  -- the following Exception Messages will be defined by the Application itself
  -- this is called Developer driven Installation.  
  PKG_LEHS.add_message (
    pi_application      => pc_log_application
   ,pi_error_name       => pc_unanticipated_error
   ,pi_error_code       => -20000
   ,pi_message          => 'Unanticipated Error %1 has been raised. Reason: %2'
   ,pi_show_error_stack => TRUE
   ,pi_nls_language     => 'AMERICAN'
  );  

  PKG_LEHS.add_message (
    pi_application      => pc_log_application
   ,pi_error_name       => pc_unanticipated_error
   ,pi_error_code       => -20000
   ,pi_message          => 'Unerwarteter Fehler %1 wurde ausgelöst. Begründung: %2'
   ,pi_show_error_stack => TRUE
   ,pi_nls_language     => 'GERMAN'
  );

  PKG_LEHS.add_message (
    pi_application      => pc_log_application
   ,pi_error_name       => pc_missing_mandatory_parameter
   ,pi_error_code       => -20001
   ,pi_message          => 'Mandatory Parameter %1 is missing or NULL'
   ,pi_show_error_stack => FALSE
   ,pi_nls_language     => 'AMERICAN'
  );  

  PKG_LEHS.add_message (
    pi_application      => pc_log_application
   ,pi_error_name       => pc_missing_mandatory_parameter
   ,pi_error_code       => -20001
   ,pi_message          => 'Zwingender Parameter %1 wurde nicht angegeben oder ist NULL'
   ,pi_show_error_stack => FALSE
   ,pi_nls_language     => 'GERMAN'
  );  

  PKG_LEHS.add_message (
    pi_application      => pc_log_application
   ,pi_error_name       => pc_errgrp_entity1
   ,pi_error_code       => -20002
   ,pi_message          => 'Error 1 of an Error Group: %1 (only English)'
   ,pi_show_error_stack => FALSE
   ,pi_nls_language     => 'AMERICAN'
  );  

  PKG_LEHS.add_message (
    pi_application      => pc_log_application
   ,pi_error_name       => pc_errgrp_entity2
   ,pi_error_code       => -20002
   ,pi_message          => 'Error 2 of an Error Group: %1 (only English)'
   ,pi_show_error_stack => FALSE
   ,pi_nls_language     => 'AMERICAN'
  );  
END pkg_constant;

CREATE OR REPLACE PROCEDURE use_lehs (
  pi_entity1 IN BOOLEAN
) IS
BEGIN
  IF NVL(pi_entity1, TRUE) THEN
    PKG_LEHS.handle_exception (
      pi_application     => PKG_CONSTANT.pc_log_application
     ,pi_error_name      => PKG_CONSTANT.pc_errgrp_entity1
     ,pi_error_arguments => PKG_LEHS.pt_error_arguments ('Argument')
    );
  ELSE
    PKG_LEHS.handle_exception (
      pi_application     => PKG_CONSTANT.pc_log_application
     ,pi_error_name      => PKG_CONSTANT.pc_errgrp_entity2
     ,pi_error_arguments => PKG_LEHS.pt_error_arguments ('Argument')
  END IF;
END use_lehs;

CREATE OR REPLACE PROCEDURE use_lehs2 (
  pi_entity1 IN BOOLEAN
) IS
BEGIN
  use_lehs (pi_entity1);
EXCEPTION
  WHEN PKG_CONSTANT.pe_errgrp THEN
    IF PKG_LEHS.is_named_exception (PKG_CONSTANT.pc_log_application, PKG_CONSTANT.pc_errgrp_entity1, SQLCODE) THEN
      -- When pc_errgrp_entity1 has been raised then only warn with a Log-Message but don't re-raise this Exception
      PKG_LEHS.warn (PKG_CONSTANT.pc_log_application, SQLERRM(SQLCODE));
    ELSE
      PKG_LEHS.log_exception (PKG_CONSTANT.pc_log_application, TRUE);  
      RAISE;
    END IF;
  WHEN OTHERS THEN
    PKG_LEHS.log_exception (PKG_CONSTANT.pc_log_application);
    RAISE;
END use_lehs2;

Done. Much code, isn't it? But not much ado nothing. This is a Recommendation by myself how to make an Application code:

Define a global Package for any Exception Names and the Log-Application as Constants itself!
Also define any PL/SQL named Exceptions you like to handle within your Application!

Then you can use these Constants from nearby any Procedure within the same Schema. The Package Body of this global Package can be used to initialize the Exception Messages when the Package is loaded into the Memory. With this Method you can redefine your Exception Messages every time you like.

Log Topics

Log Topics are, as also the Log Transactions, another way to separate your Log-Information in Pieces. Otherwise than the Log-Transaction (which cuts into the Runtime Throughputs) are the Log-Topics an organizational Way to cut the Log-Information in Pieces.

The best Way to describe Log-Topics is to describe, how a normal Book works. If you plan to write a Book you start with the first Chapter. This Chapter can have several Subchapters (and also these can Subchapters). Then you write the next Chapter. Also with several Subchapters. Finally you might have a Structure like these: 

  1. Install LEHS
    1.1 Download LEHS
    1.2 Create a User for LEHS
    1.3 Install LEHS
    1.4 Do some public Stuff
  2. Configure LEHS
    2.1 Who am I?
    2.2 Concepts
    2.3 Log Application
    2.4 Log Appender
      2.4.1 Appender Parameter
      2.4.2 Log Format
    2.5 Log Levels
    2.6 Programmable Log Levels
    2.7 Log Privileges
    2.8 Get the Metadata Information
Does this looks similar for you? Yes, this is called a Hierarchy. With Topics you can sort your Log-Entries by a Hierarchy. But this Chapter-List is just more than a normal Hierarchy. It's a Hierarchy with a strong self-managed Order within the same Hierarchy-Level. So we have a linked List within a Hierarchy-Level.

Defining a Topic is very easy. A Topic has always identified by the Name, which is case sensitive. A Top-Topic is a topic with no Parent-Topic. The Top of the Tree!
Within the same Hierarchy you can define the Position of the Topic either by the Position itself or by a Predecessor Topic.

"And why is this so powerful?" Let' have a look on another Example: 

CREATE OR REPLACE PROCEDURE prc_test IS
  c_log_application CONSTANT PKG_LEHS.pst_application_name := 'LOG_APPLICATION';

  -- Define the Log-Topics for the technical Log
  c_tl CONSTANT PKG_LEHS.pst_log_topic := 'technical Log';
  c_tl_main CONSTANT PKG_LEHS.pst_log_topic := 'Main Routine';
  c_tl_sub1 CONSTANT PKG_LEHS.pst_log_topic := 'Sub Routine 1';
  c_tl_sub2 CONSTANT PKG_LEHS.pst_log_topic := 'Sub Routine 2';  

  -- Define the Log-Topics for the Business Log
  c_bl CONSTANT PKG_LEHS.pst_log_topic := 'Business Log';

  PROCEDURE sub1 IS
  BEGIN
    PKG_LEHS.debug (c_log_application, c_tl_sub1, 'Entry from Subroutine1');  
  EXCEPTION
    WHEN OTHERS THEN
      PKG_LEHS.log_exception (c_log_application, c_tl_sub1);
  END sub1;

  PROCEDURE sub2 IS
  BEGIN
    PKG_LEHS.debug (c_log_application, c_tl_sub1, 'Entry from Subroutine2');  
  EXCEPTION
    WHEN OTHERS THEN
      PKG_LEHS.log_exception (c_log_application, c_tl_sub2);
  END sub2;
BEGIN
  -- define the Log Hierarchy
  PKG_LEHS.add_topic (c_log_application, c_bl, NULL, TO_CHAR(NULL));
  PKG_LEHS.add_topic (c_log_application, c_tl, NULL, c_bl);
  PKG_LEHS.add_topic (c_log_application, c_tl_main, c_tl, TO_CHAR(NULL));
  PKG_LEHS.add_topic (c_log_application, c_tl_sub1, c_tl_main, TO_CHAR(NULL));
  PKG_LEHS.add_topic (c_log_application, c_tl_sub2, c_tl_main, c_tl_sub1);

  PKG_LEHS.info (c_log_application, c_tl_main, 'Start the Application');
  PKG_LEHS.info (c_log_application, c_bl, 'Calculation has been started');
  
  PKG_LEHS.debug (c_log_application, c_tl_main, 'Call sub1');
  sub1;

  PKG_LEHS.debug (c_log_application, c_tl_main, 'Call sub2');
  sub2;

  PKG_LEHS.info (c_log_application, c_tl_main, 'End the Application');
  PKG_LEHS.info (c_log_application, c_bl, 'Calculation has been ended successfully');
EXCEPTION
  WHEN OTHERS THEN
    PKG_LEHS.log_exception (c_log_application, c_tl_main);
    PKG_LEHS.error (c_log_application, c_bl, 'An Error has been occured. See technical Log for further Information');
    RAISE;
END prc_test;
You defined two main Topics: a technical Log (with some Sub-Topics) and a Business Log. The Business Log is for your Clients. The technical Log is for the Developers when something has gone wrong. Your Clients would only know when the Application has been started and if the Application has been ended successfully or with an Error. But your Clients would not know which Error has been occured. So your Clients will query the Logs with the following Statement: 
SELECT *
  FROM lehs_logs
 WHERE log_topic = 'Business Log'
ORDER BY log_id DESC
When something has gone wrong it's important that the Analyst (or the Developer) has some good Structure within the Logs to find the Error quickly. So you will go through your technical Log (with all the Sub-Topics) to get the technical Log-Information: 
SELECT LOG.*
  FROM lehs_topics TOP
RIGHT OUTER JOIN lehs_logs LOG
              ON LOG.application = TOP.application
             AND LOG.log_topic = TOP.topic_name
CONNECT BY PRIOR TOP.topic_name = TOP.parent_topic_name
       AND TOP.application = 'LOG_APPLICATION'
START WITH TOP.topic_name = 'technical Log'
       AND TOP.application = 'LOG_APPLICATION'
ORDER BY LOG.log_id DESC
This is the Reason why the Log Topics are really powerful. You have a Solution for both, technical Log and Business Log with one Tool and the Code is maintainable. When something has gone wrong you can get the needed Log with one SELECT-Statement! And this can be packed into a View. I think that this has really big Power!

Note: If you ask me for the Reason of the horizontal Order (predecessing) then I will tell you: It's just for fun. Maybe one Day there's neccessary and then you can use it. Until then: Have fun with it. Because LEHS is open Source you can get some Parts as a Design Pattern. I hope so ;-)

Where to look for Information?

If the LEHS Admin has enabled the Table-Appender, the written Logs (and Exceptions) will be available by the View 
SELECT * FROM lehs_logs WHERE application = '<Log Application>' ORDER BY log_id DESC;

You can query Log Transactions with its Keywords by this SELECT-Statement: 
SELECT * FROM lehs_transactions WHERE application = '<Log Application>';

The defined Topics will be available by the View 
SELECT * FROM lehs_topics WHERE application = '<Log Application>';
The defined Log-Levels will be available by the View 
SELECT * FROM lehs_levels WHERE application = '<Log Application>';
If you want to show the whole Topic-Tree (in the correct Order of the Sub-Topics) then you can get this Query: 
SELECT *
  FROM (SELECT level AS top_level
              ,application
              ,topic_name
              ,parent_topic_name
              ,topic_name_predecessor
          FROM lehs_topics
        CONNECT BY PRIOR topic_name = topic_name_predecessor
               AND application = 'LEHS'
        START WITH topic_name_predecessor IS NULL
               AND application = 'LEHS')
CONNECT BY PRIOR topic_name = parent_topic_name
START WITH parent_topic_name IS NULL
ORDER SIBLINGS BY top_level
                 ,topic_name

A Word about Wrappers

It is a legal Way to wrap the Procedures of LEHS so that your Developers doesn't know about the real Log-Engine in the Background. In fact, there existing several Reasons that you write a Wrapper: As you can see above ther're existing several good Reasons for a Wrapper Package. Normally the Package PKG_LEHS gets the Position of the Caller from the Stack. The next Caller after PKG_LEHS will be used to write the Log to the Appender. For a Wrapper this is not the truth. So, if you use your own Wrapper for LEHS then you have to call the Methods of the Package PKG_WRAPPED_LEHS. All Methods of this Package will just ignore the next Level after PKG_LEHS and get the Caller of the Wrapper. So your Wrapper-Class will just be ignored and the real Caller will be get.

A simple Example: 

CREATE OR REPLACE PACKAGE LEHS_EXAMPLE.pkg_my_lehs_wrapper IS
  pc_own_log_level VARCHAR2(30) := 'MY OWN LOG LEVEL';

  PROCEDURE log (
    pi_log_level IN VARCHAR2
   ,pi_message   IN CLOB
  );

  PROCEDURE start_transaction (
    pi_keyword1 IN VARCHAR2
   ,pi_keyword2 IN DATE
   ,pi_keyword3 IN NUMBER
  );

  PROCEDURE end_transaction;

  PROCEDURE fatal (pi_message IN CLOB);
  PROCEDURE error (pi_message IN CLOB);
  PROCEDURE warn (pi_message IN CLOB);
  PROCEDURE info (pi_message IN CLOB);
  PROCEDURE debug (pi_message IN CLOB);
  PROCEDURE trace (pi_message IN CLOB);

  PROCEDURE mandatory_parameter_missing (pi_parameter_name IN VARCHAR2);
  PROCEDURE another_user_defined_exception (
    pi_text_parameter    IN VARCHAR2
   ,pi_numeric_parameter IN NUMBER
   ,pi_date_parameter    IN DATE
  );
END LEHS_EXAMPLE.pkg_my_lehs_wrapper;
/

CREATE OR REPLACE PACKAGE BODY LEHS_EXAMPLE.pkg_my_lehs_wrapper IS
  pc_app CONSTANT PKG_LEHS.pst_application_name := 'MY LOG APP';

  pc_mandatory_parameter_missing CONSTANT PKG_LEHS.pst_error_name := 'MANDATORY PARAMETER MISSING';
  pc_another_user_def_exception  CONSTANT PKG_LEHS.pst_error_name := 'ANOTHER USER DEFINED EXCEPTION';

  pv_transaction PKG_LEHS.pst_log_transaction := NULL;

  PROCEDURE log (
    pi_log_level IN VARCHAR2
   ,pi_message   IN CLOB
  ) IS
  BEGIN
    PKG_WRAPPED_LEHS.log (
      pi_application => pc_app
     ,pi_log_level   => pi_log_level
     ,pi_message     => pi_message
    );
  END log;

  PROCEDURE start_transaction (
    pi_keyword1 IN VARCHAR2
   ,pi_keyword2 IN DATE
   ,pi_keyword3 IN NUMBER
  ) IS
    c_keyword1 CONSTANT VARCHAR2(30) := 'KEYWORD1';
    c_keyword2 CONSTANT VARCHAR2(30) := 'KEYWORD2';
    c_keyword3 CONSTANT VARCHAR2(30) := 'KEYWORD3';
  BEGIN
    IF pv_transaction IS NOT NULL THEN
      RETURN;
    END IF;

    pv_transaction := PKG_LEHS.start_transaction (pc_app);
    PKG_LEHS.add_transaction_keyword (pc_app, pv_transaction, c_keyword1, pi_keyword1);
    PKG_LEHS.add_transaction_keyword (pc_app, pv_transaction, c_keyword2, pi_keyword2);
    PKG_LEHS.add_transaction_keyword (pc_app, pv_transaction, c_keyword3, pi_keyword3);
  END start_transaction;

  PROCEDURE end_transaction IS
  BEGIN
    IF pv_transaction IS NULL THEN
      RETURN;
    END IF;

    PKG_LEHS.end_log_transaction (pc_app);
  END end_transaction;

  PROCEDURE fatal (pi_message IN CLOB) IS
  BEGIN
    PKG_WRAPPED_LEHS.fatal (pc_app, pi_message);
  END fatal;

  PROCEDURE error (pi_message IN CLOB) IS
  BEGIN
    PKG_WRAPPED_LEHS.error (pc_app, pi_message);
  END error;

  PROCEDURE warn (pi_message IN CLOB) IS
  BEGIN
    PKG_WRAPPED_LEHS.warn (pc_app, pi_message);
  END warn;

  PROCEDURE info (pi_message IN CLOB) IS
  BEGIN
    PKG_WRAPPED_LEHS.info (pc_app, pi_message);
  END info;

  PROCEDURE debug (pi_message IN CLOB) IS
  BEGIN
    PKG_WRAPPED_LEHS.debug (pc_app, pi_message);
  END debug;

  PROCEDURE trace (pi_message IN CLOB) IS
  BEGIN
    PKG_WRAPPED_LEHS.trace (pc_app, pi_message);
  END trace;

  PROCEDURE mandatory_parameter_missing (pi_parameter_name IN VARCHAR2) IS
  BEGIN
    PKG_WRAPPED_LEHS.handle_exception_and_log (
      pi_application     => pc_app
     ,pi_error_name      => pc_mandatory_parameter_missing
     ,pi_error_arguments => PKG_LEHS.pt_error_arguments (UPPER(pi_parameter_name))
    );
  END mandatory_parameter_missing;

  PROCEDURE another_user_defined_exception (
    pi_text_parameter    IN VARCHAR2
   ,pi_numeric_parameter IN NUMBER
   ,pi_date_parameter    IN DATE
  ) IS
  BEGIN
    PKG_WRAPPED_LEHS.handle_exception_and_log (
      pi_application     => pc_app
     ,pi_error_name      => pc_another_user_def_exception
     ,pi_error_arguments => PKG_LEHS.pt_error_arguments (
        pi_text_parameter
       ,TO_CHAR(pi_numeric_parameter)
       ,TO_CHAR(pi_date_parameter)
      );
    );
  END another_user_defined_exception;
BEGIN
  PKG_LEHS.add_message (
    pi_application => pc_app
   ,pi_error_name  => pc_mandatory_parameter_missing
   ,pi_error_code  => -20000
   ,pi_message     => 'Mandatory Parameter %1 is missing or NULL'
  );

  PKG_LEHS.add_message (
    pi_application => pc_app
   ,pi_error_name  => pc_another_user_def_exception
   ,pi_error_code  => -20999
   ,pi_message     => 'Another Exception with Text Parameter %1, numeric Parameter %2 and a Date Parameter %3'
  );
END LEHS_EXAMPLE.pkg_my_lehs_wrapper;
/
As you can see in the Example (and of course in the API) not all Methods of the Package PKG_LEHS would be overwritten in the Package PKG_WRAPPED_LEHS.
The Reason is, that only these Methods are required to be overwritten where the Caller of PKG_LEHS have to be ignored so that the Caller of this Wrapper-Package would be recognized as the real Caller.

One of the best Reasons by using a Wrapper-Package is, that the real Log-Application can be hidden. So the Developers can use your Package but they don't know anything about the Log-Engine in the Background (that could either be LEHS or Log4PLSQL or another else).