How to Handle Errors in Oracle Forms Using ON-ERROR

A form that fails should say why, in words its user understands. Oracle Forms has its own messages for everything, such as FRM-40202: Field must be entered or FRM-40508: ORACLE error: unable to INSERT record, but they are written for developers more than for users.

This guide shows how Oracle Forms 14.1.2 reports events, how a form takes over that reporting with ON-ERROR and ON-MESSAGE, how to turn database errors into clear messages by constraint name, and how code checks whether built-ins succeeded and handles exceptions.

Sample Form for This Guide

The examples and screenshots use the sample form CH26_ERRORS from the Oracle Forms code repository on GitHub. Download it, open it in Forms Builder, and connect as CAREWELL to follow along.

The forms run against the CareWell Clinic sample schema, which you install first.

Messages, Alerts, and Errors

When a second message arrives before the first was seen, for example two in the same trigger, Forms shows the first in a dialog the user must acknowledge and the second on the message line. MESSAGE(text, ACKNOWLEDGE) always shows the dialog. Alerts are covered in how to show alerts using SHOW_ALERT.

Message Levels

Every Forms message has a type (FRM, or ORA for a database message shown directly), a number, and a severity level: 0 for pure information, then 5, 10, 15, 20, and 25 as they grow more serious. Messages above 25 cannot be suppressed.

:SYSTEM.MESSAGE_LEVEL hides the messages whose level is at or below its value; setting it to '5' hides the FRM-40404 message of POST, for example. Set it back to '0' right after the statement it is meant for, because messages hidden by mistake are errors nobody sees. :SYSTEM.SUPPRESS_WORKING, set to 'TRUE', hides the Working... message that long operations show.

ON-ERROR and ON-MESSAGE

Two form-level triggers replace Forms' reporting:

• ON-ERROR fires instead of showing an error.
• ON-MESSAGE fires instead of showing an informative message.

Inside them, built-ins describe the event.

Syntax:

error_type return varchar2          -- 'FRM' or 'ORA'
error_code return number            -- 40508
error_text return varchar2          -- 'ORACLE error: unable to INSERT record.'
dbms_error_code return number       -- the database error behind it: -2291
dbms_error_text return varchar2     -- 'ORA-02291: integrity constraint ... violated'
message_type, message_code, message_text     -- the same for ON-MESSAGE

Example: a Shorter Save Message

The sample errors form's ON-MESSAGE shortens the message of a successful save, and shows every other message as Forms would.

Example (ON-MESSAGE trigger on the form):

if message_code = 40400 then        -- FRM-40400: Transaction complete: ... saved.
  message('Saved.');
else
  message(message_type || '-' || to_char(message_code) || ': ' || message_text);
end if;

A trigger that handles only some events must show the others itself, as this one does in its ELSE. Whatever it does not display is lost.

Turn Database Errors into Clear Messages

When the database rejects a row, Forms reports FRM-40508: ORACLE error: unable to INSERT record, or 40509 and 40510 for updates and deletes, and the user has to open Display Error to see why. DBMS_ERROR_CODE and DBMS_ERROR_TEXT give the reason to your code.

The package CW_ERR, in the library CW_LIB, translates the errors of the appointments table, and the form's ON-ERROR only calls it.

Example (ON-ERROR trigger on the form):

cw_err.on_error;

Library CW_LIB, package CW_ERR (specification):

package cw_err is
  -- turns an error into a message for the user; call it from ON-ERROR
  procedure on_error;
  -- the constraint named in a database error: 'APPOINTMENTS_PATIENT_FK'
  function constraint_of(p_text varchar2) return varchar2;
end cw_err;

Library CW_LIB, package CW_ERR (body):

package body cw_err is
  function constraint_of(p_text varchar2) return varchar2 is
  begin
    return regexp_substr(p_text, '\(\w+\.(\w+)\)', 1, 1, null, 1);
  end constraint_of;

  procedure on_error is
    v_db   number := dbms_error_code;                -- the database error, or 0
    v_name varchar2(128) := constraint_of(dbms_error_text);
    v_text varchar2(400);
  begin
    if error_type = 'FRM' and error_code in (40508, 40509, 40510) then
      v_text := case
        when v_db = -1    then 'This record already exists.'
        when v_db = -1400 then                     -- cannot insert NULL into (...."COLUMN")
          'Enter a value for '
          || regexp_substr(dbms_error_text, '"(\w+)"\)', 1, 1, null, 1) || '.'
        when v_name = 'APPOINTMENTS_PATIENT_FK' then 'There is no patient with this number.'
        when v_name = 'APPOINTMENTS_DOCTOR_FK' then 'There is no doctor with this number.'
        when v_name = 'APPOINTMENTS_DUR_CK' then 'An appointment lasts 5 to 240 minutes.'
        when v_db = -2292 then 'Other records still refer to this one.'
      end;
    end if;
    if v_text is null then                           -- no translation: Forms' own message
      v_text := error_type || '-' || to_char(error_code) || ': ' || error_text;
    end if;
    message(v_text);
    raise form_trigger_failure;
  end on_error;
end cw_err;

Key Messages on Constraint Names

A database error names the constraint that was violated, such as ORA-02291: integrity constraint (CAREWELL.APPOINTMENTS_PATIENT_FK) violated - parent key not found, and CONSTRAINT_OF extracts it with a regular expression. Keying the messages on constraint names, rather than on error numbers alone, tells a missing patient from a missing doctor. Naming every constraint in the schema is what makes that possible.

A new appointment for patient 99999 shows the first translation.

The other errors of the same record:

Two details come from these tests. The last line is Forms' own error, shown unchanged because CW_ERR has no translation for it. And PRE-INSERT took a new number from the sequence at every attempt to save: 51342, 51343, 51344. The failed inserts were rolled back, but sequence numbers are never given back, so keys taken from a sequence have gaps, and code must not expect them to be consecutive.

Keep ON-ERROR Short and Safe

ON-ERROR fires for every error of the form, including those raised by its own triggers, such as FRM-40735 for an unhandled exception. Keep it short and safe, because an error inside ON-ERROR has no one left to report it. Checking values before the save, so these errors rarely happen, is covered in how to validate data in Oracle Forms.

Errors in Code

Did the Built-in Succeed?

Most built-ins do not raise exceptions when they fail. GO_BLOCK to a block that refuses to be entered, or COMMIT_FORM that cannot save, show their error and return. Code that continues must check.

Syntax:

form_success return boolean      -- the last built-in succeeded
form_failure return boolean      -- it failed
form_fatal   return boolean      -- it failed with a fatal error

Example:

commit_form;
if not form_success then
  raise form_trigger_failure;      -- stop here: the error is on the message line
end if;

FORM_SUCCESS describes the last built-in called, so test it right after the call; a MESSAGE in between changes it. When the real question is whether anything is left unsaved, test :SYSTEM.FORM_STATUS instead.

Exceptions in Triggers

PL/SQL exceptions work in triggers as anywhere else. FORM_TRIGGER_FAILURE is the one to raise when a trigger must fail: Forms stops the event that fired the trigger, without any message of its own. An exception that no handler catches also fails the trigger, and Forms shows a message like this one, which a global variable gives when it overflows its 4,000 characters.

Output:

FRM-40735: WHEN-BUTTON-PRESSED trigger raised unhandled exception ORA-06502.

In an exception handler, SQLCODE and SQLERRM describe the exception. A WHEN OTHERS handler that only hides the error makes the problem invisible; if it catches everything, it should say something and raise FORM_TRIGGER_FAILURE.

Forms' PL/SQL lacks RAISE_APPLICATION_ERROR and some SQL features. Stored procedures called from a form can use them, and the errors they raise reach the form like any database error, as explained in how to write PL/SQL in Oracle Forms. For older examples, see how to handle errors in Oracle Forms.

Conclusion

Oracle Forms reports through messages, alerts, and errors, and :SYSTEM.MESSAGE_LEVEL hides messages at or below a level. ON-ERROR and ON-MESSAGE replace Forms' reporting, with ERROR_CODE, ERROR_TEXT, DBMS_ERROR_CODE, DBMS_ERROR_TEXT, and the MESSAGE_ built-ins describing the event; translate database errors by constraint name, and still show the errors you have no translation for. In code, test FORM_SUCCESS right after built-ins that may fail, raise FORM_TRIGGER_FAILURE to fail a trigger, and never let WHEN OTHERS hide an error silently.