APEX_ERROR use case
When you enter data on a page we often think about APEX Validations when we want to validate our inputs and give errors to the user. (i.e. Value is required. Dates are not within range, etc…)
However, sometimes you may not be able to report all errors with a validation. Or perhaps the code you need to call performs validations and processing all in once transaction like in the case of a 3rd party API. Enter APEX_ERROR.ADD_ERROR
From the documentation.
ADD_ERROR Procedure: This procedure adds an error message to the error stack that is used to display an error on an error page or inline in a notification. It can be called in a validation or process to add one or more errors to the error stack.
For example, if you’ve used some of the Oracle eBusiness Suite API, you know that it could take a lot of code to setup and call one of these API. Then, the API may return with errors that you may want to display back on your page. If you don’t handle them correctly, your APEX page will just display a single error or exception.
Lets say you have an error record type that will hold multiple errors:
type message_rec_type is record
(
message_text varchar2(4000)
-- ... other attributes ...
);
type message_tbl_type is table of message_rec_type
index by binary_integer;
Then, if our API call returns a bunch of errors in a message_tbl_type structure, we can put those on our APEX page like so:
for i in 1 .. l_msg.COUNT loop
apex_error.add_error(p_message => l_msg(i).message_text
, p_display_location => apex_error.c_inline_in_notification);
end loop;
For a quick and dirty example, I created a Page Process with this code:
apex_error.add_error(
p_message => 'Your date range overlaps with other periods.'
, p_display_location => apex_error.c_inline_in_notification
);
apex_error.add_error(
p_message => 'The category combination is invalid for the selected item.'
, p_display_location => apex_error.c_inline_in_notification
);
Submit the page and the result is:
Hi, I'm Jorge Rimblas. Father, husband, photographer, Oraclenerd, Oracle APEX expert, Oracle ACE, coffee lover, car guy, gadget addict, etc... I'm an APEX Tech Lead DRW. I have worked with Oracle since 1995 and done eBusiness Suite implementations and customizations. Nowadays I specialize almost exclusively in Oracle APEX.
Would you mind doing a use case for a page validation? Would you use APEX_ERROR for a “Function returning validation text”? It seems pointless if you can specify the error text being returned anyway.
What about “Function returning boolean”? Can you show how query APEX stack to see if the function should return true or false?
Can you use with ajax request? I’ll be trying a simple example to see how APEX handles errors in the stack when it comes to ajax requests.
Hi Alistair,
I would not use APEX_ERROR.add_error within “Function returning validation text”. It would be redundant. In that case I would just code
if NOT date_is_valid(:Pn_PERIOD_DATE) thenreturn 'Your date range overlaps with other periods';
end if;
That is so much easier. Plus you can do many many checks and simply return the error messages.
APEX_ERROR.add_error is more for the scenario where you can’t separate the validations from the processing. It’s a very specific problem (easy to avoid when you OWN the code).
Function returning boolean is the same scenario as with the validation text but you place the error message in Error Message field. AND you only return one error.
I have not looked into apex_error.add_error and ajax. However, in a way, these are apples and oranges because we’re talking about errors that display after you submit/process the page.
Hope this helps
-Jorge
Ah, I see your point now. I was playing about with it today. We have a lot of custom functions for tabular forms where we user the global arrays (apex_applicaiton.g_fxx) and when we run our validations against those and return validation text apex always says “1 error has occurred” followed by all our validations. What I liked about using APEX_ERROR was that apex was now showing “x errors has occured” where x is relating to the number of errors.
The reason I mentioned AJAX was that my team have been thinking of using ajax to run pl/sql against passed in values and return error text. I was just wondering how APEX handled these kind of error. Ideally it would return an html chunk (i.e the error region with validation text) to be inserted into the page using js. (I know its easily done but just trying to think of a standard way not to make it feel so “hacky”)
Jorge,
I have a puzzler for you.. I have a pl/procedure I am calling from a page. Inside this procedure I want to catch errors and send them to the calling page of my APEX application.
In the procedure I have the standard exception handler:
EXCEPTION
WHEN OTHERS THEN
apex_error.add_error (p_message => ‘Error in Download_Product_Service !’,
p_display_location => apex_error.c_inline_in_notification );
I intentionally add an error to the code to see what happens, and to my surprise there is NO error logged, no error displayed.. Is it possible my exception is getting eaten from the code I am calling in the procedure??
Not sure what’s going on. To troubleshoot, I would add a call to apex_error.add_error outside of the exception and see if it’s displayed. My suspicion is that there’s something going on with commits and rollbacks. Or as you mention something else is eating the exception, but even that should display the message from add_error
I think I might have found the issue.. Buried within the package I am calling (as_xlsx) is an exception handler with a raise being called…
If I replace it with the call to add an apex_error, do you think it will raise up to the application error handler and be caught properly?
If you call apex_error.add_error and raise the error you’ll see two errors on the screen, yours and the raised exception.
Validate this. As I recall, when you call apex_error.add_error APEX will increase the error validation count on the page and not continue with the processing. You shouldn’t have to raise the error, unless you want to see the Oracle error itself.
In this case I need the error to be displayed to the user..
Use case here: We are allowing the user to export a report to an Excel file (not using the built in export, since it exports to a csv only). The code is a modified version of as_xlsx. When we are exporting data, we are pulling it from a collection, and thus if there is an error pulling the data, we want the user to know an issue happened so we can fix it..
When we use a straight add_error, the error is logged but the routine keeps going, building an empty excel file for the user to download..
I have an exception block like this:
exception
when others then
apex_error.add_error (p_message => ‘Error in Query2Sheet !’,
p_display_location => apex_error.c_inline_in_notification );
RAISE;
And nothing is being logged and nothing displayed to users..
Odd, it should work if this was in a regular process. But since you’re doing a download maybe there’s more to your example that I’m able to understand here.
apex_error.add_error does not raise exceptions and it does not stop processing, that you have to handle yourself.
Understood,, I had thought if the process is erroring out when the sql select is processed. It hits the exception, but even with a rise it does not log to the apex error handler table.. I will attempt a straight raise and add it to the constraint table to see if that will accomplish the goal..
Have to wait a bit, server folks are now updating our instance to APEX 5….
Upgraded, still trying…
With the Apex_Error or any other error mechanism inside PL/SQL Process, can you stop the further process run (based on the same condition) and also rollback the previously run processes?
You’re in charge of the commit and rollback. However, what I usually do is avoid doing the transaction by checking if I have any errors. I’ll use this condition
apex_error.get_error_count = 0in my Process along with any other checks I need.Hey Jorge love your posts!
I am now trying to use add_error in a dynamic action PL/SQL execution but no luck..
I have a popup with a Save button but the error shows in a popup of its own.
I tried capturing the exception but the no error is shown. Haven’t you used it yet this way?