Interactive Grid - database constraint error handling for interactive grid

Question

I'm working with APEX 19.2. I'm trying to handle constraint errors with an error handling function.

I have an interactive grid built on a table with unique constraint. I created constraint_lookup that matches constraint name with message.

I created a function mainly based on Oracle Doc example (that is for example in package wwv_flow_error_api)

The only thing I changed was the display location:

l_result.display_location := case
                                 when l_result.display_location =
                                      apex_error.c_on_error_page then
                                  apex_error.c_inline_with_field_and_notif
                                 else
                                  l_result.display_location
                               end;

Right now when I'm trying to add a value with non-unique value I get a message in notification like I wanted, but I don't know how to handle it to show error in notification and with the field (like in apex validation, where you can associate item)

I tried to list all apex_error attributes and I get

p_error
message ORA-00001: naruszono więzy unikatowe (#CONSTAINT_NAME#)
additional_info
ORA-00001: naruszono więzy unikatowe (#CONSTAINT_NAME#)
display_location ON_ERROR_PAGE
association_type
page_item_name
region_id
column_alias
row_num
model_instance_id
model_record_id
apex_error_code
original_message
original_additional_info
ora_sqlcode -1
ora_sqlerrm ORA-00001: naruszono więzy unikatowe (#CONSTAINT_NAME#)
error_backtrace
error_statement "ADM"

l_result
message #ERROR MESSAGE#
additional_info ORA-00001: naruszono wiezy unikatowe (#CONSTAINT_NAME#)
display_location INLINE_WITH_FIELD_AND_NOTIFICATION
page_item_name
column_alias

How to connect field from constraint with the field in interactive grid?

Answer 1

You should add p_page_item_name parameter. For example:

    p_message  => 'Employee name cannot be null',
    p_display_location => apex_error.c_inline_with_field_and_notif ,
    p_page_item_name => 'P10_NAME');

Answer 2

Can you update your error handling function with the following code please. It adds additional debug statements.

if p_error.ora_sqlcode in (-1, -2091, -2290, -2291, -2292) then
    l_constraint_name := apex_error.extract_constraint_name (
                             p_error => p_error );
    apex_debug.info( 'Raised constraint error: %s', l_constraint_name );

    begin
        select message
          into l_result.message
          from constraint_lookup
         where constraint_name = l_constraint_name;
        apex_debug.info( 'Constraint found, new message: %s', l_result.message );
    exception when no_data_found then
        apex_debug.info( 'Constraint not found in constraint_lookup' );
    end;
end if;

Run your application in debug mode and reproduce the error. After that you can use View Debug to find out if your constraint_lookup entry is actually used.