Data validation is a crucial part of web application development. Clean data is typically collected via forms and is an important foundation of enterprise IT. Whether you are an end-user filling in a form or a developer creating a form, everyone has frustration regarding forms. In this blog, we will be guiding you on how to use SQL expressions for validation in Oracle APEX. But before that, we will discuss some of the basics you must understand.

Understanding Validation

A validation is basically an edit check and if it is associated with only a single item, it is page item validation. However, if it applies to a complete page, then it is page validation. Likewise, there are different names or different types of validations.
Validation can be defined declaratively by choosing a validation Oracle APEX tutorial method. It is important to note that if a validation fails, the subsequent page computations or processes will not occur. Also, the validation you enter has to be consistent with the validation type you chose.

Oracle APEX Validations

What is Oracle APEX validation? In Oracle APEX, validations are procedures used to make sure that the data entered into the forms fulfills certain criteria before being saved or processed into the database. With these validations, you can prevent errors, enforce data integrity, and ensure that users give accurate data.

There are different types of applications in Oracle APEX validations, some of them are discussed below.

Item-Level Validations

These types of validations are applied to single-form items or fields. They ensure whether the input provided for a certain item fulfills certain conditions.
Regular Expressions: It ensures that the input matches a certain pattern. For instance: phone number, email format, etc.).
Required Value: It ensures that a field is not blank.
SQL Query: It validates data utilizing a custom SQL query. For instance, checking if a value is already present in the database.
PL/SQL Function: It executes custom PL/SQL code and validates the input.

Built-In Validations

Oracle APEX service offers multiple pre-built validations to simplify similar tasks. Examples include the following.
Length Validation: It ensures that a text input field has a specific number of characters.
Check for NULL: It ensures that a certain field is not null.
Number Range: It checks if a number falls within a certain range.

Page-Level Validations

These types of validations are applied to the complete page before it is submitted. They enable more complicated checks that depend on different form fields.
SQL Query: Use SQL Query to check the data validity on the basis of specific conditions from the database.
PL/SQL Function: These are similar to item-level validation. However, these are applied to the entire page and can assess multiple form values.

Custom validation

Using PL/SQL, developers can write custom validation logic. This enables very complex and specific validation rules. For instance:
Implementing complicated business rules.
Checking for uniqueness across several fields.

Why Data Validation Matters

Inadequate data validation at the source and poorly designed forms may be the reason why one-quarter of the time is utilized for data cleansing. Invalid email addresses, unclear questions, incorrect data formats, or poorly captured data waste a lot of time for back-end developers, data and business analysts, or DBAs.

What is Data Validation in SQL?

Data validation in SQL is the procedure for checking the quality and accuracy of data. It is usually performed before updating, adding, or processing data. Likewise, when we want to combine data from different sources we usually talk of “cleansing” the data or validating it.
While you are validating data, you can check if the data is:
Unique: must have no duplicate values
Complete: no null or blank values
Consistent with what we want. For example: a decimal present between a specific range

Creating an SQL Expression for Validation with Oracle APEX

Now that you know the basics regarding data validation and SQL expression, here is a quick guide to creating an SQL validation in Oracle APEX.
Open your Oracle APEX app and go to Page Designer.
Select the page item required to validate.
Go to the properties of the chosen page item. Open the Validation section. Click on “Add”.
Now select “SQL Expression” as your selected SQL validation type. It tells APEX that you are going to use an SQL expression for data validation.
Enter your SQL code in the SQL Expression field.
SQL expression will check if the data input is valid or not using the information given.
You can also do further customization on your Oracle APEX SQL validation by setting attributes like error message display location, error message text, and more.
You can save your validation and when a user enters the same data, it will be checked against your SQL expression. It will be valid if it meets the conditions. If not, an error message will be shown.

Testing the SQL Expression Validation

You can simply run your APEX app and enter multiple values in the respective page items to test your SQL expression validation. It will check if the conditions are matched and give error messages when needed.