A Quick Tutorial on Using SQL Expressions for Validation in Oracle APEX

In this tutorial, we'll explore how to use SQL expressions for validation in Oracle APEX. SQL expressions are like smart rules that help you decide if the data entered in your application is correct. We'll go through the process step by step and provide examples to make it easy to understand.

What is an SQL Expression for Validation?

An SQL expression for validation is like a detective checking if something is true or false in your Oracle APEX application. It's a way to define a condition that must be met for data to be considered valid. If the condition is true, the data is accepted; if it's false, you'll get an error message.

Creating an SQL Expression Validation

Let's walk through the process of creating an SQL expression validation:

1. Go to Page Designer: Open your Oracle APEX application and go to "Page Designer."

   
   

2. Select Your Page Item: Choose the page item that you want to validate. In our example, we'll use P2_JOB.

   
   

3. Add a Validation: In the properties of the selected page item, go to the "Validation" section and click on "Add."

   
   

4. Choose Validation Type: Select "SQL Expression" as the validation type. This tells APEX that we're going to use an SQL expression to validate our data.

   
   

5. Define the SQL Expression: In the "SQL Expression" field, enter your SQL code. Let's say we want to validate that the job is either 'MANAGER' or the department is located in 'CHICAGO'. Here's the SQL expression:

:P2_JOB = 'MANAGER' OR
EXISTS (
    SELECT 1
    FROM dept
    WHERE deptno = :P2_DEPTNO
    AND loc = 'CHICAGO'
)

This SQL expression checks if P2_JOB is 'MANAGER' or if the department specified by P2_DEPTNO is located in 'CHICAGO'. If either condition is true, the data is valid.

6. Set Validation Attributes: You can further customize your validation by setting attributes like error message text, error message display location, and more.

7. Save and Run: Save your validation, and now, when a user enters data in P2_JOB, it will be checked against your SQL expression. If it meets the conditions, it's valid; otherwise, an error message will be displayed.

Additional Examples

Example 1: Checking Age Limit

Let's say you have a page item P3_AGE where users enter their age. You want to make sure they are older than 18. Here's the SQL expression:

:P3_AGE > 18

This SQL expression checks if the age entered in P3_AGE is greater than 18. If it is, the data is valid.

Example 2: Validating Email Format

Suppose you have a page item P4_EMAIL where users enter their email addresses. You want to validate that the email follows a proper format. Here's the SQL expression:

REGEXP_LIKE(:P4_EMAIL, '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\\.[A-Za-z]{2,4}$')

This SQL expression uses a regular expression to check if the email address matches a valid format.

Example 3: Ensuring Unique Usernames

If you have a page item P5_USERNAME where users enter their usernames, you may want to ensure that the username is unique. Here's the SQL expression:

NOT EXISTS (
    SELECT 1
    FROM users
    WHERE username = :P5_USERNAME
)

This SQL expression checks if the username entered in P5_USERNAME doesn't already exist in the user’s table.

Testing the SQL Expression Validation

To test your SQL expression validation, simply run your APEX application and enter different values in the respective page items. The validation will check if the conditions are met and provide error messages when needed.

Conclusion

SQL expressions for validation in Oracle APEX are like detectives ensuring that your data is correct. In this tutorial, we learned how to create an SQL expression validation, define the SQL code, and customize its attributes.