Logging is the process that handles all error related information.
Logging is a balancing act, with clarity vs. completeness and simplicity vs. disk-space as mayor competitors.
- Clarity for feedback
- Completeness for analysis
- Simplicity to make sure that the decision to log or not does eat more performance than the action being logged
- Disk space wasted by irrelevant log records
Currently the best logging package is Logger (OraOpenSource).
For packages the recommended practice is as follows:
create or replace package body pkg_example
as
gc_scope_prefix constant varchar2(31) := lower($$plsql_unit) || '.';
/**
* TODO_Comments
*
* Notes:
* -
*
* Related Tickets:
* -
*
* @author TODO
* @created TODO
* @param TODO
* @return TODO
*/
procedure todo_proc_name(
p_param1_todo in varchar2)
as
l_scope logger_logs.scope%type := gc_scope_prefix || 'todo_proc_name';
l_params logger.tab_param;
begin
logger.append_param(l_params, 'p_param1_todo', p_param1_todo);
logger.log('START', l_scope, null, l_params);
...
-- All calls to logger should pass in the scope
...
logger.log('END', l_scope);
exception
when others then
logger.log_error('Unhandled Exception', l_scope, null, l_params);
raise;
end todo_proc_name;
...
end pkg_example;Logger supports multiple logging levels. This section will provide an outline of recommended situations for calling each level. The procedures are ordered in most frequently used to least frequently used.
| Level | Actionable | Target Audience | ~%Reference |
|---|---|---|---|
| Debug | No | Developers | 90% |
| Information | No | Developers/Business | 1% |
| Warning | Yes | Developers/Business | 1% |
| Error | Yes | Developers/IT/DBA | 5% |
| Permanent | No | Developers/Business | 0.5% |
Actionable means issues that require follow up by a business unit.
logger.log should be used for all developer related content. This can really be anything and everything except for items that require additional investigation. In those situations use the other logging options.
By default, Logger is configured to delete all debug level calls after 7 days. As such, developers are encouraged to log as much as they need to with this option. Using other logging levels may result (depending on the settings) in permanent storage and should not be used as frequently.
logger.log_info[rmation] should be used for messages that need to be retained at a higher level than debug but are not actionable issues.
Information logging will vary in each organization but should fall between the rules for debug and warning. An example is to use it for a long running process to highlight some of the following items:
- When did the process start
- Major steps/milestones in the process
- Number of rows processed
- When did the process end
logger.log_warn[ing] should be used for non-critical system level / business logic issues that are actionable. If it is a critical issue than an error should be raised and logger.log_error should be called. An example would be when a non-critical configuration item is missing. In this case a warning message should be logged stating that the configuration option was not set / mssing and the deafult value that the code is using in place.
logger.log_error should be used when a PL/SQL error has occurred. In most cases this is in an exception block. Regardless of any other configuration, log_error will store the callstack. Errors are considered actionalble items as an error has occurred and something (code, configuration, server down, etc) needs attention.
logger.log_permanent should be used for messages that need to be permanently retained. logger.purge and logger.purge_all will not delete these messages regardless of the PURGE_MIN_LEVEL configuration option. Only an implicit delete to logger_logs will delete these messages.
An example would be to use this procedure when updating your application to a new version. At the end of the update you can log that the upgrade was successful and the new version number. This way you can find exactly when all the upgrades occurred on your system.