Skip to content
Martin D'Souza edited this page Sep 9, 2013 · 8 revisions

Constants
Types
Subprograms ##Constants

g_logger_version Version of Logger.
g_context_name Context Logger uses for storing attributes.
gc_empty_tab_param Empty param used for default value in logger main procedures.
g_permanent Logger level permanent (1).
g_error Logger level error (2).
g_warning Logger level warning (4).
g_information Logger level information (8).
g_debug Logger level debug (16).
g_timing Logger level timing (32).
g_sys_context Logger level sys context (64). This is applicable for logging system variables.
g_apex Logger level apex (128).

##Types

rec_param Consists of:
name (varchar2)
value (varchar2)
tab_param Array of rec_param

##Subprograms

###Main Logger Procedures Since the main Logger procedures all have the same syntax and behavior (except for the procedure names) the documentation has been combined to avoid replication.

####Syntax The syntax for the main Logger procedures are all the same.

logger.procedure_name(
  p_text    in varchar2,
  p_scope   in varchar2 default null,
  p_extra   in clob default null,
  p_params  in tab_param default logger.gc_empty_tab_param);

####Parameters All of the main Logger procedures have the same parameters

Attribute Description
p_text p_text maps to the TEXT column in LOGGER_LOGS. It can handle up to 32767 characters. If p_text exceeds 4000 characters its content will be moved appended to the EXTRA column. If you need to store large blocks of text (i.e. clobs) you can use the p_extra parameter.
p_scope p_scope is optional but highly recommend. The idea behind scope is to give some context to the log message, such as the application, package.procedure where it was called from. Logger captures the call stack, as well as module and action which are great for APEX logging as they are app number / page number. However, none of these options gives you a clean, consistent way to group messages. The p_scope parameter performs a lower() on the input and stores it in the SCOPE column.
p_extra When logging large (over 4000 characters) blocks of text, use the third parameter: p_extra. p_extra is a clob field and thus isn't restricted to the 4000 character limit.
p_params p_params is for storing the parameters object. The goal of this parameter is to allow for a simple and consistent method to log the parameters to a given function. The values are explicitly converted to a string so there is no need to convert them when appending a parameter.

The data from the parameters array will be appended to the EXTRA column.

Since most production instances set the logging level to error, it is highly recommended that you leverage this 4th parameter when calling logger.log_error so that developers know the input that triggered the error.

####Examples The following code snippet highlights the main Logger procedures. Since they all have the same parameters, this will serve as the general example for all the main Logger procedures.

begin
  logger.log('This is a debug message. (level = DEBUG)');
  logger.log_information('This is an informational message. (level = INFORMATION)');
  logger.log_warning('This is a warning message. (level = WARNING)');
  logger.log_error('This is an error message (level = ERROR)');
  logger.log_permanent('This is a permanent message, good for upgrades and milestones. (level = PERMANENT)');
end;
/

select id, logger_level, text
from logger_logs_5_min
order by id;

  ID LOGGER_LEVEL TEXT
---- ------------ ------------------------------------------------------------------------------------------
  10	       16 This is a debug message. (level = DEBUG)
  11		8 This is an informational message. (level = INFORMATION)
  12	4 This is a warning message. (level = WARNING)
  13		2 This is an error message (level = ERROR)
  14		1 This is a permanent message, good for upgrades and milestones. (level = PERMANENT)

The following example shows how to use the p_params parameter. The parameter values are stored in the EXTRA column.

create or replace procedure p_demo_procedure(
  p_empno in emp.empno%type,
  p_ename in emp.ename%type)
as
  l_scope logger_logs.scope%type := 'p_demo_function';
  l_params logger.tab_param;
begin
  logger.append_param(l_params, 'p_empno', p_empno); -- Parameter name and value just stored in PL/SQL array and not logged yet
  logger.append_param(l_params, 'p_ename', p_ename); -- Parameter name and value just stored in PL/SQL array and not logged yet
  logger.log('START', l_scope, null, l_params); -- All parameters are logged at this point	
  -- ...
exception
  when others then
    logger.log_error('Unhandled Exception', l_scope, null, l_params);
