Show custom error message against database constraint in oracle apex

In oracle apex application, when an end user face an error on constraint (such as primary key, foreign key, unique key, check etc.) of database then user see the error message that defined on database. Most of the time that messages are not clear to end user. Sometimes error raises like it.
 

On this point, oracle apex has a great error handling feature to show our customize message to end user. For generating customized message, follow the following steps. It's very simple.

Firstly, you have to create a table. Here is the table script for you help.

CREATE TABLE  CONSTRAINT_LOOKUP 
   (CONSTRAINT_NAME VARCHAR2(30), 
	MESSAGE VARCHAR2(4000) NOT NULL ENABLE, 
	 PRIMARY KEY (CONSTRAINT_NAME) ENABLE
   );

Then create a function on your schema. You can just copy and paste function code from here.

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 p_error.is_internal_error
   THEN
      IF NOT p_error.is_common_runtime_error
      THEN
         -- 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
      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 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;  
         END;
      END IF;

      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 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;

After then go to Shared Component / Application Definition Attributes / Error Handling section and place your function name at Error Handling Function field.

 

Finally, insert your message against constraint of database on CONSTRAINT_LOOKUP table.

NB: Here OID and SIDPK is the constraint name of database.

Now go to application and face the constraint error again see your customized message. For enhancing your error handling follow APEX_ERROR API.

Think it helps you. As always I am here to help you. Enjoy!