Wednesday, December 10, 2008

Skipping apex item validations if others have already failed

Always check out the original article at http://www.oraclequirks.com for latest comments, fixes and updates.

A quick tip that may come in handy if you need to skip one or more validations if other prerequisite validations have already failed.

This technique can be useful to prevent Oracle Application Express unhandled errors during the validation phase owing to exceptions raised while checking certain non-standard conditions.

A simple scenario will certainly help to understand the point:

say we have an item called P1_STEPS and it must contain a positive numeric value.
At a first glance one may be tempted to create a simple validation based on a PL/SQL expression or SQL expression like:
:P1_STEPS > 0

This will certainly work if the end user plays a fair game, but it will throw an unhandled exception like:
ORA-06502: PL/SQL: numeric or value error: character to number conversion error
Error ERR-1025 Error processing PLSQL expression. :P1_STEPS > 0
if it is a PL/SQL expression validation or:
ORA-06503: PL/SQL: Function returned without value
Error ERR-1023 Unable perform validations.
if it is defined as a SQL expression validation, if the end user enters a non numeric character like 'A' in that form field.

Typically in Apex one should check first if a value is numeric, before attempting to use the value and apex provides the developer with several built-in validations. However even if you perform a built-in validation like "item specified is numeric" followed by "item is not null or zero" and either of the two fails, nothing will prevent Apex from running the additional validation which will end up in error.

So, basically, what i am proposing here is to "skip" a particular validation when we are already aware that other validations have failed along the path.
In order to do so, i need to check the value of an apex global variable called:
wwv_flow.g_inline_validation_error_cnt

CAVEAT
This global variable might change in the future without notice.
Although this is somewhat unlikely to happen, you must be aware that there is no official Oracle document (to my knowledge) stating that the usage of this variable is supported. As far as i know this variable is present since Apex 2.0 and probably earlier.

This constant contains a numeric counter of the inline failed validations (so far), so we can make the validation rule conditional as follows:
if :REQUEST = 'GO' and wwv_flow.g_inline_validation_error_cnt = 0
then
return TRUE;
else
return FALSE;
end if;
In this example i am assuming that the validation is already conditional and is fired when the request equals 'GO', that's why i need to combine the two conditions.

Thanks to this simple technique, now we can safely check if an item is numeric and also ascertain whether it is positive or negative or zero without incurring in run-time exceptions.

Hopefully, in the future, there will be some official API to check for this useful global variable or even a checkbox in the validation GUI that allows a developer to skip a validation if previous ones have already failed or even specify some kind of dependency.

See more articles about Oracle Application Express or download tools and utilities.

5 comments:

glenm said...

It would probably be easier (and supported) to use the existing validation condition type of 'no inline validation errors displayed' with a button condition on 'GO'. But I can imagine scenarios where you would need to setup more complex conditions and this variable would be of use in that situation.

Byte64 said...

Glen,
as you know developers are never satisfied with what they've got.
Ideally it would be nice to have the possibility of joining with some boolean rule (declaratively) two conditions, but then certainly someone would come up and ask for three and so on.
I think that the best way is to support each and every kind of declarative condition through a corresponding API, so that one can create any kind of combinations programmatically and in a 100% supported way.
Concerning the variable, i don't really see why this simple flags should change in the foreseeable future and probably Apex itself is doing large use of them under the hood, but never say never.

Thanks for your comment!

Anonymous said...

Thanks a lot,

it helped me quickly.

Pauli said...

There is a lot simpler way to handle the case presented: first validation checks that the value is numeric and the second validation is conditional on the value being numeric. Just select Condition Type "Value of Item in Expression 1 Is Numeric". To make it look like one validation to the user you can make the error message the same "Value must be a number in range...".

Byte64 said...

Hi Pauli,
well, may be, however what i was trying to show here is a general case in simple terms as i don't like very much those tutorials where initially the dude says "it's very easy!" and then follow 4 pages of inexplicable code for doing something that you will never see once in your life.

As Glen said (see comment#1) in very simple situations it's not necessary to resort to this type of solution, but for multiple conditions, most likely you'll have to.

Flavio

yes you can!

Two great ways to help us out with a minimal effort. Click on the Google Plus +1 button above or...
We appreciate your support!

latest articles