Type of validation in Oracle APEX
Introduction
Validations in Oracle APEX play a crucial role in ensuring data accuracy, consistency, and business rule enforcement. Before any data is submitted to the database, validations check whether the entered values meet the required conditions.
Without proper validations, applications may store incorrect or incomplete data, leading to serious issues later.
Why Validations are Important
-Prevent invalid data entry
-Maintain database integrity
-Improve user experience
-Enforce business rules
-Reduce errors in processing
Types of Validations in Oracle APEX
Oracle APEX validations can be broadly categorized into **four main types**:
1)Item Level Validation
What is it?
Item level validation is applied to a single page item (field).
When to Use
When validation depends on only one field
For simple checks like required fields or format validation
Examples
* Email must be valid
* Salary must be greater than 0
* Mobile number must have 10 digits
Example (PL/SQL Expression)
:P1_SALARY > 0
2)Page Level Validation
What is it?
Page level validation is applied to the **entire page** and can involve multiple items.
When to Use
When validation depends on **more than one field**
BEGIN
IF :P1_START_DATE > :P1_END_DATE THEN
RETURN FALSE;
END IF;
RETURN TRUE;
END;
3)Application Level Validation
What is it?
Application level validations are used across **multiple pages in the application**.
When to Use
When the same validation rule applies everywhere
4)Interactive Grid / Tabular Form Validation
What is it?
Used when working with **multiple rows of data** (like tables or grids).
When to Use
When validating row-wise data
Example (PL/SQL)
:QUANTITY >= 0
Built-in Validation Types in Oracle APEX
Inside the above categories, Oracle APEX provides several **validation methods**:
1. NOT NULL Validation
Ensures that a field is not empty
Example:
Name field must be filled
2. SQL Expression
Uses SQL condition to validate data
:P1_AGE >= 18
3. PL/SQL Function (Return TRUE/FALSE)
Used for complex logic
BEGIN
IF :P1_MARKS >= 40 THEN
RETURN TRUE;
ELSE
RETURN FALSE;
END IF;
END;
4. Regular Expression Validation
Used to validate patterns
Example: Email format
^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}$
5. Item Comparison
Compares values between two items
Example:
Password = Confirm Password
6. Exists / Not Exists (SQL Query)
Checks if data exists in database
Example:
Username already exists or not
When Do Validations Execute?
Validations run during:
* Page Submit
* Before processing (like insert/update)
* Based on defined conditions
Conclusion
Validations in Oracle APEX are essential for building **robust and reliable applications**. By properly using item-level, page-level, application-level, and grid validations, developers can ensure high-quality data and smooth user experience.
Mastering validations will help you create **professional, error-free APEX applications**.
Comments
Post a Comment