end p_demo_procedure;
/

####LOG Procedure This procedure will log an entry into the LOGGER_LOGS table when the logger_level is set to debug. See Main Logger Procedures for syntax, parameters, and examples.

####LOG_INFORMATION Procedure This procedure will log an entry into the LOGGER_LOGS table when the logger_level is set to information. See Main Logger Procedures for syntax, parameters, and examples.

####LOG_WARNING Procedure This procedure will log an entry into the LOGGER_LOGS table when the logger_level is set to warning. See Main Logger Procedures for syntax, parameters, and examples.

####LOG_ERROR Procedure This procedure will log an entry into the LOGGER_LOGS table when the logger_level is set to error. See Main Logger Procedures for syntax, parameters, and examples.

####LOG_PERMANENT Procedure This procedure will log an entry into the LOGGER_LOGS table when the logger_level is set to permanent. See Main Logger Procedures for syntax, parameters, and examples.

###Other Logger Procedures

####LOG_USERENV

There are many occasions when the value of one of the USERENV session variables (Documentation: Overview, list of variables) is a big step in the right direction of finding a problem. A simple call to the logger.log_userenv procedure is all it takes to save them in the EXTRA column of logger_logs.

log-userenv will be logged using the g_sys_context level.

#####Syntax

logger.log_userenv(
  p_detail_level  in varchar2 default 'USER',-- ALL, NLS, USER, INSTANCE
  p_show_null     in boolean  default false,
  p_scope         in varchar2 default null)

#####Parameters

Attribute Description
p_detail_level Valid values are: ALL, NLS, USER (default), or INSTANCE
p_show_null If true, then variables that have no value will still be displayed.
p_scope Scope to log variables under.

#####Example

exec logger.log_userenv('NLS');
 
select text,extra from logger_logs_5_min;

TEXT                                           EXTRA
---------------------------------------------- -----------------------------------------------------------------
USERENV values stored in the EXTRA column      NLS_CALENDAR                  : GREGORIAN
                                               NLS_CURRENCY                  : $
                                               NLS_DATE_FORMAT               : DD-MON-RR
                                               NLS_DATE_LANGUAGE             : AMERICAN
                                               NLS_SORT                      : BINARY
                                               NLS_TERRITORY                 : AMERICA
                                               LANG                          : US
                                               LANGUAGE                      : AMERICAN_AMERICA.WE8MSWIN1252
exec logger.log_userenv('USER');

select text,extra from logger_logs_5_min;
TEXT                                               EXTRA
-------------------------------------------------- -------------------------------------------------------
USERENV values stored in the EXTRA column          CURRENT_SCHEMA                : LOGGER
                                                   SESSION_USER                  : LOGGER
                                                   OS_USER                       : tmuth
                                                   IP_ADDRESS                    : 192.168.1.7
                                                   HOST                          : WORKGROUP\TMUTH-LAP
                                                   TERMINAL                      : TMUTH-LAP
                                                   AUTHENTICATED_IDENTITY        : logger
                                                   AUTHENTICATION_METHOD         : PASSWORD

####LOG_CGI_ENV This option only works within a web session, but it's a great way to quickly take a look at an APEX environment.

#####Syntax

logger.log_cgi_env(
  p_show_null in boolean default false,
  p_scope in varchar2 default null);

#####Parameters

Attribute Description
p_show_null If true, then variables that have no value will still be displayed.
p_scope Scope to log CGI variables under.

#####Example

exec logger.log_cgi_env;
 
select extra from logger_logs where text like '%CGI%';
TEXT                                               EXTRA
-------------------------------------------------- -------------------------------------------------------
 ...
SERVER_SOFTWARE               : Oracle-Application-Server-10g/10.1.3.1.0 Oracle-HTTP-Server
GATEWAY_INTERFACE             : CGI/1.1
SERVER_PORT                   : 80
SERVER_NAME                   : 11g
REQUEST_METHOD                : POST
PATH_INFO                     : /wwv_flow.show
SCRIPT_NAME                   : /pls/apex
REMOTE_ADDR                   : 192.168.1.7    
...

