Introduction
There are many options for handling validations in Oracle APEX. This post will focus on the PL/SQL Error validation type. I want to focus on this validation type because it allows us to consolidate validation logic into a PL/SQL package, centralize validation logic, facilitate reuse, and improve performance.
Background
APEX validations allow you to execute conditional logic when the page is submitted. If any of the validations fail, then the form submission is halted, and one or more error messages are displayed to the user. I won’t go into detail in this post, but if you are unfamiliar with APEX validations, I suggest you read this section of the APEX App Builder User Guide.
💡
As I mentioned, there are many options for creating validations in APEX, but this post will focus on the PL/SQL Error validation type.
Approach
The idea is to create a single validation in APEX Builder for each page. This validation uses the PL/SQL Error validation type and calls a PL/SQL package.procedure to perform all the validations for the page. The procedure will call apex_error.add_error to add errors to the error stack each time it encounters an error condition. Finally, the error messages will be displayed to the end user, and form submission will be stopped.
This approach is made possible by the apex_error.add_error API. This API adds an error to the APEX error stack. After the validation phase of the page submission is complete, all errors on the error stack are displayed to the user, and page submission is halted.
💡
Note: The apex_error.add_error procedure must be called before the Oracle APEX application has performed the last validation or process. Otherwise, the error is ignored if it does not have a display location of apex_error.c_on_error_page.
When to Use It?
I am not proposing you always follow this approach, but it can be helpful in the following situations:
If you have more than a handful of validations on a page, managing them from a PL/SQL package would be easier.
If the same validations are performed on more than one page or more than one application, centralizing the logic in a PL/SQL package makes it easier to manage and deploy.
Forms where you cannot make page items required because you want the user to be able to come back and complete the form at a later date. In this case, you must create multiple conditional validations on individual fields instead of just checking the required option.
Example
We have a function that validates an email address. The function returns NULL if the email is valid; otherwise, it returns an error message from an APEX Text Message. The idea is that this function is re-usable.
FUNCTION valid_email (p_email_address IN VARCHAR2) RETURN VARCHAR2 IS
c_regex CONSTANT VARCHAR2(100) := '^(?i)[a-z0-9][a-z0-9._%+-]*@[a-z0-9.-]+\.[a-z]{2,}$';
BEGIN
IF NOT REGEXP_LIKE (p_email_address, c_regex, 'i') THEN
RETURN(apex_lang.message
(p_name => 'INVALID_EMAIL', p0 => p_email_address));
ELSE
RETURN NULL;
END IF;
END valid_email;
🎁
Bonus content: the Regex above does a pretty good job of validating email addresses.
We then create a specific validation procedure for our page (page 5). This validation procedure calls the generic validation APIs and then calls apex_error.add_error whenever a validation fails.
PROCEDURE p5_validations IS
l_result_text VARCHAR2(1000);
BEGIN
l_result_text :=
valid_email (p_email_address => V('P5_EMAIL_ADDRESS'));
IF l_result_text IS NOT NULL THEN
apex_error.add_error
(p_message => l_result_text,
p_page_item_name => 'P5_EMAIL_ADDRESS',
p_display_location => 'INLINE_WITH_FIELD');
END IF;
END p5_validations;
In APEX, we create a single validation of type PL/SQL Error for the page pointing to the above validation procedure.
The area highlighted in green does not come into play for a PL/SQL Error type validation. This is handled by the apex_error.add_error API.
💡
This is why I abstract the page-level procedure (p5_validations), which decides how to handle each validation and displays each validation failure to the user from the functions/procedures that perform the validation (valid_email).
Advantages
The code you use for validations (e.g., the email validation function above) is stored in a PL/SQL package and is more reusable.
You can stop processing further validations based on your logic. This allows you to choose if you show the user the first one or two errors or potentially overwhelm them with ten validation errors.
Having the validation logic in a PL/SQL package means you can change and deploy your validation logic without deploying the APEX application.
Performance can be improved by:
Using compiled PL/SQL code.
In some situations, you can use one SQL query to fetch multiple values that can be used to perform several validations. Having these in separate validations on the page sometimes involves running the same query multiple times.
Disadvantages
- The validations are not visible within the APEX Builder. This can make debugging a little trickier.
💡
I could only think of one disadvantage. Please comment on this post if you can think of others.
Conclusion
This approach can help organize and reuse code in validations and error messages. However, as with most programming solutions, it is not a one-size-fits-all solution. For example, you may not need to do this if you have one field with one validation on a page.
