The purpose of this document is to provide an overview of Trigger usage within Oracle Forms and to discuss some lessons learned.

Introduction

Oracle Forms is an easy to use environment for developing GUI Oracle database applications using PL/SQL¹. My experience is that the most common cause of problems in Forms code is the incorrect use of Form Triggers.

Processing Overview

Oracle Forms uses an event driven programing model² where Triggers take the place of Event Handlers. After the form has been shown on the screen, triggers will fire as the user enters data. Forms has a single event queue and a single execution thread. This means that a trigger must complete before the next trigger will be processed. When the user saves the data, Forms will verify that the data is valid and commit it on the database. Any exceptions will cause Forms to rollback the changes.

What to avoid

Having a Push Button with the following code in the When-Button-Pressed Trigger may initially appear to work correctly but this will certainly lead to problems later.

When-Button-Pressed:

BEGIN

— First, perform some validation

IF check_item() AND check_record() THEN

null;

ELSE

MESSAGE(‘Invalid value’);

RAISE Form_Trigger_Failure;

END IF;

— OK, now Commit

commit_form;

— after a successful Commit, run the report

IF Form_Success THEN

run_report;

END IF;

END;

When the button is pressed the form will be validated, the data committed and the report will run; which is as expected. What is wrong with this code? The problem is that there are other ways of committing the data without pressing this button and the code will only execute if the button is pressed. For example, if the Accept key is pressed the data will be committed to the database but neither the validation or the report will be executed.

How should this code have been written? By using Triggers as Oracle intended, the code is not only easier to read and maintain, it will perform as expected.

When-Button-Pressed:

— start Commit processing, validating first

commit_form;

When-Validate-Item:

BEGIN

— Validate item

IF check_item() THEN

null;

ELSE

MESSAGE(‘Invalid item value’);

RAISE Form_Trigger_Failure;

END IF;

END;

When-Validate-Record:

BEGIN

— Validate the record

IF check_record() THEN

null;

ELSE

MESSAGE(‘Invalid record value’);

RAISE Form_Trigger_Failure;

END IF;

END;

Post-Database-Commit:

— after a successful Commit, run the report

run_report;

The advantage of this code structure is that no matter how the Commit is initiated the validation and report functions will always behave as expected.

For this reason triggers such as When-Button-Pressed and Key-xxx (eg: Key-Commit) should contain very little code, as explained in the following from the Forms Reference Manual.

Key Triggers have a one-to-one relationship with specific keys. That is, the trigger fires when the operator presses a specific key or key-sequence.

Remember that most GUI applications offer operators more than one way to execute commands. For instance, an operator might be able to execute a query by clicking a button, selecting a menu command, or pressing the [Execute Query] key.

In such situations, it would be a mistake to place all of your application logic in a key trigger that might never fire. Similarly, in any mouse-driven application, you cannot rely entirely on key triggers for navigational keys like [Next Item] and [Next Block]. Because operators can navigate with a mouse, they may choose not to use these keys for navigation, and the associated triggers would not be fired.

Commit processing

The processing flow when a Commit occurs always seems to cause confusion. The basics of the Commit processing are as follows.

• Each item that has been changed is validated (When-Validate-Item).

• Each record with a changed item is validated (When-Validate-Record).

• Pre-Commit trigger occurs after validation and before any data is written to the database.

• Any records that are marked as INSERT are inserted onto the database (Pre-Insert / Post Insert).

• Any records that are marked as CHANGED are updated onto the database (Pre-Update / Post-Update).

• Any records that are marked as DELETED are deleted from the database (Pre-Delete / Post-Delete).

• Post-Forms-Commit trigger occurs after Forms has written the data to the database but before the database Commit.

• Post-Database-Commit trigger occurs after the database Commit.

This description has been simplified but covers the main points. Refer to the Oracle Forms Processing Flowcharts for the exact details.

Common uses for Triggers

This is a list of some common programming tasks and the Trigger to be used. This is not an extensive list and other triggers are useful; refer to the Oracle Forms manuals for details.

Common Issues

It is best practice to follow the documented usage for each type of Trigger. The usual symptoms of incorrect Trigger usage is code executing at the wrong time (or not executing at all). Needing to specify IF statements to determine the current state (eg: Get_Record_Property) can be a sign of incorrect Trigger usage; review the code any time this occurs. For example checking for INSERT status is not necessary in a Pre-Insert Trigger.

Best practice is to have very little code in the Trigger, instead most of the code should be in Program Units. This allows the code to be easily reused.

Form_Success sometimes did not work correctly in Oracle 6i; I am unsure if this has been fixed in later versions. The problem was that records with invalid values would fail validation but Form_Success would be true and the incorrect value committed to the database.

Validation

While it is possible to put validation logic just about anywhere in the Forms code, having most (if not all) of the validation logic in either When-Validate-Item or When-Validate-Record triggers would be best practice. The normal sequence of events is that Forms will continue processing towards committing the data unless an exception is raised (eg: Form_Trigger_Failure), when a Rollback will occur and processing will cease.

As a general principle it is best to have validation occur as soon as possible. Avoid having the user enter a whole screen of data, then finding that the first field is invalid.

A common problem is that items loaded from the database are considered valid and the When-Validate-Item and When-Validate-Record triggers only fire when an item has been changed. This means that invalid values that are on the database will be considered valid. The record or form level validation described below can be used to check for this.

Item Validation

Basic item validation can be specified with the properties for the item. Lowest value, highest value, lists of values and value required can be specified. The When-Validate-Item trigger can also be coded to programatically validate the item. Avoid combining both of these methods as this can cause significant programmer confusion.

