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.
Typical Usage
|
Trigger
|
Screen setup
|
When-New-Form-Instance
When-New-Block-Instance
When-New-Record-Instance
|
Selecting records
|
Pre-Query
Post-Query
|
Validation
|
When-Validate-Item
When-Validate-Record
Pre-Commit
Post-Forms-Commit
|
Programatically populating values to appear on the screen
|
Post-Query
When-Create-Record
|
Programatically populating values in the record
|
Pre-Insert
Pre-Update
|
Programatically populating values in related records
|
Post-Insert
Post-Update
|
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;
