LEHS - Log- and Exception Handling System for ORACLE


Package pkg_lehs_table_appender

This is an Appender Package for LEHS. Look at PKG_LEHS_APPENDER on more detailed Information about developing an Appender Package.

You can use this Package by registering it as an Appender to a Log-Application (see PKG_LEHS_SYSTEM.register_appender).


This Appender Package logs all Log-Entries to the Table LEHS_LOG_TABLE_BASIC and the Log Transactions to the Tables LEHS_TRANSACTIONS and LEHS_TRANSACTION_KEYS.

These saved Log-Informations are provided by these Views:
original Table providing View public Synonym
LEHS_LOG_TABLE_BASIC LEHS_LOGS_VIEW LEHS_LOGS
LEHS_TRANSACTIONS
LEHS_TRANSACTION_KEYS
LEHS_TRANSACTION_VIEW LEHS_TRANSACTIONS
Warning: the public Synonyms are available for every User on the Database but the Information is only provided, if the User has Read or Read/Write Privileges on the Log-Application.

Appender Parameters:
Parameter Name Datatype valid Values Description
RETENTION_TIME PKG_LEHS_APPENDER.pst_param_value_number 0 - 99999.
0 = infinite Retention Time (Default)
Retention Time (in Minutes) of the Log-Entries
REMOVE_UNUSED_TRANSACTIONS PKG_LEHS_APPENDER.pst_param_value_clob
  • TRUE
  • FALSE (Default)
Should unused Transactions (created Transactions without any Log-Messages) also be removed regardless of the Retention Time?
REMOVE_LOGS_IN_OPEN_TRX PKG_LEHS_APPENDER.pst_param_value_clob
  • TRUE
  • FALSE (Default)
Should Log-Entries, whose Log-Transaction is in an open State, also be removed?


Field Summary
 PKG_LEHS_APPENDER.pst_parameter_namepc_param_remove_open_logs
           Parameter Name: REMOVE_LOGS_IN_OPEN_TRX
 PKG_LEHS_APPENDER.pst_parameter_namepc_param_remove_unused_trx
           Parameter Name: REMOVE_UNUSED_TRANSACTIONS
 PKG_LEHS_APPENDER.pst_parameter_namepc_param_retention_time
           Parameter Name: RETENTION_TIME

