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.

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.

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;

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
  -- Validate Email Address.
  l_result_text := 
     valid_email (p_email_address => V('P5_EMAIL_ADDRESS'));
  IF l_result_text IS NOT NULL THEN
    -- Add error to error stack.
    -- You get to decide the p_display_location for each
    --   validation you perform.
    apex_error.add_error
     (p_message          => l_result_text,
      p_page_item_name   => 'P5_EMAIL_ADDRESS',
      p_display_location => 'INLINE_WITH_FIELD');
  END IF;

  -- Additional validations for the page added here.

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.

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.

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.