This document provides a quick How-To for the ora-logger application. As time permits, additional documentation will be coming to provide details on the additional functionality provided by the application.
The ora-logger application is designed to provide a logging framework that can be turned on and off within a production Oracle environment without needing to compile or recompile any code. The calls to the logging framework can be placed into source code and compiled into a production environment with little effect on the performance of the unmodified source code; when the logging process is disabled. When necessary, the logging process can be turned on using various methods via parameter values stored in an Oracle table. This functionality gives the user the ability to turn logging on/off on the fly without needing to recompile any source code.
The first step to logging data is placing calls within your source code to the logging package. There are several predefined procedures configured to do this that provide access to the logging levels. These procedures include:
log_error(message, id) log_warning(message, id) log_debug(message, id) log_deep_debug(message, id) log_info(message, id)
All of these procedures take a VARCHAR message and a BINARY_INTEGER id. The id is not required and has a default value of 0. The intended purpose of the id field is to provide the end user with the ability to identify individual logging messages with a distinct id. The use of this field is totally at the end users discretion.Additionally, the error and warning procedures will inspect the Oracle session variables to determine if an error has occurred. If an error has occurred, the error message will be appended to any message supplied. Therefore, the following are the simplest calls to the logging procedures:
log_error log_warning log_debug(message) log_deep_debug(message) log_info(message)
After these calls are added to source code, they provide the ability to log data. By default, only error level logging messages will be written to the logging table. To change this behavior and to increase the logging level, the developer / end user must set the parameters to increase the level.
The logging levels can be hard-coded using the package level global variables defined within the ora_logger package. These variables may/will be updated as a part of standard processing and should not be used as a method of setting the logging level consistently. These variables should only be used to set the default logging level for the various methods of enabling the logging process.
The logging level can be changed at the Oracle session level and will effect only the current session. To set the logging level for a specific session, use the ora_logger.set_debug_level procedure:
BEGIN ora_logger.set_debug_level(CONSTANTS.C_DEEP_DEBUG); END;
The input parameter for the logging level should correspond to the logging levels defined within the CONSTANTS package. This will set the logging level to DEEP_DEBUG and all messages written with a logging level less than or equal to DEEP_DEBUG will be written to the logging table. This includes ERROR, WARNING, DEBUG, and DEEP_DEBUG.
Setting the logging level at a session level will have an immediate effect and all logging calls executed afterwards will use this value to determine whether the data should be recorded to the logging table. This level may be superseded by other logging configuration settings, but only in terms of logging at a higher level. The logging level will never be set at a higher level. All logic in the application is based on the highest logging level being used at all times.
The session logging level can be reset by calling the set_debug_level with a lower level, or by disconnecting/reconnecting to Oracle.
The parameter logging methods are set using the table ORA_LOGGER_PARAMETERS within the ORA_LOGGER schema. To set the logging level, create a new record in the ORA_LOGGER_PARAMETERS table with a distinct PARAMETER_KEY (user defined value) and an OBJECT_NAME equal to the desired parameter method value. Once the record is created, it will apply to all new connections. Additionally, the ora_logger package will reload the ORA_LOGGER_PARAMETERS table at an interval defined by ORA_LOGGER.g_tab_load_mins. The time between reload can be configured using the setting TABLE_RELOAD_MINS in the ORA_LOGGER_PARAMETERS table.
The parameter methods include:
GLOBAL PROCESS PACKAGE PROCEDURE USER
To increase or decrease the logging level, set the debug_level to the appropriate value from the CONSTANTS package:
INSERT INTO ORA_LOGGER_PARAMETERS (
PARAMETER_KEY,
OBJECT_NAME,
DEBUG_LEVEL)
VALUES (1,
'GLOBAL',
CONSTANTS.DEBUG);
INSERT INTO ORA_LOGGER_PARAMETERS (
PARAMETER_KEY,
OBJECT_NAME,
DEBUG_LEVEL)
VALUES (2,
'SCOTT',
CONSTANTS.DEEP_DEBUG);
INSERT INTO ORA_LOGGER_PARAMETERS (
PARAMETER_KEY,
OBJECT_NAME,
DEBUG_LEVEL)
VALUES (3,
'ORDERS',
CONSTANTS.INFO);
The first record sets the global logging level to DEBUG. This change has an effect on all calls to the ora_logger package. Any logging calls with a logging level less or equal to DEBUG will be logged to the logging table. This includes ERROR, WARNING, and DEBUG.
The second record sets the logging level for User SCOTT to DEEP_DEBUG. The parameter values do not check for object or method type. Therefore, if another object or logging method is named SCOTT, it’s logging level will be set to DEEP_DEBUG. Any logging calls with a logging level less than or equal to DEEP_DEBUG will be logged to the logging table. This includes ERROR, WARNING, DEBUG, and DEEP_DEBUG.
The third record sets the logging level of the Package ORDERS to INFO. The parameter values do not check for object or method type. Therefore, if another object or logging method is named ORDERS, it’s logging level will be set to INFO. Any logging calls with a logging level less than or equal to INFO will be logged to the logging table. This includes ERROR, WARNING, DEBUG, DEEP_DEBUG, and INFO.
Parameter methods may be set by default or may be set at run time by the programmer.
The global method is set by default and is all encompassing. It defines the lowest level of logging across the ora-logger application and applies to all calls to the ora_logger package.
The user method may be set by default or may be configured by the programmer. By default, the user method parameter will be set to the Oracle user/schema for the current session. If the programmer wishes to define a user other than the Oracle user, the programmer can send the appropriate value to the set_user procedure. This procedure is useful if an application is configured to log into a specific Oracle schema and the user validation is handled by the application. Once the user method parameter is set, the logging level defined for the parameter value 'SCOTT' will be used for the user method.
ora_logger.set_user(‘SCOTT’);
The package and procedure methods are configured by the programmer. In order to set the method parameter, the programmer calls the set_package and/or set_procedure procedures. When the methods are called with the parameter values, the logging levels defined for those values are used for the appropriate method.
ora_logger.set_package(‘ORDERS’); ora_logger.set_procedure(‘PLACE_ORDER’);
The process method is configured by the programmer. In order to set the process method parameter, the programmer calls the set_process procedure. When the method is called with the parameter value, the logging level defined for that value is used for the process method. The process method is intended to define a series of steps which make a transaction or entry process.
ora_logger.set_process(‘ORDER_ENTRY’);
The logging level is always the greatest logging level of all possible logging levels. The logging levels include Global, User, Process, Package, Procedure, and Session. If the logging level for the Package method is the greatest, than that level is used. If the logging level for the User method is the greatest, than that level is used. This allows logging to be turned on for a specific user, package, procedure, session, process or for the entire database.