In Oracle Apex, there is an option to enter the error message in the Error Message text box to show it whenever any error occurred in the PL/SQL process. But it will display always the same error message. You might be required to display different error messages depending on the validation. In this tutorial, I will show you how you can display custom error messages from PL/SQL process.
Display Custom Error Messages from PL/SQL Process in Oracle Apex
To display the custom error messages dynamically from PL/SQL process, you need to specify an item in the Error Message text box and set the value of that item in the PL/SQL process.
To do this, first create a page item on any region of that page and set the following properties:
- Name: P3_ERROR_MSG (P3 is the prefix for my page no. 3, it could be different)
- Type: Hidden
- Value Protected: No (switch of the Yes/No button)
Then I have created a PL/SQL process PRC_VALIDATE_EMP before the main process of that page to validate the data and to display relevant error messages.
I have added the page item P3_ERROR_MSG, we created above, like the string substitution (&P3_ERROR_MSG.) in the Error Message text box. As shown in the following screenshot:
And added the following PL/SQL code to validate the salary for a particular department number 90. It will check if the salary is less than or equal to 3,000 then give the relevant error and also will check if the salary is greater than 10,000 for department 90 then will give the error accordingly. I have just put these conditions to show you the example, your conditions can be different and can be more for other items on the page.
declare
e_error exception;
begin
if :P3_DEPARTMENT_ID = 90 AND :p3_salary > 10000 then
apex_util.set_session_state('P3_ERROR_MSG', 'Salary must be less than 10,000 for department 90.');
raise e_error;
elsif :P3_DEPARTMENT_ID = 90 AND :p3_salary <= 3000 then
apex_util.set_session_state('P3_ERROR_MSG', 'Salary must be greater than from 3,000 for department 90.');
raise e_error;
end if;
end;You can see in the above code that I am using the APEX_UTIL.SET_SESSION_STATE method to set the custom error message for the hidden page item P3_ERROR_MSG and raising the error E_ERROR defined as an exception in the declare section.
Now save the changes and run the page to test. It will display the error message as shown in the below image:
If you will enter the salary to less than or equal to 3,000 then you will get the following error:
Return Custom Error Messages Using the Database Function
You can do this by using the database function also and return the error message using the OUT parameter. Below is an example:
Create this function in your Oracle Database schema:
create or replace function fnc_validate_emp (i_dept in integer, i_salary in number, o_error_message out varchar2)
return boolean is
e_error exception;
begin
if i_dept = 90 AND i_salary > 10000 then
o_error_message := 'Salary must be less than 10,000 for department 90.';
elsif i_dept = 90 AND i_salary <= 3000 then
o_error_message := 'Salary must be greater than from 3,000 for department 90.';
end if;
if o_error_message is not null then
return false;
end if;
return true;
end;declare
v_error varchar2(1000);
e_error exception;
begin
if not fnc_validate_emp(:P3_DEPARTMENT_ID, :P3_SALARY, v_error) then
apex_util.set_session_state('P3_ERROR_MSG', v_error);
raise e_error;
end if;
end;The functionality is the same, the difference is the validation code is written in the database using the stored function and in Apex it is just setting the error message text.
Excelent!! thanks for shared know
It really helped me to solve my issue. Thank you so much for sharing this article. very well explained . Thank you .
I have a similar issue. But I need to remove the "1 error has occurred line" and only display one line - similar to "Salary must ...". Is there any way I can remove the line "1 error has occurred"?
Thanks