8 APEX_DEBUG

The APEX_DEBUG package provides utility functions for managing the debug message log. Specifically, this package provides the necessary APIs to instrument and debug PL/SQL code contained within your Application Express application as well as PL/SQL code in database stored procedures and functions. Instrumenting your PL/SQL code makes it much easier to track down bugs and isolate unexpected behavior more quickly.

The package also provides the means to enable and disable debugging at different debug levels and utility procedures to clean up the message log.

You can view the message log either as described in the "Accessing Debugging Mode" section of the Oracle Application Express Application Builder User's Guide or by querying the APEX_DEBUG_MESSAGES view.

For further information, see the individual API descriptions.

Note:

In Oracle Application Express 4.2, the APEX_DEBUG_MESSAGE package was renamed to APEX_DEBUG. The APEX_DEBUG_MESSAGE package name is still supported to provide backward compatibility. As a best practice, however, use the new APEX_DEBUG package for new applications unless you plan to run them in an earlier version of Oracle Application Express.

Constants
DISABLE Procedure
ENABLE Procedure
ENTER Procedure
ERROR Procedure
INFO Procedure
LOG_DBMS_OUTPUT Procedure
LOG_LONG_MESSAGE Procedure
LOG_MESSAGE Procedure [Deprecated]
LOG_PAGE_SESSION_STATE Procedure
MESSAGE Procedure
REMOVE_DEBUG_BY_AGE Procedure
REMOVE_DEBUG_BY_APP Procedure
REMOVE_DEBUG_BY_VIEW Procedure
REMOVE_SESSION_MESSAGES Procedure
TOCHAR Function
TRACE Procedure
WARN Procedure

Constants

The following constants are used by this package.

subtype t_log_level is pls_integer; 
c_log_level_error constant t_log_level := 1; -- critical error 
c_log_level_warn constant t_log_level := 2; -- less critical error 
c_log_level_info constant t_log_level := 4; -- default level if debugging is enabled (for example, used by apex_application.debug) 
c_log_level_app_enter constant t_log_level := 5; -- application: messages when procedures/functions are entered 
c_log_level_app_trace constant t_log_level := 6; -- application: other messages within procedures/functions 
c_log_level_engine_enter constant t_log_level := 8; -- Application Express engine: messages when procedures/functions are entered 
c_log_level_engine_trace constant t_log_level := 9; -- Application Express engine: other messages within procedures/functions

DISABLE Procedure

This procedure turns off debug messaging.

Syntax

APEX_DEBUG.DISABLE;

Parameters

None.

Example

This example shows how you can turn off debug messaging.

BEGIN
    APEX_DEBUG.DISABLE();
END;

See Also:

"ENABLE Procedure"

ENABLE Procedure

This procedure turns on debug messaging. You can specify, by level of importance, the types of debug messages that are monitored.

Note:

You only need to call ENABLE procedure once per page view or page accept.

Syntax

APEX_DEBUG.ENABLE (
    p_level    IN  T_LOG_LEVEL DEFAULT C_LOG_LEVEL_INFO );

Parameters

Table 8-1 describes the parameters available in the APEX_DEBUG.ENABLE procedure.

Table 8-1 APEX_DEBUG.ENABLE Procedure Parameters

Parameter	Description
`p_level`	Level or levels of messages to log. Must be an integer from 1 to 9, where level 1 is the most important messages and level 9 (the default) is the least important. Setting to a specific level logs messages both at that level and below that level. For example, setting `p_level` to 2 logs any message at level 1 and 2.

Example

This examples shows how to enable logging of messages for levels 1, 2 and 4. Messages at higher levels are not logged.

BEGIN
    APEX_DEBUG.ENABLE(
        apex_debug.c_log_level_info);
END;

ENTER Procedure

This procedure logs messages at level c_log_level_app_enter. Use APEX_DEBUG.ENTER() to log the routine name and it's arguments at the beginning of a procedure or function.

Syntax

