9 APEX_ERROR

Constants and Attributes 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';

The following record structure is passed into an error handling callout function and contains all the relevant information about the error.

type t_error is record (
    message  varchar2(32767),             /* Error message which will be displayed */
    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 APEX 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 APEX */
    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 sys.dbms_utility.format_error_backtrace or sys.dbms_utility.format_call_stack */
   error_statement   varchar2(32767),     /* Statement that was parsed when the error occurred - only suitable when parsing caused the error */
   component         wwv_flow.t_component /* Component which has been processed when the error occurred */
);

The following record structure must be returned by an error handling callout function.

type t_error_result is record (
   message          varchar2(32767), /* Error message which will be displayed */
   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

The following is an 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
        -- mask all errors that are not common runtime errors (Access Denied
        -- errors raised by application / page authorization and all errors
        -- regarding session and session state)
        if not p_error.is_common_runtime_error 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 doesn't expose
            -- 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.

Syntax

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

Parameters

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

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.

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 9-2 describes the parameters available in the ADD_ERROR Procedure Signature 2.

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.

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 9-3 describes the parameters available in the ADD_ERROR Procedure Signature 3.

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.

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 9-4 describes the parameters available in the ADD_ERROR Procedure Signature 4.

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.

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 9-5 describes the parameters available in the ADD_ERROR Procedure Signature 5.

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 9-10 describes the parameters available in the AUTO_SET_ASSOCIATED_ITEM procedure.

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 9-7 describes the parameters available in the EXTRACT_CONSTRAINT_NAME function.

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.

Syntax

function get_aria_error_attributes (
    p_item_name     in varchar2 )
    return varchar2;

Parameters

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

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 9-9 describes the parameters available in the GET_FIRST_ORA_TEXT function.

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.

Syntax

APEX_ERROR.INIT_ERROR_RESULT (
    p_error  in t_error)
    return   t_error_result;

Parameters

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

Example

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