An informative-type error message results from an error caused by some missing data or insufficient data and is not an error in the strict sense. No action is necessary by default, and further processing can be resumed after the occurrence of the error.

Examples are

FRM-40350: Query caused no records to be retrieved.
FRM-40353: Query Cancelled.

However, these types of errors can be tracked and replaced by customized messages to improve readability to the end user.

An error-type error message results from an error in the strict sense, which must be rectified before further processing can resume.

Examples are

FRM-40202: Field must be entered.
FRM-40360: Cannot Query Records here.
FRM-40600: Record has already been inserted.

In Forms, few error-type error messages occur for which no action is required. A commonly occurring example is

FRM-40100: At first record.

When the cursor is in the first record in a block and the user presses the <Previous Record> or <Up> key, this error is raised and requires no action to be taken. However, the same error resulting from the built-in FIRST_RECORD is a genuine failure, and the code following this built-in is executed after the FRM-40100 error message is displayed. The latter error might result when the user presses the Enter key and the corresponding KEY-NEXT-ITEM trigger has a call to FIRST_RECORD in it:

KEY-NEXT-ITEMFIRST_RECORD;
--- <perform some action>
……

In this case, if the cursor is in the first record in the block, the code following the FIRST_RECORD built-in is executed after the error message FRM-40100 is displayed.

The only way to distinguish between an informative error message and an actual error message is by further processing, usually at runtime, without correcting the error. (Of course, the easy way is to look at the online help.)

Most error messages, whether informative or error, are assigned a severity level. The severity level of an error message determines the damage caused by that error and whether that message can be suppressed. The severity level can be one of 0, 5, 10, 15, 20, 25, and greater than 25, which includes 99. Table 5.1 shows some examples.

As an example, consider the message

FRM-40202: Field must be entered.

This message has a severity level of 15 and cannot be suppressed at all. The reason is that a mandatory item in a form should always have a value, irrespective of whether the resulting error message is suppressed.

Oracle Forms has a set of built-ins, triggers, system variables, and exceptions to handle errors.

I will elaborate on each of these as we progress through the chapter. More specifically, I will address issues such as how to trap (suppress) informative and error messages using these different methods (that is, how to use the various system variables and triggers to trap messages).

Either the FORM_SUCCESS built-in or the FORM_TRIGGER_FAILURE exception must be used to handle all Forms errors. FORM_SUCCESS is a Boolean function that evaluates to TRUE or FALSE depending on the success or failure of a Forms built-in. FORM_TRIGGER_FAILURE is an exception that has to be raised to stop further processing whenever an error occurs or FORM_SUCCESS evaluates to FALSE. The following sections describe their use in error-message handling.

The two triggers ON-ERROR and ON-MESSAGE can be used to suppress unnecessary messages of both error and informative types. Here, I will elaborate on the examples discussed earlier in the Error Type section.

Consider the following informative-type error messages:

FRM-40350: Query caused no records to be retrieved.
FRM-40353: Query Cancelled.

These messages can be suppressed by means of an ON-MESSAGE as follows:

ON-MESSAGE

IF MESSAGE_TYPE = 'FRM'AND MESSAGE_CODE IN (40350, 40353) THEN
   NULL;
ELSE
    MESSAGE(MESSAGE_TYPE||'-'||TO_CHAR(MESSAGE_CODE)||': '||MESSAGE_TEXT);
END IF;

This is how informative messages are trapped: You use the ON-MESSAGE trigger with MESSAGE_TYPE, MESSAGE_CODE, and MESSAGE_TEXT. Note the ELSE part. This is a very important part because it ensures that other genuine messages are not suppressed.

Now consider these error-type messages:

FRM-40202: Field must be entered.
FRM-40360: Cannot Query Records here.
FRM-40600: Record has already been inserted.
FRM-40100: At first record.

An ON-ERROR trigger along similar lines as the preceding example can be written with ERROR_TYPE, ERROR_CODE, and ERROR_TEXT to suppress unnecessary error-type errors:

ON-ERROR
IF ERROR_TYPE = 'FRM'AND ERROR_CODE = 40401 THEN
   NULL;