APEX_DEBUG.ENTER ( 
p_routine_name IN VARCHAR2, 
p_name01 IN VARCHAR2 DEFAULT NULL, 
p_value01 IN VARCHAR2 DEFAULT NULL, 
p_name02 IN VARCHAR2 DEFAULT NULL, 
p_value02 IN VARCHAR2 DEFAULT NULL, 
p_name03 IN VARCHAR2 DEFAULT NULL, 
p_value03 IN VARCHAR2 DEFAULT NULL, 
p_name04 IN VARCHAR2 DEFAULT NULL, 
p_value04 IN VARCHAR2 DEFAULT NULL, 
p_name05 IN VARCHAR2 DEFAULT NULL, 
p_value05 IN VARCHAR2 DEFAULT NULL, 
p_name06 IN VARCHAR2 DEFAULT NULL, 
p_value06 IN VARCHAR2 DEFAULT NULL, 
p_name07 IN VARCHAR2 DEFAULT NULL, 
p_value07 IN VARCHAR2 DEFAULT NULL, 
p_name08 IN VARCHAR2 DEFAULT NULL, 
p_value08 IN VARCHAR2 DEFAULT NULL, 
p_name09 IN VARCHAR2 DEFAULT NULL, 
p_value09 IN VARCHAR2 DEFAULT NULL, 
p_name10 IN VARCHAR2 DEFAULT NULL, 
p_value10 IN VARCHAR2 DEFAULT NULL, 
p_value_max_length IN PLS_INTEGER DEFAULT 1000 );

Parameters

Table 8-2 describes the parameters available for the APEX_DEBUG.ENTER procedure.

Table 8-2 APEX_DEBUG.ENTER Procedure Parameters

Parameter	Description
`p_routine_name`	The name of the procedure or function.
`p_namexx`/`p_valuexx`	The procedure or function parameter name and value.
`p_value_max_length`	The `p_valuexx` values is truncated to this length.

Example

This example shows how to use APEX_ENTER to add a debug message at the beginning of a procedure.

procedure foo ( 
    p_widget_id in number, 
    p_additional_data in varchar2, 
    p_emp_rec in emp%rowtype ) 
is 
begin 
    apex_debug.enter('foo', 
        'p_widget_id' , p_widget_id, 
        'p_additional_data', p_additional_data, 
        'p_emp_rec.id' , p_emp_rec.id ); 
....do something.... 
end foo;

ERROR Procedure

This procedure logs messages at level c_log_level_error. This procedure always logs, even if debug mode is turned off.

Syntax

APEX_DEBUG.ERROR ( 
    p_message IN VARCHAR2, 
    p0 IN VARCHAR2 DEFAULT NULL, 
    p1 IN VARCHAR2 DEFAULT NULL, 
    p2 IN VARCHAR2 DEFAULT NULL, 
    p3 IN VARCHAR2 DEFAULT NULL, 
    p4 IN VARCHAR2 DEFAULT NULL, 
    p5 IN VARCHAR2 DEFAULT NULL, 
    p6 IN VARCHAR2 DEFAULT NULL, 
    p7 IN VARCHAR2 DEFAULT NULL, 
    p8 IN VARCHAR2 DEFAULT NULL, 
    p9 IN VARCHAR2 DEFAULT NULL, 
    p_max_length IN PLS_INTEGER DEFAULT 1000 );

Parameters

Table 8-3 describes parameters available for the ERROR procedure.

Table 8-3 APEX_DEBUG.ERROR Procedure Parameters

Parameter	Description
`p_message`	The debug message. Occurrences of '%s' are replaced by `p0` to `p19`, as in `utl_lms.format_message` and C's sprintf. Occurrences of '%%' represent the special character '%'. Occurrences of '%<n>' are replaced by p<n>.
`p0` through `p9`	Substitution strings for '%s' placeholders.
`p_max_length`	The `p<n>` values are truncated to this length.

Example

This example shows how to use APEX_ERROR to log a critical error in the debug log.

apex_debug.error('Critical error %s', sqlerrm);

INFO Procedure

This procedure logs messages at level c_log_level_info.

Syntax

APEX_DEBUG.INFO ( 
    p_message IN VARCHAR2, 
    p0 IN VARCHAR2 DEFAULT NULL, 
    p1 IN VARCHAR2 DEFAULT NULL, 
    p2 IN VARCHAR2 DEFAULT NULL, 
    p3 IN VARCHAR2 DEFAULT NULL, 
    p4 IN VARCHAR2 DEFAULT NULL, 
    p5 IN VARCHAR2 DEFAULT NULL, 
    p6 IN VARCHAR2 DEFAULT NULL, 
    p7 IN VARCHAR2 DEFAULT NULL, 
    p8 IN VARCHAR2 DEFAULT NULL, 
    p9 IN VARCHAR2 DEFAULT NULL, 
    p_max_length IN PLS_INTEGER DEFAULT 1000 );

