In most cases error messages are shown on an APEX page when the user submits a form and a validation fails. Sometimes (not often) you may need to manually control the validations rather than using the declarative validations section. To do this you can use the apex_error API. The following is an example of how to call the API.
apex_error.add_error(
p_message => 'Custom error from PL/SQL',
p_display_location => apex_error.c_inline_in_notification);
This will generate the following on the page:
{% asset_img custom-error.png %}
apex_error.add_error triggers a rollback and in most cases this is a good thing. In some very specific cases you may not want a rollback to occur. The rest of this article covers how to generate errors without a rollback occurring.
Edge Case
I recently had a rare edge case for a page submit and errors. The page was used to edit batch staging data. The requirements were:
- User submits page
- All the data is saved (in a staging table)
- Since this is staging area, the data must be saved and committed each time regardless of errors
- Do validations against staged data
- If errors were found, the page would show them (remember the data still needs to be saved)
- If no errors were found, the staged data would be posted and then branch to another page.
The Solution
Given that apex_error.add_error performs a rollback the simple solution is to save the staged data, perform an explicit commit, and then do custom validations. Unfortunately this was not possible due to additional requirements that I left out of this article.
_The following relies on the fact that you know about the apex.message API. If you do not please read {% postlink custom-apex-notification-messages this article %}.
This is an overview of the final solution:
- On Submit PL/SQL page process that:
- Saved all the page's data
- Called a custom validation function that returned a JSON object. If no errors were found this function would return
null. The results of this function were stored in the page item P1_ERR_JSON.
- Created an
onPageLoad Dynamic Action to display errors using the apex.message API
The following pseudo code summarizes the above description:
Page Process
l_err_json := null;
select sum(salary)
into l_salary_total
from my_stage_table;
if l_salary_total > 1000 then
apex_json.initialize_clob_output;
apex_json.open_object;
apex_json.write('errMsg', 'Total salary must be less than $1000');
apex_json.close_object;
l_err_json := apex_json.get_clob_output;
apex_json.free_output;
end if;
apex_util.set_session_state('P1_ERR_JSON', l_err_json, false);
onPageLoad Dynamic Action
err = apex.item('P1_ERR_JSON').getValue();
if (err.length > 0) {
err = JSON.parse(err);
apex.message.clearErrors();
apex.message.showErrors([
{
type: apex.message.TYPE.ERROR,
location: ["page"],
message: err.errMsg,
unsafe: false
}
]);
}
When an error occurred, the page looked like:
{% asset_img total-error.png %}
Wrapping Up
This solution is a summary of the final code. You can easily expand it to handle multiple error messages, associate with page items, etc.