ELSE
    MESSAGE(ERROR_TYPE||'-'||TO_CHAR(ERROR_CODE)||': '||ERROR_TEXT);
END IF;

With ON-MESSAGE and ON-ERROR, you can suppress errors at any severity level because you are trying to replace the default functionality of Forms error-message handling. However, the trigger does not suffice for the purpose of capturing the error unless it is handled.

As mentioned earlier, you should always include an ELSE part, such as

ELSE
    MESSAGE(MESSAGE_TYPE||'-'||TO_CHAR(MESSAGE_CODE)||': '||MESSAGE_TEXT);

in ON-MESSAGE and ON-ERROR triggers to prevent other genuine messages from being suppressed.

FRM errors are errors raised by the failure of Forms built-ins used in trigger or program unit code. An example is the failure of the GO_BLOCK built-in. The code

GO_BLOCK('CUST_ORDERS'),

causes the error

FRM-40104: No such block CUST_ORDERS.

All errors occurring because of built-in failures should be trapped by using FORM_SUCCESS followed by a RAISE FORM_TRIGGER_FAILURE. Check form success and form failure for any built-ins by means of the following general procedure, and call this procedure after the call to the built-in:

PROCEDURE check_package_failure
IS
IF NOT FORM_SUCCESS THEN
    RAISE FORM_TRIGGER_FAILURE;
END IF;
END check_package_failure;

Note how FORM_SUCCESS is negated to cover both FORM_FAILURE and FORM_FATAL caused by internal errors (such as memory failures, and so on). Using only FORM_FAILURE and omitting the negation would have served the purpose but not fully, because FORM_FATAL would have been ignored.

The one line of code containing the GO_BLOCK call can be replaced by a line segment as follows:

GO_BLOCK('CUST_XX'),
CHECK_PACKAGE_FAILURE;

These FRM errors are caused by form, block, object, or item property failures. A very common example is the error

FRM-40202: Field must be entered.

which is caused by a null value in a mandatory form item, specified by setting the Required item property to True.

Normally, Forms causes an input lockout until the item is filled in with a value, and there is no way to continue further processing except by entering a valid value or exiting the form.

Always, as far as possible, track errors raised by form object properties by simulating them with a WHEN-VALIDATE-<object> trigger (if applicable) rather than specifying them in the object properties. Otherwise, track them in an ON-ERROR trigger. The former might seem too redundant a task, to write code for what is available ready-made, but the later sections Errors That Cannot Be Tracked by FORM_SUCCESS or FORM_FAILURE and The Inability of the ON-ERROR Trigger to Track Forms Errors explain the disastrous consequences that can result from the insufficiency of the ON-ERROR trigger and FORM_SUCCESS.

As an example, the substituted code for tracking the preceding error can be written as

WHEN-VALIDATE-ITEM

IF NAME_IN('SYSTEM.CURSOR_VALUE') IS NULL THEN
  MESSAGE('This Item Value must be entered.'),
  RAISE FORM_TRIGGER_FAILURE;
END IF;

This code can be used to validate any number of NOT NULL items in the form, irrespective of the data type.

Take a look at another typical example. Consider the error

  FRM-40505: ORACLE error, unable to perform query.

At first, this might seem the result of one of the following:

Of course, it is one of these, but how do you figure out which one?

At first sight, capturing this error might seem as trivial as getting the DBMS_ERROR_TEXT in the ON-ERROR trigger (as explained in the Errors Raised on the Server Side and Propagated to Forms section later). Fine. This helps to unearth the actual error message.

The error is a server error propagated to Forms, and it shows up as

ORA-00904: Invalid column name.

Now, what is the cause? All items in the block seem to be valid column names in the underlying base table.

It is surprising to note that the error message and the cause of it seem to be totally unrelated when you see the error message alone. It is very difficult to figure out that one peculiar cause of this error can be the failure of the Forms block property Include REF Item set to YES. The world of objects seems to cause problems for querying.

The Include REF Item property is valid only for blocks having object tables as base tables and not for blocks having items corresponding to OBJECT REFS or column objects.

After all, the error was due to incorrect setting of a block object property.