####LOG_CHARACTER_CODES Have you ever run into an issue with a string that contains control characters such as carriage returns, line feeds and tabs that are difficult to debug? The sql dump() function is great for this, but the output is a bit hard to read as it outputs the character codes for each character, so you end up comparing the character code to an ascii table to figure out what it is. The function get_character_codes and the procedure log_character_codes make it much easier as they line up the characters in the original string under the corresponding character codes from dump. Additionally, all tabs are replaced with "^" and all other control characters such as carriage returns and line feeds are replaced with "~".

#####Syntax

logger.log_character_codes(
  p_text          in varchar2,
  p_scope         in varchar2 default null,
  p_show_common_codes   in boolean default true);

#####Parameters

Attribute Description
p_text Text to retrieve character codes for.
p_scope Scope to log text under.
p_show_common_codes Provides legend of common character codes in output.

#####Example

exec logger.log_character_codes('Hello World'||chr(9)||'Foo'||chr(13)||chr(10)||'Bar');
 
select extra from logger_logs_5_min;
 
EXTRA
----------------------------------------------------------------------------------
Common Codes: 13=Line Feed, 10=Carriage Return, 32=Space, 9=Tab
  72,101,108,108,111, 32, 87,111,114,108,100,  9, 70,111,111, 13, 10, 66, 97,114
   H,  e,  l,  l,  o,   ,  W,  o,  r,  l,  d,  ^,  F,  o,  o,  ~,  ~,  B,  a,  r

####LOG_APEX_ITEMS This feature is useful in debugging issues in an APEX application that are related session state. The developers toolbar in APEX provides a place to view session state, but it won't tell you the value of items midway through page rendering or right before and after an AJAX call to an application process.

#####Syntax

logger.log_apex_items(
  p_text    in varchar2 default 'Log APEX Items',
  p_scope   in varchar2 default null);

#####Parameters

Attribute Description
p_text Text to be added to TEXT column.
p_scope Scope to log text under.

#####Example TODO required configuration

-- in an on-submit page process
begin
  logger.log_apex_items('Debug Edit Customer');
end;
select id,logger_level,text,module,action,client_identifier 
from logger_logs 
where logger_level = 128;
 
 ID     LOGGER_LEVEL TEXT                 MODULE                 ACTION    CLIENT_IDENTIFIER
------- ------------ -------------------- ---------------------- --------- --------------------
     47          128 Debug Edit Customer  APEX:APPLICATION 100   PAGE 7    ADMIN:45588554040361
      
select * 
from logger_logs_apex_items 
where log_id = 47; --log_id relates to logger_logs.id
 
ID      LOG_ID  APP_SESSION    ITEM_NAME                 ITEM_VALUE
------- ------- ---------------- ------------------------- ---------------------------------------------
    136      47   45588554040361 P1_QUOTA
    137      47   45588554040361 P1_TOTAL_SALES
    138      47   45588554040361 P6_PRODUCT_NAME           3.2 GHz Desktop PC
    139      47   45588554040361 P6_PRODUCT_DESCRIPTION    All the options, this machine is loaded!
    140      47   45588554040361 P6_CATEGORY               Computer
    141      47   45588554040361 P6_PRODUCT_AVAIL          Y
    142      47   45588554040361 P6_LIST_PRICE             1200
    143      47   45588554040361 P6_PRODUCT_IMAGE
    144      47   45588554040361 P4_CALENDAR_DATE          20091103
    145      47   45588554040361 P7_CUSTOMER_ID            6
    146      47   45588554040361 P7_BRANCH                 2
    147      47   45588554040361 P29_ORDER_ID_NEXT
    148      47   45588554040361 P29_ORDER_ID_PREV
    149      47   45588554040361 P29_ORDER_ID_COUNT        0 of 0
    150      47   45588554040361 P7_CUST_FIRST_NAME        Albert
    151      47   45588554040361 P7_CUST_LAST_NAME         Lambert
    152      47   45588554040361 P7_CUST_STREET_ADDRESS1   10701 Lambert International Blvd.
    153      47   45588554040361 P7_CUST_STREET_ADDRESS2
    154      47   45588554040361 P7_CUST_CITY              St. Louis
    155      47   45588554040361 P7_CUST_STATE             MO
    156      47   45588554040361 P7_CUST_POSTAL_CODE       63145
    157      47   45588554040361 P7_CUST_EMAIL
    158      47   45588554040361 P7_PHONE_NUMBER1          314-555-4022
    159      47   45588554040361 P7_PHONE_NUMBER2
    160      47   45588554040361 P7_CREDIT_LIMIT           1000
    161      47   45588554040361 P6_PRODUCT_ID             1
    162      47   45588554040361 P29_ORDER_ID              9