Method Summary
 add_transaction_key(pi_application PKG_LEHS_APPENDER.pst_application_name, pi_transaction BINARY_INTEGER, pi_key VARCHAR2, pi_value CLOB)
           Adds a new or change an existing Transaction Key on Table LEHS_TRANSACTION_KEYS.
 add_transaction_key(pi_application PKG_LEHS_APPENDER.pst_application_name, pi_transaction BINARY_INTEGER, pi_key VARCHAR2, pi_value NUMBER)
           Adds a new or change an existing Transaction Key on Table LEHS_TRANSACTION_KEYS.
 add_transaction_key(pi_application PKG_LEHS_APPENDER.pst_application_name, pi_transaction BINARY_INTEGER, pi_key VARCHAR2, pi_value DATE)
           Adds a new or change an existing Transaction Key on Table LEHS_TRANSACTION_KEYS.
 add_transaction_key(pi_application PKG_LEHS_APPENDER.pst_application_name, pi_transaction BINARY_INTEGER, pi_key VARCHAR2, pi_value TIMESTAMP_UNCONSTRAINED)
           Adds a new or change an existing Transaction Key on Table LEHS_TRANSACTION_KEYS.
 add_transaction_key(pi_application PKG_LEHS_APPENDER.pst_application_name, pi_transaction BINARY_INTEGER, pi_key VARCHAR2, pi_value DSINTERVAL_UNCONSTRAINED)
           Adds a new or change an existing Transaction Key on Table LEHS_TRANSACTION_KEYS.
 add_transaction_key(pi_application PKG_LEHS_APPENDER.pst_application_name, pi_transaction BINARY_INTEGER, pi_key VARCHAR2, pi_value YMINTERVAL_UNCONSTRAINED)
           Adds a new or change an existing Transaction Key on Table LEHS_TRANSACTION_KEYS.
 check_param(pi_application PKG_LEHS_APPENDER.pst_application_name, pi_name PKG_LEHS_APPENDER.pst_parameter_name, pi_value PKG_LEHS_APPENDER.pst_param_value_number)
           Checks the Value of an Appender Parameter.
 check_param(pi_application PKG_LEHS_APPENDER.pst_application_name, pi_name PKG_LEHS_APPENDER.pst_parameter_name, pi_value PKG_LEHS_APPENDER.pst_param_value_clob)
           Checks the Value of an Appender Parameter.
 cleanup(pi_application PKG_LEHS_APPENDER.pst_application_name)
           Cleans up the internal used Buffer for the Log-Application
 end_log_transaction(pi_application PKG_LEHS_APPENDER.pst_application_name, pi_transaction BINARY_INTEGER)
           Updates the Transaction ID at Table LEHS_TRANSACTIONS to be closed (set the closed Timestamp).
 log(pi_log_line PKG_LEHS_APPENDER.pr_log_line)
           Adds a single Log-Entry to the Table LEHS_LOG_TABLE_BASIC
 maintain_tables()
           Maintenance of the Log Entries.
 register(pi_application PKG_LEHS_APPENDER.pst_application_name)
           Creates a Partition or reuse an existing Partition on the Table LEHS_LOG_TABLE_BASIC.
 set_param(pi_application PKG_LEHS_APPENDER.pst_application_name, pi_name PKG_LEHS_APPENDER.pst_parameter_name, pi_value PKG_LEHS_APPENDER.pst_param_value_number)
           Sets the registered Appender Parameter during the Initialization of the Appender
 set_param(pi_application PKG_LEHS_APPENDER.pst_application_name, pi_name PKG_LEHS_APPENDER.pst_parameter_name, pi_value PKG_LEHS_APPENDER.pst_param_value_clob)
           Sets the registered Appender Parameter during the Initialization of the Appender
 start_log_transaction(pi_application PKG_LEHS_APPENDER.pst_application_name, pi_transaction BINARY_INTEGER)
           Adds a new Log-Transaction to the Table LEHS_TRANSACTIONS

Field Detail

pc_param_retention_time

  public PKG_LEHS_APPENDER.pst_parameter_name pc_param_retention_time
Parameter Name: RETENTION_TIME

pc_param_remove_unused_trx

  public PKG_LEHS_APPENDER.pst_parameter_name pc_param_remove_unused_trx
Parameter Name: REMOVE_UNUSED_TRANSACTIONS

pc_param_remove_open_logs

  public PKG_LEHS_APPENDER.pst_parameter_name pc_param_remove_open_logs
Parameter Name: REMOVE_LOGS_IN_OPEN_TRX

Method Detail

register

  public  register(pi_application PKG_LEHS_APPENDER.pst_application_name)
Creates a Partition or reuse an existing Partition on the Table LEHS_LOG_TABLE_BASIC.

If this Package will be registered as an Appender by PKG_LEHS_SYSTEM.register_appender then this Procedure will be called (see PKG_LEHS_APPENDER for more Details on Appender Packages).

If the Table LEHS_LOG_TABLE_BASIC is list partitioned by the Column APPLICATION, then this Procedure either creates a new Partition named as the Log-Application or it reuses an existing Partition, where the Log-Application exists within the Value-List of this existing Partition.

Parameters:
pi_application - Name of the Log-Application this Package will be registered to as an Appender
Throws:
ORA-20001 - [MANDATORY PARAMETER MISSING] A mandatory Parameter is missing
ORA-20032 - [INVALID APPLICATION NAME] The Name of the Log-Application is invalid to be used by this Appender Package
ORA-20033 - [APPLICATION NOT SERVED BY PARTITION] The Log-Application is not serviced by the Partition-List of the Partition named as the Log-Application