This section points out a common use of FORM_TRIGGER_FAILURE, which might seem trivial at first but is, in fact, a flaw that can sometimes prove serious. The trick of the trade when using FORM_TRIGGER_FAILURE is that it has to be RAISEd explicitly and then handled by means of an exception handler. Although an exception, it is not raised implicitly, like NO_DATA_FOUND, for example. A good example is to analyze the effect of code segments like the following:

BEGIN
  GO_ITEM(item_name);
EXCEPTION WHEN FORM_TRIGGER_FAILURE THEN
      <handle the error>
END;

Here, the exception is never raised, even when GO_ITEM fails. The correct way is to check for FORM_SUCCESS and then raise FORM_TRIGGER_FAILURE, as follows:

BEGIN
  GO_ITEM(item_name);
   IF NOT FORM_SUCCESS THEN
       RAISE FORM_TRIGGER_FAILURE;
   END IF;
EXCEPTION WHEN FORM_TRIGGER_FAILURE THEN
      <handle the error>
END;

This code illustrates using FORM_TRIGGER_FAILURE in a fool-proof way.

FORM_SUCCESS refers only to the success of any code written in Forms triggers, built-ins checked, and Forms side program units. It also refers to form failure caused by an explicit RAISE FORM_TRIGGER_FAILURE. It does not refer to errors resulting from the failure caused by a Forms object property, in which case it may not evaluate to TRUE.

Errors raised out of exceptions from triggers and program unit code can be tracked in the exception handler using SQLCODE and SQLERRM. These are SQL and PL/SQL exceptions caused by the failure of SQL DML statements or PL/SQL statements. These are tracked by SQLCODE and SQLERRM in the WHEN OTHERS exception-handling section.

Always give an exception-handling section with the WHEN OTHERS clause, irrespective of whether the trigger or program unit body contains any DML statements. Exceptions do occur because of errors in the declaration section, such as PL/SQL value or numeric errors.

EXCEPTION WHEN OTHERS THEN
      MESSAGE(TO_CHAR(SQLCODE)||''||SQLERRM);

Errors raised on the server side and propagated to Forms as a failure of integrity constraints, insufficient privileges on base tables, and so on, arise out of failure of DML statements or semantic checks on the server side. An example is

FRM-40508: ORACLE error: unable to INSERT record.

Also, a user-defined error message given in a stored program unit called from Forms, such as a message in RAISE_APPLICATION_ERROR, can result in the same message, if the same is caused during INSERT.

Although at first it appears that the error is an FRM error and is from Forms, the error is actually propagated from the Oracle server, indicating that an INSERT operation on a base table block failed in the form.

There are two ways to trap this error. You can press the <Display Error> hot key and determine what the resulting error from the database is. This approach makes sense from the programmer's point of view but not from the end user's.

You can also programmatically trap the error and replace it with a customized message that is user-friendly. This approach makes sense from either point of view.

You should use DBMS_ERROR_CODE and DBMS_ERROR_TEXT in an ON-ERROR trigger to trap errors propagated from Oracle server from Forms. For example, to trap a message in RAISE_APPLICATION_ERROR in a server-side stored program, which is being called from Forms, you can write an ON-ERROR trigger as follows:

ON-ERROR

DECLARE
    alert_button NUMBER;
    error_msg VARCHAR2(1000)
BEGIN
     error_msg := TO_CHAR(DBMS_ERROR_CODE)||''||DBMS_ERROR_TEXT;
     SET_ALERT_PROPERTY('ALERT_ERROR',ALERT_MESSAGE_TEXT, error_msg);
     alert_button := SHOW_ALERT('ALERT_ERROR'),
END;

This displays an Oracle message string followed by the user message string. You can shorten the message by eliminating the Oracle message string as follows:

error_msg :=  DBMS_ERROR_TEXT;
error_msg := SUBSTR(error_msg,1,INSTR(error_msg,'ORA-',1,2)-1));

An alternative way of suppressing unnecessary messages, both informative and error, is by using the system variable :SYSTEM.MESSAGE_LEVEL. This is a read-write system variable, unlike most others provided by Oracle Forms. The following example illustrates this concept:

