How to Prevent Commit if Validation Fails in Oracle Forms

In Oracle Forms, committing data is one of the most critical operations because it permanently saves user input into the database. However, saving invalid or incomplete data can lead to inconsistencies, errors, and business rule violations. To avoid such issues, developers must ensure that all necessary validations are performed before a commit is allowed.

This article explains different techniques you can use to prevent commit if validation fails in Oracle Forms, along with examples and best practices to ensure your application maintains strong data integrity.

Why Prevent Commit on Validation Failure?

Validations are the backbone of data accuracy. Without proper validation, users might:

• Enter incomplete or incorrect values.
• Save duplicate or inconsistent records.
• Violate business rules like minimum salary, valid dates, or customer IDs.

By blocking the commit until all rules are satisfied, you ensure that only correct and consistent data enters the database.

Methods to Prevent Commit in Oracle Forms

Oracle Forms provides multiple ways to handle this requirement. Let’s look at the most common approaches.

1. Using WHEN-VALIDATE-ITEM Trigger

The WHEN-VALIDATE-ITEM trigger checks data immediately after a user leaves a field. If validation fails, you can stop further processing.

Example:

IF :EMP.SALARY < 1000 THEN
   MESSAGE('Salary must be at least 1000.');
   RAISE FORM_TRIGGER_FAILURE;
END IF;

Here, the commit will not proceed because the trigger raises FORM_TRIGGER_FAILURE.

2. Using WHEN-VALIDATE-RECORD Trigger

The WHEN-VALIDATE-RECORD trigger validates all items in a record before saving. This ensures consistency across multiple fields.

Example:

IF :ORDERS.ORDER_DATE > SYSDATE THEN
   MESSAGE('Order date cannot be in the future.');
   RAISE FORM_TRIGGER_FAILURE;
END IF;

IF :ORDERS.TOTAL_AMOUNT <= 0 THEN
   MESSAGE('Total amount must be greater than zero.');
   RAISE FORM_TRIGGER_FAILURE;
END IF;

If any condition fails, the commit process is stopped.

3. Using PRE-COMMIT Trigger

The PRE-COMMIT trigger fires just before the data is written to the database. It is one of the most effective places to perform validations that must be enforced at the transaction level.

Example:

BEGIN
   IF :CUSTOMERS.CREDIT_LIMIT < :CUSTOMERS.OUTSTANDING_BALANCE THEN
      MESSAGE('Credit limit cannot be less than outstanding balance.');
      RAISE FORM_TRIGGER_FAILURE;
   END IF;
END;

If the condition is not met, the commit is rolled back automatically.

4. Using ON-COMMIT Trigger

The ON-COMMIT trigger allows you to control the commit process explicitly. You can add validation checks and then decide whether to allow or stop the commit.

Example:

DECLARE
   v_valid BOOLEAN := TRUE;
BEGIN
   IF :EMP.HIRE_DATE > SYSDATE THEN
      MESSAGE('Hire date cannot be in the future.');
      v_valid := FALSE;
   END IF;

   IF v_valid = FALSE THEN
      RAISE FORM_TRIGGER_FAILURE;
   END IF;
END;

This ensures no invalid data gets saved.

5. Using KEY-COMMIT Trigger

The KEY-COMMIT trigger can be customized to run validations before executing the commit. If validations fail, you can cancel the commit.

Example:

BEGIN
   IF :EMP.SALARY IS NULL THEN
      MESSAGE('Salary is required before saving.');
      RAISE FORM_TRIGGER_FAILURE;
   ELSE
      DO_KEY('COMMIT_FORM'); -- Only commit if validation passes
   END IF;
END;

This gives you complete control over the commit process.

Best Practices for Validation and Commit Control

To ensure robust and user-friendly validation in Oracle Forms, follow these practices:

• Use field-level validations with WHEN-VALIDATE-ITEM for immediate checks.
• Use record-level validations with WHEN-VALIDATE-RECORD to handle dependencies between fields.
• Use transaction-level validations with PRE-COMMIT for final business checks before saving.
• Always raise FORM_TRIGGER_FAILURE when validation fails to stop the commit.
• Provide meaningful messages so users know what went wrong and how to fix it.
• Log technical details separately if needed, but keep user messages simple.

Conclusion

Preventing a commit when validation fails in Oracle Forms is essential for maintaining data accuracy, consistency, and compliance with business rules. By strategically using triggers like WHEN-VALIDATE-ITEM, WHEN-VALIDATE-RECORD, PRE-COMMIT, ON-COMMIT, and KEY-COMMIT, you can ensure that invalid data never enters the database.

With proper validation techniques and clear error messages, you can build reliable, user-friendly applications that safeguard business data effectively.