Handling Errors in Oracle Forms

Errors are inevitable in any application, especially when users interact with data entry forms. In Oracle Forms, errors can occur due to invalid input, database constraints, missing values, or application logic. To build reliable and user-friendly applications, it is crucial to understand how to handle errors effectively.

This article explains how error handling works in Oracle Forms, the types of errors you may encounter, and different techniques to trap and manage them using triggers, built-ins, and alerts.

Why Error Handling is Important in Oracle Forms

Error handling ensures that when something goes wrong, the user receives clear and helpful information without crashing the application. Some key reasons include:

• Data Integrity → Prevents invalid or incomplete data from being saved.
• User Experience → Provides clear messages instead of system-generated errors.
• Application Stability → Stops unexpected failures and allows graceful recovery.
• Business Rules Enforcement → Ensures users follow organizational policies and workflows.

Common Types of Errors in Oracle Forms

Errors in Oracle Forms can be classified into different categories:

1. Validation Errors → When user input does not meet validation rules (e.g., blank mandatory field, wrong data type).
2. Database Errors → When database constraints are violated (e.g., unique constraint, foreign key violation).
3. Runtime Errors → Errors caused by triggers, PL/SQL blocks, or incorrect logic in program units.
4. Application Errors → Custom error conditions defined in your code (e.g., duplicate entries, invalid date ranges).

Techniques for Handling Errors in Oracle Forms

Oracle Forms provides several ways to handle errors, ensuring users are guided with proper responses instead of cryptic system messages.

1. Using the ON-ERROR Trigger

The ON-ERROR trigger is the most commonly used for global error handling. It traps runtime errors and allows you to replace system messages with custom messages.

Example:

DECLARE
   err_code NUMBER := ERROR_CODE;
   err_msg  VARCHAR2(200) := ERROR_TEXT;
BEGIN
   IF err_code = 40508 THEN
      MESSAGE('You must enter a value in this field.');
   ELSE
      MESSAGE('An unexpected error occurred: ' || err_msg);
   END IF;
   RAISE FORM_TRIGGER_FAILURE;
END;

Here, error code 40508 is handled with a user-friendly message for required fields.

2. Using the ON-MESSAGE Trigger

The ON-MESSAGE trigger intercepts system messages and lets you display customized messages.

Example:

BEGIN
   MESSAGE('Custom: ' || MESSAGE_TEXT);
   RAISE FORM_TRIGGER_FAILURE;
END;

This is useful when you want to override or log standard messages.

3. Using Exception Handling in PL/SQL Blocks

When writing PL/SQL in triggers or program units, you can use EXCEPTION blocks to manage errors.

Example:

BEGIN
   INSERT INTO EMP (EMP_ID, NAME) VALUES (:EMP.EMP_ID, :EMP.NAME);
EXCEPTION
   WHEN DUP_VAL_ON_INDEX THEN
      MESSAGE('Employee ID already exists.');
      RAISE FORM_TRIGGER_FAILURE;
   WHEN OTHERS THEN
      MESSAGE('Unexpected error: ' || SQLERRM);
      RAISE FORM_TRIGGER_FAILURE;
END;

This approach is helpful for database and logic-related errors.

4. Using FORM_TRIGGER_FAILURE to Stop Processing

Whenever you handle errors, you can use the built-in FORM_TRIGGER_FAILURE to stop further execution and prevent invalid data from being saved.

5. Using Alerts for Error Messages

Instead of simple messages, you can display errors in alerts for better visibility.

Example:

DECLARE
   al_id ALERT;
   btn   NUMBER;
BEGIN
   al_id := FIND_ALERT('ERROR_ALERT');
   SET_ALERT_PROPERTY(al_id, ALERT_MESSAGE_TEXT, 'Invalid entry. Please check your input.');
   btn := SHOW_ALERT(al_id);
   RAISE FORM_TRIGGER_FAILURE;
END;

This ensures that users cannot miss the error message.

Best Practices for Error Handling in Oracle Forms

• Identify common error codes and handle them with clear messages.
• Use ON-ERROR for global handling and specific triggers for business validations.
• Leverage alerts for critical errors instead of plain text messages.
• Log errors in a custom table for debugging and audit purposes.
• Keep messages simple and actionable so users know exactly what to do next.
• Do not expose database error text directly to end-users, as it may confuse them.

Conclusion

Handling errors in Oracle Forms is vital for building robust and user-friendly applications. By using triggers like ON-ERROR and ON-MESSAGE, adding exception handling in PL/SQL, and displaying errors through alerts, you can ensure that users receive clear guidance when something goes wrong. Effective error handling not only improves usability but also safeguards data integrity and keeps applications stable.