Oracle Apex: Display Custom Error Messages from PL/SQL Process

  • Post author:
  • Post category:OrclApex
  • Reading time:5 mins read
Custom error message example-1.

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:

PL/SQL Process for custom error messages.

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.

 e_error exception;

  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;


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:

Custom error message example-1.

If you will enter the salary to less than or equal to 3,000 then you will get the following error:

Custom error message example-2.

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;


  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;

Change the Oracle Apex PL/SQL process code with the following code:
 v_error varchar2(1000);
 e_error exception;

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;


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.

Related Tutorials:

Vinish Kapoor

An Oracle Apex Consultant, Oracle ACE, and founder of and a question and answer forum for developers.

This Post Has 4 Comments

  1. Roland

    Excelent!! thanks for shared know

  2. saiful
    • Hi my oracle apex version is 20.1. I have try with your example but in my case it showing "Error processing validation." after submit. help me pls.
  3. Soumya

    It really helped me to solve my issue. Thank you so much for sharing this article. very well explained . Thank you .

  4. Anand

    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"?


Comments are closed.