Proper use of form and global variables is indispensable for any Forms programming application, for both performance reasons and programming ability. When used improperly, they result in poor performance, as well as unintelligent programming. This section begins with tips on how to use form variables repeatedly.
To get the best results when working with variables, you should always assign a form or global variable to a local PL/SQL variable before using it, if it has to be used repeatedly in the same trigger or PL/SQL program unit.
Consider the following piece of code:
BEGIN IF :order_items.qty_on_hand > 10000 THEN p_return_excess_quantity; ELSIF :order_items.qty_on_hand = 0 THEN p_order_quantity; END IF; END;
Here, :ORDER_ITEMS.QTY_ON_HAND is a Forms variable (in fact, a Forms item) that is referenced twice. Also, each time it is referenced, it has the same value. At first sight, this code seems all right. However, there is a hidden drawback here that hinders the performance, if not badly , at least to some extent. If this code is all the code in the trigger or PL/SQL program unit, the performance problems might not be so visible (it doesn't mean that there isn't performance; in fact, there is better performance), but think of one having the form variable repeated a large number of times.
You can make a small modification (remember, small doesn't necessarily mean less) to the code as follows :
DECLARE v_qty_on_hand NUMBER; BEGIN v_qty_on_hand := NVL(:order_items.qty_on_hand,-1); IF v_qty_on_hand > 10000 THEN p_return_excess_quantity; ELSIF v_qty_on_hand = 0 THEN p_order_quantity; END IF; END;
The trick here is, PL/SQL is executed on the server side, and there will be trips from Oracle Server to Forms and back each time the PL/SQL engine encounters a Forms variable. Assigning the form item to a local PL/SQL variable costs only one trip. The PL/SQL engine then uses the value of the local variable on the server side, even if this local variable is repeated several times. The same holds true for a global variable.
Tips for Working with Variables
Here are some additional tips that will help as you work with variables:
An alternative for avoiding this problem is to use DEFAULT_VALUE in each required form.
Advanced GUI Development: Developing Beyond GUI
Advanced Forms Programming
Object-oriented Methods in Forms
Intelligence in Forms
Additional Interesting Techniques
Working with Trees
Oracle 8 and 8i Features in Forms Developer