Validation is one of the most important aspects of any data entry system, and Oracle Forms provides powerful features to ensure that users enter correct and consistent data. Whether you are checking field-level inputs, validating across blocks, or applying business rules, Oracle Forms offers multiple techniques to enforce validation effectively.
This article explains the different ways to perform validation in Oracle Forms, including the use of triggers, built-ins, and program units. You will also learn best practices to ensure your forms remain user-friendly and maintain data integrity.
What is Validation in Oracle Forms?
Validation in Oracle Forms refers to the process of checking whether the data entered by the user is correct, complete, and meets specific business rules before it is accepted and saved into the database.
Validation can be applied at:
- Item level → Ensuring a single field has correct input (e.g., phone number, email, salary).
- Block level → Checking multiple fields in combination (e.g., order date should be before shipping date).
- Form level → Validating the entire form before submission.
Methods of Validation in Oracle Forms
Oracle Forms provides several techniques for performing validation. Let us look at the most commonly used methods:
1. Using Item-Level Triggers
Item-level triggers are the most direct way to validate user input.
- WHEN-VALIDATE-ITEM: Fires when the user leaves a field after entering data.
- KEY-NEXT-ITEM: Can be used to validate before moving to the next field.
- POST-CHANGE: Fires after the value in an item changes.
Example: Validate that salary cannot be less than 5000
BEGIN
IF :EMP.SALARY < 5000 THEN
MESSAGE('Salary must be at least 5000.');
RAISE FORM_TRIGGER_FAILURE;
END IF;
END;
Here, the FORM_TRIGGER_FAILURE stops the user from proceeding until valid data is entered.
2. Using Block-Level Triggers
Block-level validation is useful when rules depend on multiple items in a block.
- WHEN-VALIDATE-RECORD: Fires when the user tries to move out of the record.
Example: Ensure that hire date is earlier than end date
BEGIN
IF :EMP.HIRE_DATE > :EMP.END_DATE THEN
MESSAGE('Hire Date cannot be greater than End Date.');
RAISE FORM_TRIGGER_FAILURE;
END IF;
END;
3. Using Form-Level Triggers
Form-level validation ensures that all necessary conditions are checked before saving or exiting.
- PRE-INSERT / PRE-UPDATE: Validates data before writing to the database.
- ON-COMMIT: Allows you to intercept commit operation and perform global checks.
Example: Ensure no employee has a duplicate email before commit
DECLARE
v_count NUMBER;
BEGIN
SELECT COUNT(*) INTO v_count
FROM EMP
WHERE EMAIL = :EMP.EMAIL;
IF v_count > 0 THEN
MESSAGE('Duplicate email not allowed.');
RAISE FORM_TRIGGER_FAILURE;
END IF;
END;
4. Using Property Palette
Oracle Forms also allows validation through the Property Palette by setting:
- Required → Ensures the field cannot be left blank.
- Maximum Length → Restricts number of characters.
- Format Mask → Restricts the format of data (e.g., date format).
These simple validations reduce the need for custom PL/SQL code.
5. Using Program Units for Reusable Validation
For complex business rules, it is best to write validation logic in PL/SQL program units so it can be reused across forms.
Example:
PROCEDURE validate_salary(p_salary NUMBER) IS
BEGIN
IF p_salary < 5000 THEN
MESSAGE('Salary must be at least 5000.');
RAISE FORM_TRIGGER_FAILURE;
END IF;
END;
This procedure can be called from multiple triggers instead of repeating the same logic everywhere.
6. Using Alerts for User-Friendly Messages
Instead of plain messages, you can use alerts for better user interaction.
Example:
DECLARE
al_id ALERT;
btn NUMBER;
BEGIN
al_id := FIND_ALERT('ERROR_ALERT');
SET_ALERT_PROPERTY(al_id, ALERT_MESSAGE_TEXT, 'Salary must be at least 5000.');
btn := SHOW_ALERT(al_id);
RAISE FORM_TRIGGER_FAILURE;
END;
This gives a professional look and ensures users notice the validation message.
Best Practices for Validation in Oracle Forms
- Keep it simple → Perform item-level validation where possible instead of waiting until form submission.
- Provide clear error messages → Tell users exactly what went wrong and how to fix it.
- Avoid duplication → Place common validation logic in program units.
- Balance strictness and usability → Do not over-validate to the point where forms become frustrating to use.
- Test thoroughly → Ensure validation logic works in all scenarios (insert, update, commit).
Conclusion
Performing validation in Oracle Forms is essential to maintain data integrity and enforce business rules. You can validate at the item, block, or form level using triggers, properties, program units, and alerts. By applying these techniques effectively and following best practices, you can ensure that your Oracle Forms applications remain reliable, user-friendly, and accurate.