###Utility Functions

####GET_CGI_ENV TODO Description

#####Syntax

logger.get_cgi_env(
  p_show_null   in boolean default false)
  return clob;

#####Parameters

Attribute Description
p_show_null Show null variables.
return Formatted list of CGI variables

#####Example

TODO

####GET_PREF Returns the preference from LOGGER_PREFS.

#####Syntax

logger.function get_pref(
  p_pref_name     in  varchar2)
  return varchar2

#####Parameters

Attribute Description
p_pref_name Preference to get value for.
return Prefence value.

#####Example

TODO

####PURGE TODO_DESC

#####Syntax

logger.purge(
  p_purge_after_days  in varchar2 default null,
  p_purge_min_level in varchar2 default null);

#####Parameters

Attribute Description
p_purge_after_days Purge entries older than n days.
p_purge_min_level Minimum level to purge entries. For example if set to *logger.g\_information* then information, debug, timing, sys_context, and apex logs will be deleted.

#####Example

TODO

####PURGE_ALL Purges all non-permanent entries in LOGGER_LOGS.

#####Syntax

logger.purge_all;

#####Parameters No Parameters

#####Example

TODO
-- For this one show a count before of logger_logs. Then run, then show what's left in the table.

####STATUS Prints the Logger's current status and configuration settings.

#####Syntax

logger.status(
  p_output_format in varchar2 default null); -- SQL-DEVELOPER | HTML | DBMS_OUPUT

#####Parameters

Attribute Description
p_output_format What type of output. Accepted values are SQL-DEVELOPER, HTML, and DBMS_OUPUT.

#####Example

set serveroutput on
exec logger.status

Project Home Page    : https://github.com/tmuth/Logger---A-PL-SQL-Logging-Utility/
Logger Version       : 2.0.0.a01
Debug Level          : DEBUG
Capture Call Stack     : TRUE
Protect Admin Procedures : TRUE
APEX Tracing       : Disabled
SCN Capture        : Disabled
Min. Purge Level     : DEBUG
Purge Older Than     : 7 days
Pref by client_id expire : 12 hours
For all client info see  : logger_prefs_by_client_id

PL/SQL procedure successfully completed.

####SQLPLUS_FORMAT TODO_DESC

#####Syntax

logger.sqlplus_format;

#####Parameters No Parameters

#####Example

TODO

####NULL_GLOBAL_CONTEXTS TODO_DESC

#####Syntax

logger.null_global_contexts;

#####Parameters No Parameters.

#####Example

TODO

####CONVERT_LEVEL_CHAR_TO_NUM Returns the number representing the given level (string).

#####Syntax

logger.convert_level_char_to_num(
  p_level in varchar2)
  return number;

#####Parameters

Attribute Description
p_level Level name.
return Level number

#####Example

select logger.convert_level_char_to_num(p_level => 'DEBUG') level_number
from dual;

LEVEL_NUMBER
------------
    16

####DATE_TEXT_FORMAT Returns the time difference (in nicely formatted string) of p_date compared to now (sysdate).

#####Syntax

logger.date_text_format (p_date in date)
  return varchar2;

#####Parameters

Attribute Description
p_date Date to compare
return Time difference between p_date and now.

#####Example

select logger.date_text_format(sysdate-1) date_diff
from dual;

DATE_DIFF
-----------
1 days ago

####GET_CHARACTER_CODES Similar to log_character_codes except will return the character codes instead of logging them.

#####Syntax

