Using Form and Global Variables

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.

Tip

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:

• Use indirect referencing by using NAME_IN for read and COPY for write while referring to form item variables, form system variables, form global variables, and form parameter variables, in Object Libraries or Forms Libraries. This is because direct referencing with the colon ( : ) prefix is not allowed in libraries.
• Always "create" a global variable using DEFAULT_VALUE instead of explicit assignment. This not only creates, but also initializes the global variable. The COPY built-in doesn't create a variable.
• Always use indirect referencing when referring to a global variable. This preserves access control for these global variables and also improves code portability. Using routines such as read_global_data() and write_global_data(), which indirectly reference the global variables, can enhance reusability and the sharing capability of these routines across Forms applications.
• Create all global variables in a shared library packaged procedure, and call it once in the beginning of application startup, preferably after sign-on. In addition to providing the performance benefit provided by packages, this eliminates the problem of not creating those global variables that might be necessary from some point onward in a multiform application.

  An alternative for avoiding this problem is to use DEFAULT_VALUE in each required form.