Parameters

Table 8-4 describes parameters available for the APEX_DEBUG.INFO procedure.

Table 8-4 APEX_DEBUG.INFO Procedure Parameters

Parameter	Description
`p_message`	The debug message. Occurrences of '%s' are replaced by `p0` to `p19`, as in `utl_lms.format_message` and C's sprintf. Occurrences of '%%' represent the special character '%'. Occurrences of '%<n>' are replaced by p<n>.
`p0` through `p9`	Substitution strings for '%s' placeholders.
`p_max_length`	The `p<n>` values are truncated to this length.

Example

This example shows how to use APEX_DEBUG.INFO to log information in the debug log.

apex_debug.info('Important: %s', 'fnord');

LOG_DBMS_OUTPUT Procedure

This procedure writes the contents of dbms_output.get_lines to the debug log. Messages of legacy applications which use dbms_output are copied into the debug log. In order to write to the debug log, dbms_output.enable must be performed

Syntax

APEX_DEBUG.LOG_DBMS_OUTPUT;

Parameters

None.

Example

This example shows how to log the contents of the DBMS_OUTPUT buffer in the debug log.

sys.dbms_output.enable;
sys.dbms_output.put_line('some data'); 
sys.dbms_output.put_line('other data'); 
apex_debug.log_dbms_output;

LOG_LONG_MESSAGE Procedure

Use this procedure to emit debug messages from PLSQL components of Application Express, or PLSQL procedures and functions. This procedure is the same as LOG_MESSAGE, except it allows logging of much longer messages, which are subsequently split into 4,000 character chunks in the debugging output (because a single debug message is constrained to 4,000 characters).

Note:

Instead of this procedure, use "ERROR Procedure," "WARN Procedure," "MESSAGE Procedure," "INFO Procedure," "ENTER Procedure," or "TRACE Procedure."

Syntax

APEX_DEBUG.LOG_LONG_MESSAGE (
    p_message    IN VARCHAR2  DEFAULT NULL,
    p_enabled    IN BOOLEAN   DEFAULT FALSE,
    p_level      IN T_LOG_LEVEL DEFAULT C_LOG_LEVEL_APP_TRACE);

Parameters

Table 8-5 describes parameters available for the APEX_DEBUG.LOG_LONG_MESSAGE procedure.

Table 8-5 APEX_DEBUG.LOG_LONG_MESSAGE Procedure Parameters

Parameter	Description
`p_message`	Log long message with maximum size of 32767 bytes.
`p_enabled`	Set to `TRUE` to always log messages, irrespective of whether debugging is enabled. Set to `FALSE` to only log messages if debugging is enabled.
p_level	Identifies the level of the long log message. See "Constants."

Example

This example shows how to enable debug message logging for 1 and 2 level messages and display a level 1 message that could contain anything up to 32767 characters. Note, the p_enabled parameter need not be specified, as debugging has been explicitly enabled and the default of FALSE for this parameter respects this enabling.

DECLARE
    l_msg VARCHAR2(32767) := 'Debug outputs anything up to varchar2 limit';
BEGIN
    APEX_DEBUG.ENABLE (p_level => 2);
    
    APEX_DEBUG.LOG_LONG_MESSAGE(
        p_message => l_msg,
        p_level => 1 );     
END;

LOG_MESSAGE Procedure [Deprecated]

This procedure logs a debug message.

Note:

Instead of this procedure, use "ERROR Procedure," "WARN Procedure," "MESSAGE Procedure," "INFO Procedure," "ENTER Procedure," or "TRACE Procedure."

Syntax

APEX_DEBUG.LOG_MESSAGE ( 
    p_message IN VARCHAR2 DEFAULT NULL, 
    p_enabled IN BOOLEAN DEFAULT FALSE, 
    p_level IN T_LOG_LEVEL DEFAULT C_LOG_LEVEL_APP_TRACE );

Parameters

Table 8-6 describes parameters available for the APEX_DEBUG.LOG_MESSAGE procedure.

