How to let a user override an Apex validation rule

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

Recently i had to find out a way of overriding an Apex validation rule on user request.
Imagine the following scenario:

if item longer than 10 bytes then raise error
else if item longer than 5 bytes then ask the user and continue if confirmed

In other words, the concept here is to discourage the user from entering a value that exceeds a certain amount without blocking him/her completely.

The first validation is easily implemented, it's just a normal PL/SQL Expression validation type.
The second validation type is apparently not supported by Oracle Application Express, there are no provisions in the validation attributes supporting this kind of "interaction", a rule is either validated or not validated in which case an error message is shown and after submit processes are not executed.

In reality the solution is rather simple, just make a smart use of the validation error message.

The validation error message can contain arbitrary HTML code, so what about stuffing some tags in the message?
Nothing prevents us from copying the HTML of a button template and use it inside the error message, replacing certain substitutions with custom text.

For instance, here is a sample (click on picture to enlarge):


Validation Expression 1 contains the PL/SQL expression, if it evaluates to FALSE, then the error message will be displayed.
In the Error Message field i copied the HTML source code taken from the HTML button template, so the button will look like the others. I replaced a couple of substitution strings as follows:
#LINK# with javascript:doSubmit('forceSUBMIT');
#TEXT# with confirm
The validation is conditional, such that it will not run the validation if previous validations have failed, as I've already explained some time ago in another posting. This is necessary in order to avoid displaying a misleading message in presence of other failing prerequisite validations.
The validation however is only executed when a "standard" SUBMIT request is generated by clicking on the corresponding button, but it will be skipped when the "special" forceSUBMIT request is generated by the custom button inside the error message and this does the trick.
All other validations are executed in either case, just to ensure that everything is ok even if a user changes something between the first round of validations (SUBMIT) and the second one (forceSUBMIT).

Another possibility that i didn't implement, but should work, is to skip the validation if the user checks a box.
This technique however requires an additional item and is somewhat more complex to deal with, especially if the checkbox item is to be displayed only after the first warning. I opted for a simpler solution, with fewer moving parts, in the true spirit of Apex.

You can try also a minimalistic demo of this functionality at apex.oracle.com.

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

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.