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!
Think it helps you. As always I am here to help you. Enjoy!