To suppress the message

FRM-40100: At first record.

or

FRM-40350: Query caused no records to be retrieved.

the following pieces of code describe the technique involved:

/* For suppressing FRM-40100 */
:SYSTEM.MESSAGE_LEVEL := '5';
FIRST_RECORD;
:SYSTEM.MESSAGE_LEVEL := '0';
/* For suppressing FRM-40350 */
:SYSTEM.MESSAGE_LEVEL := '5';
EXECUTE_QUERY;
:SYSTEM.MESSAGE_LEVEL := '0';

The first assignment of :SYSTEM.MESSAGE_LEVEL to 5 is required to suppress all error messages (informative and error) at level 5 and above.

The reassignment of :SYSTEM.MESSAGE_LEVEL to 0 is required to re-enable the default behavior, that is, display all messages when occurring.

There are many Forms errors that the built-ins FORM_SUCCESS and FORM_FAILURE cannot point out. One such error is

FRM-40600: Record has already been inserted.

This can be explained by the following scenario. Consider a base table master block having a detail block. The detail block has the Primary Key property set to TRUE. This facilitates the checking of duplicate records without making explicit SELECTs to the database for each record entered. Consider a Save button that contains the following code segment in the WHEN-BUTTON-PRESSED trigger:

COMMIT;
IF FORM_SUCCESS THEN
  <perform a sequence of actions>
END IF;

The sequence of actions can be, for example, to navigate to the detail block and perform an EXECUTE_QUERY.

As soon as the Save button is pressed, Forms raises the error FRM-40600 (just mentioned) whenever it encounters two records with the same values for the primary key combination. It is surprising to note that FORM_SUCCESS evaluates to TRUE and the code under the IF statement is still executed. What an alarming situation! The immediate idea is to use FORM_FAILURE and raise FORM_TRIGGER_FAILURE as follows:

IF FORM_FAILURE THEN
 RAISE FORM_TRIGGER_FAILURE;
END IF;

Unfortunately, this fails when there are multiple duplicate records involving the same primary key values. Now, what's the work-around? Obviously, to write an ON-ERROR trigger that captures this FRM error using ERROR_TYPE and ERROR_CODE, something similar to the following:

IF ERROR_TYPE = 'FRM'AND ERROR_CODE = 40600 THEN
  MESSAGE('Duplicate Record'),
  RAISE FORM_TRIGGER_FAILURE;
ELSE
  MESSAGE(ERROR_TYPE||'-'||TO_CHAR(ERROR_CODE)||': '||ERROR_TEXT);
END IF;

Surprisingly, this also doesn't work, and the code under the IF condition is still executed. The only way to circumvent this situation is to assign a flag in the ON-ERROR trigger just described and to check explicitly for the flag in the code for the Save button before checking for FORM_SUCCESS. The modified versions of the ON-ERROR trigger and the WHEN-BUTTON-PRESSED triggers are

ON-ERROR

IF ERROR_TYPE = 'FRM'AND ERROR_CODE = 40600 THEN
   MESSAGE('Duplicate Record'),
  : Error_flag := 1;
    RAISE FORM_TRIGGER_FAILURE;
ELSE
    MESSAGE(ERROR_TYPE||'-'||TO_CHAR(ERROR_CODE)||': '||ERROR_TEXT);
 END IF;
WHEN-BUTTON-PRESSED

COMMIT;
IF (:error_flag = 1) THEN
   :error_flag := 0;
   RAISE FORM_TRIGGER_FAILURE;
END IF;
IF FORM_SUCCESS THEN
   <perform a sequence of actions>
END IF;

(Here, :ERROR_FLAG is a form variable defined in the master block or some other control block.) This code makes the situation fool-proof.

This section deals with a different category of errors, ones that the ON-ERROR trigger is incapable of handling. In the alarming situation just described, the ON-ERROR trigger really helped. The following describes a scenario in which the ON-ERROR plays no part in tracking errors raised by Forms built-ins. A rule of thumb that most Forms programmers follow is that most of the built-ins in Forms do not call for an ON-ERROR trigger, which is reserved only for FRM-type errors.