check_param

  public  check_param(pi_application PKG_LEHS_APPENDER.pst_application_name, 
pi_name PKG_LEHS_APPENDER.pst_parameter_name,
pi_value PKG_LEHS_APPENDER.pst_param_value_number)
Checks the Value of an Appender Parameter.

Will be automatically called by PKG_LEHS_SYSTEM.set_appender_parameter

Parameters:
pi_application - Name of the Log-Application
pi_name - Name of the Appender Parameter
pi_value - Value of the Appender Parameter
Throws:
ORA-20009 - [INVALID APPENDER PARAMETER NAME] Invalid Parameter Name or Datatype of this Parameter is invalid
ORA-20009 - [FAILED ASSERTION FOR APPENDER PARAMETER WITH REASON] The Value of the Parameter is invalid

check_param

  public  check_param(pi_application PKG_LEHS_APPENDER.pst_application_name, 
pi_name PKG_LEHS_APPENDER.pst_parameter_name,
pi_value PKG_LEHS_APPENDER.pst_param_value_clob)
Checks the Value of an Appender Parameter.

Will be automatically called by PKG_LEHS_SYSTEM.set_appender_parameter

Parameters:
pi_application - Name of the Log-Application
pi_name - Name of the Appender Parameter
pi_value - Value of the Appender Parameter
Throws:
ORA-20009 - [INVALID APPENDER PARAMETER NAME] Invalid Parameter Name or Datatype of this Parameter is invalid
ORA-20009 - [FAILED ASSERTION FOR APPENDER PARAMETER WITH REASON] The Value of the Parameter is invalid

set_param

  public  set_param(pi_application PKG_LEHS_APPENDER.pst_application_name, 
pi_name PKG_LEHS_APPENDER.pst_parameter_name,
pi_value PKG_LEHS_APPENDER.pst_param_value_number)
Sets the registered Appender Parameter during the Initialization of the Appender
Parameters:
pi_application - Name of the Log-Application
pi_name - Name of the Appender Parameter
pi_value - Value of the Appender Parameter

set_param

  public  set_param(pi_application PKG_LEHS_APPENDER.pst_application_name, 
pi_name PKG_LEHS_APPENDER.pst_parameter_name,
pi_value PKG_LEHS_APPENDER.pst_param_value_clob)
Sets the registered Appender Parameter during the Initialization of the Appender
Parameters:
pi_application - Name of the Log-Application
pi_name - Name of the Appender Parameter
pi_value - Value of the Appender Parameter

log

  public  log(pi_log_line PKG_LEHS_APPENDER.pr_log_line)
Adds a single Log-Entry to the Table LEHS_LOG_TABLE_BASIC
Parameters:
pi_log_line - Log Information provided by LEHS

cleanup

  public  cleanup(pi_application PKG_LEHS_APPENDER.pst_application_name)
Cleans up the internal used Buffer for the Log-Application
Parameters:
pi_application - Name of the Log-Application

start_log_transaction

  public  start_log_transaction(pi_application PKG_LEHS_APPENDER.pst_application_name, 
pi_transaction BINARY_INTEGER)
Adds a new Log-Transaction to the Table LEHS_TRANSACTIONS
Parameters:
pi_application - Name of the Log-Application
pi_transaction - created Transaction ID

end_log_transaction

  public  end_log_transaction(pi_application PKG_LEHS_APPENDER.pst_application_name, 
pi_transaction BINARY_INTEGER)
Updates the Transaction ID at Table LEHS_TRANSACTIONS to be closed (set the closed Timestamp).
Parameters:
pi_application - Name of the Log-Application
pi_transaction - Transaction ID to be closed

add_transaction_key

  public  add_transaction_key(pi_application PKG_LEHS_APPENDER.pst_application_name, 
pi_transaction BINARY_INTEGER,
pi_key VARCHAR2,
pi_value CLOB)
Adds a new or change an existing Transaction Key on Table LEHS_TRANSACTION_KEYS.