logger.get_character_codes(
  p_string        in varchar2,
  p_show_common_codes   in boolean default true)
  return varchar2;

#####Parameters

Attribute Description
p_string String to get codes for.
p_show_common_codes Display legend of common character codes.
return String with character codes.

#####Example

select logger.get_character_codes('Hello World') char_codes
from dual;

CHAR_CODES
--------------------------------------------------------------------------------
Common Codes: 13=Line Feed, 10=Carriage Return, 32=Space, 9=Tab
  72,101,108,108,111, 32, 87,111,114,108,100
   H,  e,  l,  l,  o, ,  W,  o,  r,  l,  d

###Set Logging Level

Logger allows you to configure both system logging levels and client specific logging levels. If a client specific logging level is defined, it will override the system level configuration. If no client level is defined Logger will defautl to the system level configuration.

Prior to version 2.0.0 Logger only supported one logger level. The primary goal of this approach was to enable Logger at Debug level for development environments, then change it to Error levels in production environments so the logs did not slow down the system. Over time developers start to find that in some situations they needed to see what a particular user / session was doing in production. Their only option was to enable Logger for the entire system which could potentially slow everyone down.

Starting in version 2.0.0 you can now specify the logger level along with call stack setting by specifying the client_identifier. If not explicitly unset, client specific configurations will expire after a set period of time.

The following query shows all the current client specific log configurations:

select *
from logger_prefs_by_client_id;

CLIENT_ID     LOGGER_LEVEL  INCLUDE_CALL_STACK CREATED_DATE   EXPIRY_DATE
------------------- ------------- ------------------ -------------------- --------------------
logger_demo_session ERROR   TRUE         24-APR-2013 02:48:13 24-APR-2013 14:48:13

####SET_LEVEL Set both system and client logging levels.

#####Syntax

logger.set_level(
  p_level in varchar2 default 'DEBUG',
  p_client_id in varchar2 default null,
  p_include_call_stack in varchar2 default null,
  p_client_id_expire_hours in number default null
);

#####Parameters

Attribute Description
p_level Valid values: OFF,PERMANENT,ERROR,WARNING,INFORMATION,DEBUG,TIMING.
p_client_id Optional: If defined, will set the level for the given client identifier. If null will affect global settings.
p_include_call_stack Optional: Only valid if p_client_id is defined Valid values: TRUE, FALSE. If not set will use the default system pref in logger_prefs.
p_client_id_expire_hours If p_client_id, expire after number of hours. If not defined, will default to system preference PREF_BY_CLIENT_ID_EXPIRE_HOURS.

#####Example Set system level logging level:

exec logger.set_level('DEBUG');

Client Specific Configuration:

-- In Oracle Session-1
exec logger.set_level('DEBUG');

exec logger.log('Session-1: this should show up');

select id, logger_level, text, client_identifier, call_stack
from logger_logs_5_min
order by id;

  ID LOGGER_LEVEL TEXT                      CLIENT_IDENTIFIER CALL_STACK
---- ------------ ----------------------------------- ----------------- ----------------------------
  31         16 Session-1: this should show up              object      line  object

exec logger.set_level ('ERROR');

exec logger.log('Session-1: this should NOT show up');

-- The previous line does not get logged since the logger level is set to ERROR and it made a .log call


-- In Oracle Session-2 (i.e. a different session)
exec dbms_session.set_identifier('my_identifier');

-- This sets the logger level for current identifier
exec logger.set_level('DEBUG', sys_context('userenv','client_identifier'));

exec logger.log('Session-2: this should show up');

select id, logger_level, text, client_identifier, call_stack
from logger_logs_5_min
order by id;

  ID LOGGER_LEVEL TEXT                      CLIENT_IDENTIFIER CALL_STACK
---- ------------ ----------------------------------- ----------------- ----------------------------
  31         16 Session-1: this should show up                  object      line  object
  32         16 Session-2: this should show up    my_identifier   object      line  object
  
-- Notice how the CLIENT_IDENTIFIER field also contains the current client_identifer

In APEX the client_identifier is

:APP_USER || ':' || :APP_SESSION 

####UNSET_CLIENT_LEVEL Unset logger level by specific client_id.