Table 8-6 APEX_DEBUG.LOG_MESSAGE Procedure Parameters

Parameter	Description
`p_message`	The debug message with a maximum length of 1000 bytes.
`p_enabled`	Messages are logged when logging is enabled, setting a value of TRUE enables logging.
`p_level`	Identifies the level of the log message where 1 is most important and 9 is least important. This is an integer value.

Example

This example shows how to enable debug message logging for 1 and 2 level messages and display a level 1 message showing a variable value. Note, the p_enabled parameter need not be specified, as debugging has been explicitly enabled and the default of FALSE for this parameter respects this enabling.

DECLARE
    l_value varchar2(100) := 'test value';
BEGIN
    APEX_DEBUG.ENABLE (p_level => 2);
 
APEX_DEBUG.LOG_MESSAGE(
    p_message => 'l_value = ' || l_value,
    p_level => 1 );
 
END;

LOG_PAGE_SESSION_STATE Procedure

This procedure logs the session's item values.

Syntax

APEX_DEBUG.LOG_PAGE_SESSION_STATE ( 
    p_page_id IN NUMBER DEFAULT NULL, 
    p_enabled IN BOOLEAN DEFAULT FALSE, 
    p_level IN T_LOG_LEVEL DEFAULT C_LOG_LEVEL_APP_TRACE );

Parameters

Table 8-7 describes parameters available for the APEX_DEBUG.LOG_SESSION_STATE procedure.

Table 8-7 APEX_DEBUG.LOG_SESSION_STATE Procedure Parameters

Parameter	Description
`p_page_id`	Identifies a page within the current applicaton and workspace context.
`p_enabled`	Messages are logged when logging is enabled, setting a value of TRUE enables logging.
`p_level`	Identifies the level of the log message where 1 is most important, 9 is least important. Must be an integer value.

Example

This example shows how to enable debug message logging for 1 and 2 level messages and display a level 1 message containing all the session state for the application's current page. Note, the p_enabled parameter need not be specified, as debugging has been explicitly enabled and the default of FALSE for this parameter respects this enabling. Also note the p_page_id has not been specified, as this example just shows session state information for the application's current page.

BEGIN
    APEX_DEBUG.ENABLE (p_level => 2);
    
    APEX_DEBUG.LOG_PAGE_SESSION_STATE (p_level => 1);
    
END;

MESSAGE Procedure

This procedure logs a formatted debug message, general version.

Syntax

APEX_DEBUG.MESSAGE ( 
    p_message IN VARCHAR2, 
    p0 IN VARCHAR2 DEFAULT NULL, 
    p1 IN VARCHAR2 DEFAULT NULL, 
    p2 IN VARCHAR2 DEFAULT NULL, 
    p3 IN VARCHAR2 DEFAULT NULL, 
    p4 IN VARCHAR2 DEFAULT NULL, 
    p5 IN VARCHAR2 DEFAULT NULL, 
    p6 IN VARCHAR2 DEFAULT NULL, 
    p7 IN VARCHAR2 DEFAULT NULL, 
    p8 IN VARCHAR2 DEFAULT NULL, 
    p9 IN VARCHAR2 DEFAULT NULL, 
    p10 IN VARCHAR2 DEFAULT NULL, 
    p11 IN VARCHAR2 DEFAULT NULL, 
    p12 IN VARCHAR2 DEFAULT NULL, 
    p13 IN VARCHAR2 DEFAULT NULL, 
    p14 IN VARCHAR2 DEFAULT NULL, 
    p15 IN VARCHAR2 DEFAULT NULL, 
    p16 IN VARCHAR2 DEFAULT NULL, 
    p17 IN VARCHAR2 DEFAULT NULL, 
    p18 IN VARCHAR2 DEFAULT NULL, 
    p19 IN VARCHAR2 DEFAULT NULL, 
    p_max_length IN PLS_INTEGER DEFAULT 1000, 
    p_level IN T_LOG_LEVEL DEFAULT C_LOG_LEVEL_INFO, 
    p_force IN BOOLEAN DEFAULT FALSE );

Parameters

Table 8-8 describes parameters available for the APEX_DEBUG.MESSAGE procedure.

Table 8-8 APEX_DEBUG.MESSAGE Procedure Parameters