If a NULL-Value is given then the existing Transaction Key would be removed from Table LEHS_TRANSACTION_KEYS.

Parameters:
pi_application - Name of the Log-Application
pi_transaction - existing Transaction ID
pi_key - Name of the Transaction Key
pi_value - Value of the Transaction Key

add_transaction_key

  public  add_transaction_key(pi_application PKG_LEHS_APPENDER.pst_application_name, 
pi_transaction BINARY_INTEGER,
pi_key VARCHAR2,
pi_value NUMBER)
Adds a new or change an existing Transaction Key on Table LEHS_TRANSACTION_KEYS.

If a NULL-Value is given then the existing Transaction Key would be removed from Table LEHS_TRANSACTION_KEYS.

Parameters:
pi_application - Name of the Log-Application
pi_transaction - existing Transaction ID
pi_key - Name of the Transaction Key
pi_value - Value of the Transaction Key

add_transaction_key

  public  add_transaction_key(pi_application PKG_LEHS_APPENDER.pst_application_name, 
pi_transaction BINARY_INTEGER,
pi_key VARCHAR2,
pi_value DATE)
Adds a new or change an existing Transaction Key on Table LEHS_TRANSACTION_KEYS.

If a NULL-Value is given then the existing Transaction Key would be removed from Table LEHS_TRANSACTION_KEYS.

Parameters:
pi_application - Name of the Log-Application
pi_transaction - existing Transaction ID
pi_key - Name of the Transaction Key
pi_value - Value of the Transaction Key

add_transaction_key

  public  add_transaction_key(pi_application PKG_LEHS_APPENDER.pst_application_name, 
pi_transaction BINARY_INTEGER,
pi_key VARCHAR2,
pi_value TIMESTAMP_UNCONSTRAINED)
Adds a new or change an existing Transaction Key on Table LEHS_TRANSACTION_KEYS.

If a NULL-Value is given then the existing Transaction Key would be removed from Table LEHS_TRANSACTION_KEYS.

Parameters:
pi_application - Name of the Log-Application
pi_transaction - existing Transaction ID
pi_key - Name of the Transaction Key
pi_value - Value of the Transaction Key

add_transaction_key

  public  add_transaction_key(pi_application PKG_LEHS_APPENDER.pst_application_name, 
pi_transaction BINARY_INTEGER,
pi_key VARCHAR2,
pi_value DSINTERVAL_UNCONSTRAINED)
Adds a new or change an existing Transaction Key on Table LEHS_TRANSACTION_KEYS.

If a NULL-Value is given then the existing Transaction Key would be removed from Table LEHS_TRANSACTION_KEYS.

Parameters:
pi_application - Name of the Log-Application
pi_transaction - existing Transaction ID
pi_key - Name of the Transaction Key
pi_value - Value of the Transaction Key

add_transaction_key

  public  add_transaction_key(pi_application PKG_LEHS_APPENDER.pst_application_name, 
pi_transaction BINARY_INTEGER,
pi_key VARCHAR2,
pi_value YMINTERVAL_UNCONSTRAINED)
Adds a new or change an existing Transaction Key on Table LEHS_TRANSACTION_KEYS.

If a NULL-Value is given then the existing Transaction Key would be removed from Table LEHS_TRANSACTION_KEYS.

Parameters:
pi_application - Name of the Log-Application
pi_transaction - existing Transaction ID
pi_key - Name of the Transaction Key
pi_value - Value of the Transaction Key

maintain_tables

  public  maintain_tables()
Maintenance of the Log Entries.

This Procedure maintains the Log-Tables (LEHS_LOG_TABLE_BASIC, LEHS_TRANSACTIONS and LEHS_TRANSACTION_KEYS) in accordance to the registered Appender Parameters for every Log-Application this Package has been registered to as an Appender

The maintenance follows these Rules:

This Procedure should be called automatically through a maintenance Window.

LEHS - Log- and Exception Handling System for ORACLE