#####Syntax

logger.unset_client_level(p_client_id in varchar2);

#####Parameters

Attribute Description
p_client_id Client identifier to unset logging level.

#####Example

exec logger.unset_client_level('my_client_id');

####UNSET_CLIENT_LEVEL Unset all expired client_ids. Note this run automatically each hour by the LOGGER_UNSET_PREFS_BY_CLIENT job.

#####Syntax

logger.unset_client_level;

#####Parameters No parameters.

#####Example

exec logger.unset_client_level;

####UNSET_CLIENT_LEVEL_ALL Unset all client configurations (regardless of expiry time).

#####Syntax

logger.unset_client_level_all;

#####Parameters No Parameters.

#####Example

exec logger.unset_client_level_all;

###Timing Procedures

TODO description?

####Example Since all the timing procedures are tightly coupled, the following example will be used to cover all of them:

declare
    l_number number;
begin
    logger.time_reset;
    logger.time_start('foo');
    logger.time_start('bar');
    for i in 1..500000 loop
        l_number := power(i,15);
        l_number := sqrt(1333);
    end loop; --i
    logger.time_stop('bar');
    for i in 1..500000 loop
        l_number := power(i,15);
        l_number := sqrt(1333);
    end loop; --i
    logger.time_stop('foo');
end;
/
 
select text from logger_logs_5_min;
 
TEXT
---------------------------------
START: foo
>  START: bar
>  STOP : bar - 1.000843 seconds
STOP : foo - 2.015953 seconds

####TIME_RESET Resets all timers.

#####Syntax

logger.time_reset;

#####Parameters No Parameters.

#####Example

logger.time_reset;

####TIME_START Starts a timer.

#####Syntax

logger.time_start(
  p_unit        IN VARCHAR2,
  p_log_in_table      IN boolean default true)

#####Parameters

Attribute Description
p_unit Name for timing unit
p_log_in_table If true, will log the start event in LOGGER_LOGS.

#####Example See Timing Procedures Example.

####TIME_STOP Stops a timing event and logs in LOGGER_LOGS using level = logger.g_timing.

#####Syntax

logger.time_stop(
  p_unit in varchar2,
  p_scope in varchar2 default null);

#####Parameters

Attribute Description
p_unit Timer to stop.
p_scope Scope to log timer under.

#####Example See Timing Procedures Example.

####TIME_STOP Similar to TIME_STOP procedure, this function will stop a timer. Logging into LOGGER_LOGS is configurable. Returns the stop time string.

#####Syntax

logger.time_stop(
  p_unit in varchar2,
  p_scope in varchar2 default null,
  p_log_in_table in boolean default true)
  return varchar2;

#####Parameters

Attribute Description
p_unit Timer to stop.
p_scope Scope to log timer under.
p_log_in_table Store result in LOGGER_LOGS.
return Timer results.

#####Example

TODO

####TIME_STOP_SECONDS TODO_DESC

#####Syntax

logger.time_stop_seconds(
  p_unit        in varchar2,
  p_scope             in varchar2 default null,
  p_log_in_table      in boolean default true)
  return number;

#####Parameters

Attribute Description
p_unit Timer to stop.
p_scope Scope to log timer under.
p_log_in_table Store result in LOGGER_LOGS.

#####Example

TODO

####APPEND_PARAM Logger has wrapper functions to quickly and easily log parameters. All primary log procedures take in a fourth parameter to support logging a parameter array. The values are explicitly converted to strings so you don't need to convert them. The parameter values will be stored n the extra column.

#####Syntax

logger.append_param(
  p_params in out nocopy logger.tab_param,
  p_name in varchar2,
  p_val in <various_data_types>);

#####Parameters

Attribute Description
p_params Param array to append parameter value to.
p_name Name of the parameter.
p_val Value (in original data type).

#####Example

create or replace procedure p_demo_function(
  p_empno in emp.empno%type,
  p_ename in emp.ename%type)
as
  l_scope logger_logs.scope%type := 'p_demo_function';
  l_params logger.tab_param;