Parameter	Description
`p_message`	The debug message. Occurrences of '%s' is replaced by `p0` to `p19`, as in `utl_lms.format_message` and C's sprintf. Occurrences of '%%' represent the special character '%'. Occurrences of '%<n>' are replaced by `p<n>`.
`p0` through `p19`	Substitution strings for '%s' placeholders.
`p_max_length`	The `p<n>` values is truncated to this length.
`p_level`	The log level for the message, default is `c_log_level_info`. See "Constants."
`p_force`	If TRUE, this generates a debug message even if the page is not rendered in debug mode or `p_level` is greater than the configured debug messaging (using the URL or using the enable procedure).

Example

This example shows how to use the APEX_DEBUG.MESSAGE procedure to add text to the debug log.

apex_debug.message('the value of %s + %s equals %s', 3, 5, 'eight');

REMOVE_DEBUG_BY_AGE Procedure

Use this procedure to delete from the debug message log all data older than the specified number of days.

Syntax

APEX_DEBUG.REMOVE_DEBUG_BY_AGE (
    p_application_id IN NUMBER,
    p_older_than_days IN NUMBER);

Parameters

Table 8-9 describes parameters available for the APEX_DEBUG.REMOVE_DEBUG_BY_AGE procedure.

Table 8-9 APEX_DEBUG.REMOVE_DEBUG_BY_AGE Procedure Parameters

Parameter	Description
`p_application_id`	The application ID of the application.
`p_older_than_days`	The number of days data can exist in the debug message log before it is deleted.

Example

This example demonstrates removing debug messages relating to the current application, that are older than 3 days old.

BEGIN
    APEX_DEBUG.REMOVE_DEBUG_BY_AGE (
        p_application_id  => TO_NUMBER(:APP_ID),
        p_older_than_days => 3 );
END;

REMOVE_DEBUG_BY_APP Procedure

Use this procedure to delete from the debug message log all data belonging to a specified application.

Syntax

APEX_DEBUG.REMOVE_DEBUG_BY_APP ( 
    p_application_id IN NUMBER);

Parameters

Table 8-10 describes parameters available for the APEX_DEBUG.REMOVE_DEBUG_BY_APP procedure.

Table 8-10 APEX_DEBUG.REMOVE_DEBUG_BY_APP Procedure Parameters

Parameter	Description
`p_application_id`	The application ID of the application.

Example

This example demonstrates removing all debug messages logged for the current application.

BEGIN
    APEX_DEBUG.REMOVE_DEBUG_BY_APP(
        p_application_id => TO_NUMBER(:APP_ID) );
END;

REMOVE_DEBUG_BY_VIEW Procedure

Use this procedure to delete all data for a specified view from the message log.

Syntax

APEX_DEBUG.REMOVE_DEBUG_BY_VIEW ( 
    p_application_id IN NUMBER, 
    p_view_id IN NUMBER);

Parameters

Table 8-11 describes parameters available for the APEX_DEBUG.REMOVE_DEBUG_BY_VIEW procedure.

Table 8-11 APEX_DEBUG.REMOVE_DEBUG_BY_VIEW Procedure Parameters

Parameter	Description
`p_application_id`	The application ID of the application.
`p_view_id`	The view ID of the view.

Example

This example demonstrates the removal of debug messages within the 'View Identifier' of 12345, belonging to the current application.

BEGIN
    APEX_DEBUG.REMOVE_DEBUG_BY_VIEW (
        p_application_id => TO_NUMBER(:APP_ID),
        p_view_id        => 12345 );
END;

REMOVE_SESSION_MESSAGES Procedure

This procedure deletes from the debug message log all data for a given session in your workspace defaults to your current session.

Syntax

APEX_DEBUG.REMOVE_SESSION_MESSAGES (
    p_session    IN NUMBER  DEFAULT NULL);

Parameters

Table 8-12 describes parameters available for the APEX_DEBUG.REMOVE_SESSION_MESSAGES procedure.

Table 8-12 APEX_DEBUG.REMOVE_SESSION_MESSAGES Procedure Parameters

Parameter	Description
`p_session`	The session ID. Defaults to your current session.

Example

This example demonstrates the removal of all debug messages logged within the current session. Note: As no value is passed for the p_session parameter, the procedure defaults to the current session.

BEGIN
    APEX_DEBUG.REMOVE_SESSION_MESSAGES();
END;

TOCHAR Function

This procedure converts a BOOLEAN to a VARCHAR2.

Syntax

APEX_DEBUG.TOCHAR ( 
    p_value IN BOOLEAN ) 
    return VARCHAR2;

Parameters

Table 8-13 describes parameters available for the APEX_DEBUG.TOCHAR function.

Table 8-13 APEX_DEBUG.TOCHAR Procedure Parameters

Parameter	Description
`p_value`	A BOOLEAN 0or 1 that is converted to FALSE or TRUE respectively.

Example

This example shows how to use the APEX_DEBUG.TOCHAR function to convert boolean values to varchar2, so they can be passed to the other debug procedures.

declare
    l_state boolean;
begin
    ....
    apex_debug.info('Value of l_state is %s', apex_debug.tochar(l_state));
    ....
end;

TRACE Procedure

This procedure logs messages at level c_log_level_app_trace.

Syntax

APEX_DEBUG.TRACE ( 
    p_message IN VARCHAR2, 
    p0 IN VARCHAR2 DEFAULT NULL, 
    p1 IN VARCHAR2 DEFAULT NULL, 
    p2 IN VARCHAR2 DEFAULT NULL, 
    p3 IN VARCHAR2 DEFAULT NULL, 
    p4 IN VARCHAR2 DEFAULT NULL, 
    p5 IN VARCHAR2 DEFAULT NULL, 
    p6 IN VARCHAR2 DEFAULT NULL, 
    p7 IN VARCHAR2 DEFAULT NULL, 
    p8 IN VARCHAR2 DEFAULT NULL, 
    p9 IN VARCHAR2 DEFAULT NULL, 
    p_max_length IN PLS_INTEGER DEFAULT 1000 );

Parameters

Table 8-14 describes parameters available for the APEX_DEBUG.TRACE procedure.

Table 8-14 APEX_DEBUG.TRACE Procedure Parameters

Parameter	Description
`p_message`	The debug message. Occurrences of '%s' are replaced by `p0` to `p19`, as in `utl_lms.format_message` and C's sprintf. Occurrences of '%%' represent the special character '%'. Occurrences of '%<n>' are replaced by p<n>.
`p0` through `p9`	Substitution strings for '%s' placeholders.
`p_max_length`	The `p<n>` values are truncated to this length.

Example

This example shows how to use APEX_DEBUG.TRACE to log low-level debug information in the debug log.

apex_debug.trace('Low-level information: %s+%s=%s', 1, 2, 3);

WARN Procedure

This procedure logs messages at level c_log_level_warn.

Syntax

APEX_DEBUG.WARN ( 
    p_message IN VARCHAR2, 
    p0 IN VARCHAR2 DEFAULT NULL, 
    p1 IN VARCHAR2 DEFAULT NULL, 
    p2 IN VARCHAR2 DEFAULT NULL, 
    p3 IN VARCHAR2 DEFAULT NULL, 
    p4 IN VARCHAR2 DEFAULT NULL, 
    p5 IN VARCHAR2 DEFAULT NULL, 
    p6 IN VARCHAR2 DEFAULT NULL, 
    p7 IN VARCHAR2 DEFAULT NULL, 
    p8 IN VARCHAR2 DEFAULT NULL, 
    p9 IN VARCHAR2 DEFAULT NULL, 
    p_max_length IN PLS_INTEGER DEFAULT 1000 );

Parameters

Table 8-15 describes parameters available for the APEX_DEBUG.WARN procedure.

Table 8-15 APEX_DEBUG.WARN Procedure Parameters

Parameter	Description
`p_message`	The debug message. Occurrences of '%s' are replaced by `p0` to `p19`, as in `utl_lms.format_message` and C's sprintf. Occurrences of '%%' represent the special character '%'. Occurrences of '%<n>' are replaced by p<n>.
`p0` through `p9`	Substitution strings for '%s' placeholders.
`p_max_length`	The `p<n>` values are truncated to this length.

Example

This example shows how to use APEX_DEBUG.WARN to log highly important data in the debug log.

apex_debug.warn('Soft constraint %s violated: %s', 4711, sqlerrm);