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).

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?

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

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:

• If the Parameter RETENTION_TIME is set to 0 then no Rows will be removed from neither the Table LEHS_LOG_TABLE_BASIC nor from the Tables LEHS_TRANSACTIONS and LEHS_TRANSACTION_KEYS
• Every Entry at the Table LEHS_LOG_TABLE_BASIC, whose Log-Time is older than SYSDATE - RETENTION_TIME will be removed unless it is in an open Transaction. (if the Parameter REMOVE_LOGS_IN_OPEN_TRX is set to FALSE)
• If the Parameter REMOVE_LOGS_IN_OPEN_TRX is set to TRUE then every Entry at the Table LEHS_LOG_TABLE_BASIC, whose Log-Time is older than SYSDATE - RETENTION_TIME will be removed regardless if it is in an open Transaction.
• All closed Transactions, which have no Relation to a removed Row at Table LEHS_LOG_TABLE_BASIC will be removed from the Tables LEHS_TRANSACTIONS and LEHS_TRANSACTION_KEYS (if the Parameter REMOVE_UNUSED_TRANSACTIONS is set to FALSE)
• If the Parameter REMOVE_UNUSED_TRANSACTIONS is set to TRUE then all Transactions, which have no relation to any Log-Entry at Table LEHS_LOG_TABLE_BASIC will be removed from the Tables LEHS_TRANSACTIONS and LEHS_TRANSACTION_KEYS.

This Procedure should be called automatically through a maintenance Window.