begin
  logger.append_param(l_params, 'p_empno', p_empno); -- Parameter name and value just stored in PL/SQL array and not logged yet
  logger.append_param(l_params, 'p_ename', p_ename); -- Parameter name and value just stored in PL/SQL array and not logged yet
  logger.log('START', l_scope, null, l_params); -- All parameters are logged at this point  
  -- ...
exception
  when others then
    logger.log_error('Unhandled Exception', l_scope, null, l_params);
end p_demo_function;
/

####OK_TO_LOG Though Logger internally handles when a statement is stored in the LOGGER_LOGS table there may be situations where you need to know if logger will log a statement before calling logger. This is useful when doing an expensive operation just to log the data.

A classic example is looping over an array for the sole purpose of logging the data. In this case, there's no reason why the code should perform the additional computations when logging is disabled for a certain level.

ok_to_log will also factor in client specific logging settings.

Note: ok_to_log is not something that should be used frequently. All calls to logger run this command internally.

#####Syntax

logger.ok_to_log(p_level  in  varchar2)
  return boolean;

#####Parameters

Attribute Description
p_level Level (name) to test for.
return Wether or not level will be logged.

#####Example

declare
  type typ_array is table of number index by pls_integer;
  l_array typ_array;
begin
  -- Load test data
  for x in 1..100 loop
    l_array(x) := x;
  end loop;

  -- Only log if logging is enabled
  if logger.ok_to_log('DEBUG') then
    for x in 1..l_array.count loop
      logger.log(l_array(x));
    end loop;
  end if;
end;
/ 

Note: ok_to_log should not be used for one-off log commands. This defeats the whole purpose of having the various log commands. For example ok_to_log should not be used in the following way:

-- Reminder: This is an example of how not to use ok_to_log
...
if logger.ok_to_log(logger.g_debug) then
 logger.log('test');
end if;
...

####INS_LOGGER_LOGS As part of the 2.1.0 release, the trigger on LOGGER_LOGS was removed for both performance and other issues. Though inserting directly to the LOGGER_LOGS table is not a supported feature of Logger, you may have some code that does a direct insert. The primary reason that a manual insert into LOGGER_LOGS was done was to obtain the ID column for the log entry.

To help prevent any issues with backwards compatibility, ins_logger_logs has been made publicly accessible to handle any inserts into LOGGER_LOGS. This is a supported procedure and any manual insert statements will need to be modified to use this procedure instead.

It's important to note that ins_logger_logs does not check the Logger level. This means it will always insert into the LOGGER_LOGS table. It is also an Autonomous Transaction procedure so a commit is always performed, however it will not affect the current session.

Similar to ok_to_log, this procedure should be used very infrequently as the main Logger procedures should handle everything that is required for quickly logging information.

#####Syntax

logger.ins_logger_logs(
  p_logger_level in logger_logs.logger_level%type,
  p_text in varchar2 default null,
  p_scope in logger_logs.scope%type default null,
  p_call_stack in logger_logs.call_stack%type default null,
  p_unit_name in logger_logs.unit_name%type default null,
  p_line_no in logger_logs.line_no%type default null, 
  p_extra in logger_logs.extra%type default null,
  po_id out nocopy logger_logs.id%type);

#####Parameters

Attribute Description
p_logger_level Logger level. See Constants section for list of variables to chose from.
p_text Text column.
p_scope Scope.
p_call_stack PL/SQL call stack.
p_unit_name Unit name (this is usually the calling procedure).
p_line_no Line number
p_extra Extra CLOB.
po_id Logger ID (out).

#####Example

set serveroutput on

declare
  l_id logger_logs.id%type;
begin
  -- Note: Commented out parameters not used for this demo (but still accessible via API)
  logger.ins_logger_logs(
    p_logger_level => logger.g_debug,
    p_text => 'Custom Insert',
    p_scope => 'demo.logger.custom_insert',
--    p_call_stack => ''
    p_unit_name => 'Dynamic PL/SQL',
--    p_line_no => ,
--    p_extra => ,
    po_id => l_id
  );
  
  dbms_output.put_line('ID: ' || l_id);
end;
/

ID: 2930650
Clone this wiki locally