Keeping this in mind, one good use of ON-ERROR trigger has been to keep track of the errors raised at the database side from the Forms side, such as errors raised by RAISE_APPLICATION_ERROR in a stored program from the Forms side. This can be done using DBMS_ERROR_CODE and DBMS_ERROR_TEXT in an ON-ERROR trigger. This highlights the insufficiency of the ON-ERROR trigger in the sense of using DBMS_ERROR_CODE and DBMS_ERROR_TEXT outside the ON-ERROR trigger. In other words, using DBMS_ERROR_CODE and DBMS_ERROR_TEXT in ON-ERROR is not enough to track all errors resulting from the database side. Using the FORMS_DDL built-in is one such scenario.

Consider the following piece of code:

FORMS_DDL('BEGIN UPDATE employee SET sal = '||TO_CHAR(:sal)||
          ' WHERE  empno IN '||:empno_list||'; END;'),
IF FORM_SUCCESS THEN
  COMMIT;
ELSE
  MESSAGE(TO_CHAR(DBMS_ERROR_CODE)||''||DBMS_ERROR_TEXT);
  RAISE FORM_TRIGGER_FAILURE;
END IF;

If the UPDATE statement fails, the error cannot be detected by using either an exception handler or an ON-ERROR trigger, no matter if SQLCODE, ERROR_CODE, or DBMS_ERROR_CODE is used in either case. This illustrates the use of DBMS_ERROR_CODE and DBMS_ERROR_TEXT outside the ON-ERROR trigger and thus its inadequacy.

This section begins by explaining the "smart-but-not-too-smart" role played by FORM_SUCCESS or FORM_FAILURE when it comes to nested triggers (triggers within triggers). There is the EXECUTE_TRIGGER built-in, by which one can force Forms to execute the code present in the trigger given as its parameter.

Consider the following code in the WHEN-BUTTON-PRESSED trigger of a button:

EXECUTE_TRIGGER('WHEN-NEW-RECORD-INSTANCE'),
IF FORM_SUCCESS THEN
   <perform_a_sequence_of_events>
END IF;

or this code, in the WHEN-NEW-RECORD-INSTANCE of the corresponding block:

