Handling Errors

Now you will learn how to handle errors raised by each of the four categories.

FORM_SUCCESS and FORM_TRIGGER_FAILURE

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.

Suppressing Errors Using ON-ERROR and ON-MESSAGE Triggers

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.

Tip

The rule of the thumb is to raise FORM_TRIGGER_FAILURE for error-type error messages except those that are being suppressed.

 

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.

Errors Raised from the Failure of Forms Built-Ins

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;

Errors Raised from the Failure of Forms Object Properties

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- 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:

• The base table doesn't exist.
• One or more of the base table items do exist, such as an invalid column name .
• Either one or both of the WHERE and ORDER BY clauses is wrong.
• Insufficient privileges.

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.

The Proper Use of FORM_TRIGGER_FAILURE

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 RAISE d 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

 

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

 

END;

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

Tip

Don't expect Forms to do what you can do yourself! Explicitly RAISE FORM_TRIGGER_FAILURE.

 

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 from Exceptions from Triggers and Program Unit Code

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

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 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));

Suppressing Errors Using :SYSTEM.MESSAGE_LEVEL

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 is required to re-enable the default behavior, that is, display all messages when occurring.

Tip

The assigning of :SYSTEM.MESSAGE_LEVEL to a value suppresses all messages at that level or above. The only exception is the one specified earlier: Not all messages with severity levels less than or equal to 25 can be suppressed. Error messages can be suppressed only for errors causing damage that Forms can do away with.

 

Tip

When you want to suppress a message, use :SYSTEM.MESSAGE_LEVEL or the conventional method, with ON-ERROR. When you want to replace a message, use the conventional method with ON-ERROR. The conventional method is always superior because it eliminates the risk of suppressing unknown messages.