Item validation only occurs on changed items; record or form validation should be used to check for invalid values on the database.

Be careful with the use of When-Validate-Item and cross referencing as this can cause a situation where to correct a data entry error the user needs to get out of the field but the validation will not let them. Use When-Validate-Record for validating cross references. This problem can also occur with the use of the Required property. While this is not a logic error, it can cause a poor user experience.

Record Validation

Often most real world validation will occur at the record level as frequently all of the information required for validation is not available until the record is complete. It is usually best to validate mandatory fields at this point rather than the item level.

Record validation only occurs for records that have had at least one item changed; form validation may be required to check for invalid values on the database.

Form Validation

There are two other triggers which are sometimes necessary for validation, these are: Pre-Commit and Post-Forms-Commit.

Pre-Commit trigger:- is useful as it can be used for form level validation. This trigger can be treated like a When-Validate-Form trigger (which is not provided by Oracle). It always fires before the Forms Commit process. No modifications have been posted to the database at this point. Any validations requiring complex PL/SQL coding (such as Cursor loops) placed here will see the database before the data is changed.

Post-Forms-Commit:- trigger is the last chance for validation before the data is committed to the database. The main advantage of this trigger is that Forms has posted all of the Insert, Update, Deletes to the database but has not yet performed the database Commit. Any validations requiring complex PL/SQL coding (such as Cursor loops) on the changed data should be placed here.

Restricted Built-in Functions

Many of the Triggers (mainly Pre and Post Triggers) prohibit restricted built-in functions. Most of these restricted functions involve navigation within the form (eg: Go_Item). Usually trying to use a restricted function in a Trigger that prohibits them is a sign of a possible logic error.

Sometimes it is necessary to use a restricted function at an unusual point in the processing flow; this may require placing the function in what would appear to be an incorrect Trigger. Great care should be taken if this is required.

One way of overcoming this limitation is to use a Timer that fires almost immediately. Timers are not restricted so can execute all functions. Note that the When-Timer-Expired Trigger will not start execution until after the current trigger has completed no matter how short the Timer delay.

A restricted trigger:

DECLARE

nav_timer timer;

BEGIN

…

nav_timer := Find_Timer(‘NAV’);

IF NOT Id_Null(nav_timer) THEN

Delete_Timer(nav_timer);

END IF;

nav_timer:= CREATE_TIMER(‘NAV’, 80 ,NO_REPEAT);

…

END

When-Timer-Expired:

DECLARE

expired_timer VARCHAR2(20);

BEGIN

expired_timer:=GET_APPLICATION_PROPERTY(TIMER_NAME);

IF (expired_timer = ‘NAV’) THEN

go_item(‘user_block.item_name’);

END IF;

END;

This sort of work around is usually required to either perform navigation from a restricted trigger or use a restricted function following a Commit (and the Post-Database-Commit trigger is restricted). The following code fragments provide a general solution.

Go_Item_Later:- procedure navigates to the item after the restricted trigger completes.

Post_Commit:- procedure runs after the Post-Database-Commit trigger and is unrestricted.

A restricted trigger:

…

— navigate to item after trigger completes

Go_Item_Later(‘user_block.item_name’);

…

PU:Go_Item_Later(item_name IN VARCHAR2)

BEGIN

— set control variable with name of item to navigate to

:control.timer_item := item_name;

— start Navigation timer

Start_Timer(‘ITEM’);

END;

Post-Database-Commit

— start Post-Commit timer

Start_Timer(‘POST’);

PU:Start_Timer(timer_name IN VARCHAR2)

nav_timer timer;

BEGIN

— check if timer exists and delete it

nav_timer := Find_Timer(timer_name);

IF NOT Id_Null(nav_timer) THEN

Delete_Timer(nav_timer);

END IF;

— create new timer

nav_timer:= CREATE_TIMER(timer_name, 80 ,NO_REPEAT);

END;

When-Timer-Expired:

DECLARE

expired_timer VARCHAR2(20);

BEGIN

expired_timer:=GET_APPLICATION_PROPERTY(TIMER_NAME);

IF (expired_timer = ‘ITEM’) THEN

go_item(:control.timer_item);

END IF;

IF (expired_timer = ‘POST’) THEN

Post_Commit;

END IF;

END;

PU:Post_Commit

— This program unit runs after the Post-Database-Commit trigger completes

null;

Resources

The basic functionality of Oracle Forms has not changed significantly since Release 4.5, so a lot of the older documentation is still relevant and includes information that has not been reproduced in later documentation. In particular the processing flowcharts can be found in the Release 4.5 Reference manuals.

The major difference between the versions is in deployment and stability. The earlier versions supported character mode and client/server deployment, whereas currently only web deployment is available.

Website

Oracle Forms http://www.oracle.com/technology/products/forms/index.html

Reference

Oracle9i Forms Developer, Reference Guide, Part No. A97289-01 (March 2002)

Oracle Forms Developer – Form Builder Reference, Volume 1 & 2 – Release 6i, Part No: A73074 01 (January, 2000)

Forms Reference Manual, Release 4.5, Volume 1, Part No. A32509–2 (1994)

Forms Reference Manual, Release 4.5, Volume 2, Part No. A32510–2 (1994)

Forms Developer’s Guide, Release 4.5, Part No. A32505-2 (1994)

White Papers

Oracle 10gR2 AS Forms Services – Best Practices for Application Development (2006)

Oracle Forms Server Troubleshooting (2000)

About the author

Paul Shipley has been a software developer for over twenty years and has done a bit of just about everything. He is a co-author of Photoshop Elements 2: Zero to Hero (ISBN 1 904344 23 2).

— End of Document —