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:
#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.