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.