PL/SQL Logging

Logger

OraOpenSOurce/Logger
This project is well documented. This page acts as a crib sheet for some of the most common features.

Status

exec schema.logger.status

Project Home Page        : https://logger.samplecode.oracle.com/Logger Version           : 1.3.0Debug Level              : ERRORCapture Call Stack       : TRUEProtect Admin Procedures : TRUEAPEX Tracing             : EnabledSCN Capture              : DisabledMin. Purge Level         : WARNINGPurge Older Than         : 14 days

Set Logging Level

It is recommended to use the numeric options rather than the string options

16

8

4

2

1

0

exec schema.logger.set_level(logger.g_debug);

exec schema.logger.set_level(logger.g_information);

exec schema.logger.set_level(logger.g_warning);

exec schema.logger.set_level(logger.g_error);

exec schema.logger.set_level(logger.g_permanent);

exec schema.logger.set_level(logger.g_off);

exec schema.logger.set_level(logger.g_debug_name);

exec schema.logger.set_level(logger.g_information_name);

exec schema.logger.set_level(logger.g_warning_name);

exec schema.logger.set_level(logger.g_error_name);

exec schema.logger.set_level(logger.g_permanent);

exec schema.logger.set_level(logger.g_off);

16

8

4

2

1

0

exec schema.logger.set_level(16);

exec schema.logger.set_level(8);

exec schema.logger.set_level(4);

exec schema.logger.set_level(2);

exec schema.logger.set_level(1);

exec schema.logger.set_level(0);

exec schema.logger.set_level('DEBUG');

exec schema.logger.set_level('INFORMATION');

exec schema.logger.set_level('WARNING');

exec schema.logger.set_level('ERROR');

exec schema.logger.set_level('PERMANENT');

exec schema.logger.set_level('OFF');

Logging

Note that these log entries only end up in the logger_logs table if the Logging Level is set appropriately.

BEGIN

  logger.log('This is a debug message. (level 16 = DEBUG)');

  logger.log_information('This is an informational message. (level 8 = INFORMATION)');

  logger.log_info('This is an informational message. (level 8 = INFORMATION)');

  logger.log_warning('This is a warning message. (level 4 = WARNING)');

  logger.log_warn('This is a warning message. (level 4 = WARNING)');

  logger.log_error('This is an error message (level 2 = ERROR)');

  logger.log_permanent('This is a permanent message, good for upgrades and milestones. (level 1 = PERMANENT)');

END;

/

Retrieval

SELECT id, logger_level, text

  FROM logger_logs

 ORDER BY id;

SELECT id, logger_level, text, module, action, client_identifier

  FROM logger_logs

 ORDER BY id;

SELECT id, logger_level, text

  FROM logger_logs_5_min

 ORDER BY id;

Bibliography