GO_BLOCK('<block_name'>;
EXECUTE_QUERY;

Even if there is no form event that implicitly triggers WHEN-NEW-RECORD-INSTANCE to fire, the code in it is still executed. In this case, the FORM_SUCCESS indicates the success of the code in the WHEN-NEW-RECORD-INSTANCE trigger, which indicates the success of the built-in. (I mean, if you have FORM_SUCCESS following a GO_BLOCK, it refers to the success of the GO_BLOCK, and similarly for most of the built-ins). Now, if the GO_BLOCK is a failure, the sequence of actions in the outermost trigger WHEN-BUTTON-PRESSED is still executed. What a negative FORM_SUCCESS!

To circumvent the problem, I modified code for WHEN-NEW-RECORD-INSTANCE as follows:

GO_BLOCK('<block_name'>
IF NOT FORM_SUCCESS THEN
   RAISE FORM_TRIGGER_FAILURE;
ELSE
    EXECUTE_QUERY;
END IF;

Now, if the GO_BLOCK is a failure, the sequence of actions in the outermost trigger WHEN-BUTTON-PRESSED is not executed. What a positive FORM_SUCCESS!

Two points should be analyzed here. First, if there is a

RAISE FORM_TRIGGER_FAILURE

in the WHEN-NEW-RECORD-INSTANCE, the FORM_SUCCESS immediately following the EXECUTE_TRIGGER returns FALSE.

Second, if there is a

GO_BLOCK('<block_name>'),

which is a failure, and there is not a RAISE FORM_TRIGGER_FAILURE in the WHEN-NEW_RECORD-INSTANCE, the FORM_SUCCESS immediately following the EXECUTE_TRIGGER returns TRUE. Remember, the EXECUTE_TRIGGER is called from an "outer" trigger, so already there is one level of nesting triggers, and FORM_SUCCESS is smart enough to return FALSE in the first case and not in the second case.

Now, what if the WHEN-NEW_RECORD-INSTANCE has a second EXECUTE_TRIGGER in it? Also, what if the trigger this is invoking has a GO_BLOCK, which fails? This creates a second level of nesting. What does FORM_SUCCESS return for the failure of the innermost GO_BLOCK? The behavior is the same as in the case of one level of nesting.

It might be surprising, but this is how FORM_SUCCESS works. The rule is that FORM_SUCCESS gives the success of the immediately preceding Forms built-in and returns FALSE if this built-in is a failure or whenever FORM_TRIGGER_FAILURE is raised. Therefore, in the case of nested triggers, in the nested trigger follow any call to built-ins by an immediate check for FORM_SUCCESS, and then RAISE FORM_TRIGGER_FAILURE when the built-in fails. It is important to RAISE FORM_TRIGGER_FAILURE because this is the only way the effect of a form failure is carried over to the outer trigger.

The modified version of the WHEN-BUTTON-PRESSED and WHEN-NEW-RECORD triggers is

EXECUTE_TRIGGER('WHEN-NEW-RECORD-INSTANCE'),
IF FORM_SUCCESS THEN
   <perform_a_sequence_of_events>
END IF;
GO_BLOCK('<block_name'>
IF NOT FORM_SUCCESS THEN
   RAISE FORM_TRIGGER_FAILURE;
ELSE
     EXECUTE_QUERY;
END IF;

This makes the situation fool-proof.

Considering the overall form, there is no way in Forms to keep track of form success. Errors in Forms can occur for any of the following reasons:

The first two types of errors can be tracked by FORM_SUCCESS, but the third type of error cannot. As an example, consider the error

FRM-40600: Record has already been inserted.

which occurs when the block property Primary key is set to TRUE for a base table block. There are many more examples like this. Of course, there is a workaround for this! (Refer to the bibliography in this book's front matter.) However, it becomes practically impossible to keep track of all such "behind-the-scenes" errors.

How nice and intelligent it would have been to have a built-in like ALL_FORM_SUCCESS that would take into account the three types of errors listed here. This would reduce a lot of code and effort by way of a simple call and also make the impossibility described here a possibility. For example, one could have a KEY-COMMIT trigger at the form level as follows:

KEY-COMMIT at form level 

COMMIT_FORM;
IF NOT ALL_FORM_SUCCESS THEN
  RAISE FORM_TRIGGER_FAILURE;
END IF;

In Forms, you have no way programmatically to keep track of whether a particular trigger fired and which trigger fired. (This inability should not be confused with Debug mode, which traces the order of execution of triggers in an interactive mode.)

Knowledge of this becomes important when developing an application that displays the execution steps or prepares the execution report of a form during runtime. This is something similar to the debugger, but the main difference is that the execution is tracked programmatically and recorded—a "programmatic debugger." In this section, I present an approach for implementing this.

The first part, tracking whether a particular trigger fired, can be achieved by means of a built-in, TRIGGER_FIRED, just like FORM_SUCCESS. Remember, no need exists for TRIGGER_SUCCESS because FORM_SUCCESS does the job. The second part, asking which trigger fired, can be regarded as the reverse of :SYSTEM.TRIGGER_ITEM, :SYSTEM.TRIGGER_RECORD, and :SYSTEM.TRIGGER_BLOCK. You could use something like SYSTEM.ITEM_TRIGGER, SYSTEM.RECORD_TRIGGER, SYSTEM.BLOCK_TRIGGER, and an additional trigger, SYSTEM.FORM_TRIGGER.

Now, using the TRIGGER_FIRED in conjunction with SYSTEM.<object>_TRIGGER—where <object> is one of ITEM, RECORD, BLOCK, FORM, and so on—you can circumvent this insufficiency. But where? You need a new trigger, WHEN-TRIGGER-FIRED, that can be defined at all levels, with the new <SYSTEM.<object>_TRIGGER returning only those triggers that make sense at that level. The need for a new trigger such as WHEN-TRIGGER-FIRED arises because not all triggers have cascading effects. Therefore, TRIGGER_FIRED cannot always be checked in one of the existing triggers .

There are two aspects to this: