8 APEX_ERROR

The APEX_ERROR package provides the interface declarations and some utility functions for an error handling function and includes procedures and functions to raise errors in an Application Express application.

Topics:

Data Types and Constants
Example of an Error Handling Function
ADD_ERROR Procedure Signature 1
ADD_ERROR Procedure Signature 2
ADD_ERROR Procedure Signature 3
ADD_ERROR Procedure Signature 4
ADD_ERROR Procedure Signature 5
AUTO_SET_ASSOCIATED_ITEM Procedure
EXTRACT_CONSTRAINT_NAME Function
GET_ARIA_ERROR_ATTRIBUTES Function
GET_FIRST_ORA_ERROR_TEXT Function
INIT_ERROR_RESULT Function

Data Types and Constants

This section describes the data types and constants used by the APEX_ERROR package.

Constants used for Result Types
t_error
t_error_result

Constants used for Result Types

The following constants are used for the API parameter p_display_location and the attribute display_location in the t_error and t_error_result types.

c_inline_with_field            constant varchar2(40):='INLINE_WITH_FIELD';
c_inline_with_field_and_notif  constant varchar2(40):='INLINE_WITH_FIELD_AND_NOTIFICATION';
c_inline_in_notification       constant varchar2(40):='INLINE_IN_NOTIFICATION';
c_on_error_page                constant varchar2(40):='ON_ERROR_PAGE';

The following constants are used for the API parameter associated_type in the t_error type.

c_ass_type_page_item           constant varchar2(30):='PAGE_ITEM';
c_ass_type_region              constant varchar2(30):='REGION';
c_ass_type_region_column       constant varchar2(30):='REGION_COLUMN';

t_error

The following type is passed into an error handling function and contains all of the relevant error information.

type t_error is record (
    message           varchar2(32767),     /* Displayed error message */
    additional_info   varchar2(32767),     /* Only used for display_location ON_ERROR_PAGE to display additional error information */
    display_location  varchar2(40),        /* Use constants "used for display_location" below */
    association_type  varchar2(40),        /* Use constants "used for asociation_type" below */
    page_item_name    varchar2(255),       /* Associated page item name */
    region_id         number,              /* Associated tabular form region id of the primary application */
    column_alias      varchar2(255),       /* Associated tabular form column alias */
    row_num           pls_integer,         /* Associated tabular form row */
    is_internal_error boolean,             /* Set to TRUE if it's a critical error raised by the Application Express engine, like an invalid SQL/PLSQL statements, ... Internal Errors are always displayed on the Error Page */
    apex_error_code   varchar2(255),       /* Contains the system message code if it's an error raised by Application Express */
    ora_sqlcode       number,              /* SQLCODE on exception stack which triggered the error, NULL if the error was not raised by an ORA error */
    ora_sqlerrm       varchar2(32767),     /* SQLERRM which triggered the error, NULL if the error was not raised by an ORA error */
    error_backtrace   varchar2(32767),     /* Output of dbms_utility.format_error_backtrace or dbms_utility.format_call_stack */
    component         wwv_flow.t_component /* Component which has been processed when the error occurred */
    );

t_error_result

The following type is used as the result type for an error handling function.

type t_error_result is record (
    message          varchar2(32767), /* Displayed error message */
    additional_info  varchar2(32767), /* Only used for display_location ON_ERROR_PAGE to display additional error information */
    display_location varchar2(40),    /* Use constants "used for display_location" below */
    page_item_name   varchar2(255),   /* Associated page item name */
    column_alias     varchar2(255)    /* Associated tabular form column alias */
    );

Example of an Error Handling Function

create or replace function apex_error_handling_example (
    p_error in apex_error.t_error )
    return apex_error.t_error_result
is
    l_result          apex_error.t_error_result;
    l_reference_id    number;
    l_constraint_name varchar2(255);
begin
    l_result := apex_error.init_error_result (
                    p_error => p_error );
 
    -- If it's an internal error raised by APEX, like an invalid statement or
    -- code which cannot be executed, the error text might contain security sensitive
    -- information. To avoid this security problem rewrite the error to
    -- a generic error message and log the original error message for further
    -- investigation by the help desk.
    if p_error.is_internal_error then
        -- Access Denied errors raised by application or page authorization should
        -- still show up with the original error message
        if    p_error.apex_error_code <> 'APEX.AUTHORIZATION.ACCESS_DENIED'              and p_error.apex_error_code not like 'APEX.SESSION_STATE.%' then
            -- log error for example with an autonomous transaction and return
            -- l_reference_id as reference#
            -- l_reference_id := log_error (
            --                       p_error => p_error );
            --
            
            -- Change the message to the generic error message which is not exposed
            -- any sensitive information.
            l_result.message         := 'An unexpected internal application error has occurred. '||
                                        'Please get in contact with XXX and provide '||
                                        'reference# '||to_char(l_reference_id, '999G999G999G990')||
                                        ' for further investigation.';
            l_result.additional_info := null;
        end if;
    else
        -- Always show the error as inline error
        -- Note: If you have created manual tabular forms (using the package
        --       apex_item/htmldb_item in the SQL statement) you should still
        --       use "On error page" on that pages to avoid loosing entered data
        l_result.display_location := case
                                       when l_result.display_location = apex_error.c_on_error_page then apex_error.c_inline_in_notification
                                       else l_result.display_location
                                     end;
 
        -- If it's a constraint violation like
        --
        --   -) ORA-00001: unique constraint violated
        --   -) ORA-02091: transaction rolled back (-> can hide a deferred constraint)
        --   -) ORA-02290: check constraint violated
        --   -) ORA-02291: integrity constraint violated - parent key not found
        --   -) ORA-02292: integrity constraint violated - child record found
        --
        -- try to get a friendly error message from our constraint lookup configuration.
        -- If the constraint in our lookup table is not found, fallback to
        -- the original ORA error message.
        if p_error.ora_sqlcode in (-1, -2091, -2290, -2291, -2292) then
            l_constraint_name := apex_error.extract_constraint_name (
                                     p_error => p_error );
        
            begin
                select message
                  into l_result.message
                  from constraint_lookup
                 where constraint_name = l_constraint_name;
            exception when no_data_found then null; -- not every constraint has to be in our lookup table
            end;
        end if;
        
        -- If an ORA error has been raised, for example a raise_application_error(-20xxx, '...')
        -- in a table trigger or in a PL/SQL package called by a process and the 
        -- error has not been found in the lookup table, then display
        -- the actual error text and not the full error stack with all the ORA error numbers.
        if p_error.ora_sqlcode is not null and l_result.message = p_error.message then
            l_result.message := apex_error.get_first_ora_error_text (
                                    p_error => p_error );
        end if;
 
        -- If no associated page item/tabular form column has been set, use
        -- apex_error.auto_set_associated_item to automatically guess the affected
        -- error field by examine the ORA error for constraint names or column names.
        if l_result.page_item_name is null and l_result.column_alias is null then
            apex_error.auto_set_associated_item (
                p_error        => p_error,
                p_error_result => l_result );
        end if;
    end if;
 
    return l_result;
end apex_error_handling_example;

ADD_ERROR Procedure Signature 1

This procedure adds an error message to the error stack that is used to display an error on an error page or inline in a notification. It can be called in a validation or process to add one or more errors to the error stack.

Note:

This procedure must be called before the Application Express application has performed the last validation or process. Otherwise, the error is ignored if it does not have a display location of apex_error.c_on_error_page.

Syntax

APEX_ERROR.ADD_ERROR (
    p_message          in varchar2,
    p_additional_info  in varchar2 default null,
    p_display_location in varchar2 );

Parameters

Table 8-1 describes the parameters available in the ADD_ERROR Procedure Signature 1.

Table 8-1 ADD_ERROR Procedure Signature 1 Parameters

Parameters	Description
`p_message`	Displayed error message.
`p_additional_info`	Additional error information needed if the error is displayed on the error page.
`p_display_location`	Specifies where the error message is displayed. Use the constant `apex_error.inline_notification` or `apex_error.c_error_page`. See "Constants used for Result Types".

Example

This example illustrates how to add a custom error message to the error stack. The error message is displayed inline in a notification. This example can be used in a validation or process.

apex_error.add_error (
    p_message          => 'This custom account is not active!',
    p_display_location => apex_error.c_inline_in_notification );

ADD_ERROR Procedure Signature 2

This procedure adds an error message to the error stack that is used to display an error for a page item inline in a notification. It can be called in a validation or process to add one or more errors to the error stack.

Note:

Syntax

APEX_ERROR.ADD_ERROR (
    p_message          in varchar2,
    p_additional_info  in varchar2 default null,
    p_display_location in varchar2,
    p_page_item_name   in varchar2);

Parameters

Table 8-2 describes the parameters available in the ADD_ERROR Procedure Signature 2.

Table 8-2 ADD_ERROR Procedure Signature 2 Parameters

Parameters	Description
`p_message`	Displayed error message.
`p_additional_info`	Additional error information needed if the error is displayed on the error page.
`p_display_location`	Specifies where the error message is displayed. Use the constant `apex_error.c_inline_with field` or `apex_error.c_inline_with_field_and_notif`. See "Constants used for Result Types".
`p_page_item_name`	Name of the page item on the current page that is highlighted if `apex_error.c_inline_with_field` or `apex_error.c_inline_with_field_and_notif` are used as the display location.

Example

This example illustrates how to add a custom error message to the error stack. The P5_CUSTOMER_ID item is highlighted on the page. The error message is displayed inline in a notification. This example can be used in a validation or process.

apex_error.add_error (
    p_message          => 'Invalid Customer ID!',
    p_display_location =>  apex_error.c_inline_with_field_and_notif,
    p_page_item_name   => 'P5_CUSTOMER_ID');

ADD_ERROR Procedure Signature 3

This procedure adds an error message to the error stack that is used to display text as defined by a shared component. This error message can be displayed to all display locations. It can be called in a validation or process to add one or more errors to the error stack.

Note:

Syntax

APEX_ERROR.ADD_ERROR (
    p_error_code          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_escape_placeholders in boolean  default true,
    p_additional_info     in varchar2 default null,
    p_display_location    in varchar2,
    p_page_item_name      in varchar2 );

Parameters

Table 8-3 describes the parameters available in the ADD_ERROR Procedure Signature 3.

Table 8-3 ADD_ERROR Procedure Signature 3 Parameters

Parameters	Description
`p_error_code`	Name of shared component text message.
`p_additional_info`	Additional error information needed if the error is displayed on the error page.
`p0` through `p9`	Values for %0 through %9 placeholders defined in the text message.
`p_escape_placeholders`	If set to `TRUE`, the values provided in `p0` through `p9` are escaped with `sys.htf.escape_sc` before replacing the placeholder in the text message. If set to `FALSE`, values are not escaped.
`p_display_location`	Specifies where the error message is displayed. Use the constants defined for `p_display_location`. See "Constants used for Result Types".
`p_page_item_name`	Name of the page item on the current page that is highlighted if `apex_error.c_inline_with_field` or `apex_error.c_inline_with_field_and_notif` are used as the display location.

Example

This example illustrates how to add a custom error message, where the text is stored in a text message, to the error stack. The P5_CUSTOMER_ID item is highlighted on the page. The error message is displayed inline in a notification. This example can be used in a validation or process.

apex_error.add_error (
    p_error_code       => 'INVALID_CUSTOMER_ID',
    p0                 => l_customer_id,
    p_display_location => apex_error.c_inline_with_field_and_notif,
    p_page_item_name   => 'P5_CUSTOMER_ID' );

ADD_ERROR Procedure Signature 4

This procedure adds an error message to the error stack that is used to display an error for a tabular form inline in a notification. It can be called in a validation or process to add one or more errors to the error stack.

Note:

Syntax

APEX_ERROR.ADD_ERROR (
    p_message          in varchar2,
    p_additional_info  in varchar2 default null,
    p_display_location in varchar2,
    p_region_id        in number,
    p_column_alias     in varchar2 default null,
    p_row_num          in number );

Parameters

Table 8-4 describes the parameters available in the ADD_ERROR Procedure Signature 4.

Table 8-4 ADD_ERROR Procedure Signature 4 Parameters

Parameters	Description
`p_message`	Displayed error message.
`p_additional_info`	Additional error information needed if the error is displayed on the error page.
`p_display_location`	Specifies where the error message is displayed. Use the constant `apex_error.c_inline_with field` or `apex_error.c_inline_with_field_and_notif`. See "Constants used for Result Types".
`p_region_id`	The ID of a tabular form region on the current page. The ID can be read from the view `APEX_APPLICATION_PAGE_REGIONS`.
`p_column_alias`	Name of a tabular form column alias defined for p_region_id that is highlighted if `apex_error.c_inline_with_field` or `apex_error.c_inline_with_field_and_notif` are used as a display location.
`p_row_num`	Number of the tabular form row where the error occurred.

Example

This example illustrates how to add a custom error message for a tabular form, where the column CUSTOMER_ID is highlighted, to the error stack. The error message is displayed inline in a notification. This example can be used in a validation or process.

apex_error.add_error (
    p_message          => 'Invalid Customer ID!',
    p_display_location => apex_error.c_inline_with_field_and_notif,
    p_region_id        => l_region_id,
    p_column_alias     => 'CUSTOMER_ID',
    p_row_num          => l_row_num );

ADD_ERROR Procedure Signature 5

This procedure adds an error message to the error stack of a tabular form that is used to display text as defined by a shared component. This error message can be displayed to all display locations. It can be called in a validation or process to add one or more errors to the error stack.

Note:

Syntax

APEX_ERROR.ADD_ERROR (
    p_error_code          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_escape_placeholders in boolean  default true,
    p_additional_info     in varchar2 default null,
    p_display_location    in varchar2,
    p_region_id           in number,
    p_column_alias        in varchar2 default null,
    p_row_num             in number );

Parameters

Table 8-5 describes the parameters available in the ADD_ERROR Procedure Signature 5.

Table 8-5 ADD_ERROR Procedure Signature 5 Parameters

Parameters	Description
`p_error_code`	Name of shared component text message.
`p0` through `p9`	Values for %0 through %9 placeholders defined in the text message.
`p_escape_placeholders`	If set to `TRUE`, the values provided in `p0` through `p9` are escaped with `sys.htf.escape_sc` before replacing the placeholder in the text message. If set to `FALSE`, values are not escaped.
`p_additional_info`	Additional error information needed if the error is displayed on the error page.
`p_display_location`	Specifies where the error message is displayed. Use the constants defined for `p_display_location`. See "Constants used for Result Types".
`p_region_id`	The ID of the tabular form region on the current page. The ID can be read from the view `APEX_APPLICATION_PAGE_REGIONS`.
`p_column_alias`	The name of the tabular form column alias defined for `p_region_id` that is highlighted if `apex_error.c_inline_with_field` or `apex_error.c_inline_with_field_and_notif` are used as a display location.
`p_row_num`	Number of the tabular form row where the error occurred.

Example

This example illustrates how to add a custom error message, where the text is stored in a text message, to the error stack. The CUSTOMER_ID column on the tabular form is highlighted. The error message is displayed inline in a notification. This example can be used in a validation or process.

apex_error.add_error (
    p_error_code       => 'INVALID_CUSTOMER_ID',
    p0                 => l_customer_id,
    p_display_location => apex_error.c_inline_with_field_and_notif,
    p_region_id        => l_region_id,
    p_column_alias     => 'CUSTOMER_ID',
    p_row_num          => l_row_num );

AUTO_SET_ASSOCIATED_ITEM Procedure

This procedure automatically sets the associated page item or tabular form column based on a constraint contained in p_error.ora_sqlerrm.This procedure performs the following:

Identifies the constraint by searching for the schema.constraint pattern.
Only supports constraints of type P, U, R and C.
For constraints of type C (check constraints), the procedure parses the expression to identify those columns that are used in the constraints expression.
Using those columns, the procedure gets the first visible page item or tabular form column that is based on that column and set it as associated p_error_result.page_item_name or p_error_result.column_alias.
If a page item or tabular form column was found, p_error_result.display_location is set to apex_error.c_inline_with_field_and_notif.

Syntax

APEX_ERROR.AUTO_SET_ASSOCIATED_ITEM (
    p_error_result in out nocopy t_error_result,
    p_error        in            t_error );

Parameters

Table 8-10 describes the parameters available in the AUTO_SET_ASSOCIATED_ITEM procedure.

Table 8-6 AUTO_SET_ASSOCIATED_ITEM Procedure Parameters

Parameters	Description
`p_error_result`	The result variable of your error handling function.
`p_error`	The `p_error` parameter of your error handling function.

Example

See an example of how to use this procedure in "Example of an Error Handling Function".

EXTRACT_CONSTRAINT_NAME Function

This function extracts a constraint name contained in p_error.ora_sqlerrm. The constraint must match the pattern schema.constraint.

Syntax

APEX_ERROR.EXTRACT_CONSTRAINT_NAME (
    p_error          in t_error,
    p_include_schema in boolean default false )
    return varchar2;

Parameters

Table 8-7 describes the parameters available in the EXTRACT_CONSTRAINT_NAME function.

Table 8-7 EXTRACT_CONSTRAINT_NAME Function Parameters

Parameters	Description
`p_error`	The `p_error` parameter of your error handling function.
`p_include_schema`	If set to `TRUE`, the result is prefixed with the schema name. For example, `HR.DEMO_PRODUCT_INFO_PK`. If set to `FALSE`, only the constraint name is returned.

Example

See an example of how to use this procedure in "Example of an Error Handling Function".

GET_ARIA_ERROR_ATTRIBUTES Function

This function is useful for item plug-in developers, to enhance screen reader usability of your item, specifically when that item is associated with an error on a page. This function is called as part of rendering of the item, where the main form element(s) are output. The returned WAI-ARIA attributes include:

aria-invalid="true" - Indicates the page item's current value is invalid. When the user is focused on the page item, the screen reader announces 'Invalid Entry' .
aria-describedby="[page_item_name]_error" - This attribute value matches up with the ID of a <div> tag containing the item's associated error message, enabling a screen reader to announce the actual error, when the user is focused on the page item.

Note:

Because these attributes only enhance screen reader usability, attributes are returned only if the current session is running in Screen Reader mode.

Syntax

function get_aria_error_attributes (
    p_item_name     in varchar2 )
    return varchar2;

Parameters

Table 8-9 describes the parameters available in the GET_ARIA_ERROR_ATTRIBUTES function.

Table 8-8 GET_ARIA_ERROR_ATTRIBUTES Function Parameters

Parameter	Description
`p_item_name`	The page item name. This value is available by using the name attribute of the `apex_plugin.t_page_item` record type, which is passed in as the 1st parameter to all item plug-in's Render Function Callback.

Example

This example shows how this function can be used, in rendering a SELECT element, during processing of the Render Function callback for an item plug-in. This function returns additional attributes, if the page item has errors associated with it and if the user is running in Screen Reader mode.

...
        l_name := apex_plugin.get_input_name_for_page_item(false);
        sys.htp.prn('<select name="'" id="'||p_item.name||'" '||
                    apex_error.get_aria_error_attributes(p_item.name)||'>'); 
...

GET_FIRST_ORA_ERROR_TEXT Function

This function returns the first ORA error message text stored in p_error.ora_sqlerrm. If p_error_ora_sqlerrm does not contain a value, NULL is returned.

Syntax

APEX_ERROR.GET_FIRST_ORA_ERROR_TEXT (
    p_error            in t_error,
    p_include_error_no in boolean default false )
    return varchar2;

Parameters

Table 8-9 describes the parameters available in the GET_FIRST_ORA_TEXT function.

Table 8-9 GET_FIRST_ORA_TEXT Function Parameters

Parameters	Description
`p_error`	The `p_error` parameter of your error handling function.
`p_include_error_no`	If set to `TRUE`, ORA-xxxx is included in the returned error message. If set to `FALSE`, only the error message text is returned.

Example

See an example of how to use this procedure in "Example of an Error Handling Function".

INIT_ERROR_RESULT Function

This function returns the t_error_result type initialized with the values stored in p_error.

Note:

This function must be used to ensure initialization is compatible with future changes to t_error_result.

Syntax

APEX_ERROR.INIT_ERROR_RESULT (
    p_error  in t_error)
    return   t_error_result;

Parameters

Table 8-10 describes the parameters available in the INIT_ERROR_RESULT function.

Table 8-10 INT_ERROR_RESULT Function Parameters

Parameters	Description
`p_error`	The `p_error` parameter of your error handling function.

Example

See an example of how to use this function in "Example of an Error Handling Function".