Press "Enter" to skip to content

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

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.

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:

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;

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;
Change the Oracle Apex PL/SQL process code with the following code:
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.

Related Tutorials:

Have you found the answer to your question? If not, you can discuss it with me in the comments section below or join my Q&A community OrclQA.com for developers and ask your question. It is FREE.

Vinish Kapoor

Follow

Hi, I am a full stack developer and writing about development. I document everything I learn and help thousands of people. foxinfotech.in is created, written, and maintained by me; it is built on WordPress, and hosted by Bluehost. Connect with me on Facebook, Twitter, GitHub, and get notifications for new posts.

guest
1 Comment
Oldest
Newest Most Voted
Inline Feedbacks
View all comments
Roland
Roland
15 days ago

Excelent!! thanks for shared know