Chapter 20

Advanced Forms Programming I

In this chapter, you will explore the following areas:

• Programming function keys
• Responding to mouse events
• Controlling windows and canvases programmatically
• Controlling data block relationships

This chapter, and the two that follow it, introduce you to the world of advanced Form Builder programming. This chapter begins by explaining how you can reprogram your application’s response to function keys pressed by the user. It then explores the world of mouse events, showing how you can create triggers to respond to any mouse action the user performs. Next, you will see how to control windows, and the canvases they display, programmatically. Finally, you will learn about the triggers, program units, and system variables that work together to control data block relationships. The OCP Exam 4 contains a large number of questions in these subject areas—32 percent of the final score—so you will want to pay special attention to this chapter, studying it thoroughly until you know the answers to every one of its exercises and chapter questions. Of course, you have studied every chapter before this just as thoroughly, right?

In this section, you will cover the following points related to programming function keys:

• Redefining function keys
• Determining when KEY_ triggers should be used

Most applications currently being written are menu driven but that doesn’t mean the client computer’s function keys should be ignored. The Forms Runtime program already has predefined functionality for many of the function keys; you can reprogram that functionality so the keys perform functions related to your application—or so they do nothing at all. A complete treatment of this topic is beyond the scope of this book, so this introduction provides the key concepts on what function-key triggers do and when to use them.

You can easily assign items in custom menus to function keys, as well as create entirely new functionality using KEY-Fn triggers. Form Builder provides 10 of these function-key triggers, numbered KEY-F0 through KEY-F9. It also provides a KEY-OTHERS trigger that is automatically associated with every key that can have a key trigger associated with it but does not. The KEY-OTHERS trigger is useful for disabling function keys that are not germane to your application. When you use any of these techniques to assign functionality to function keys, the default functionality of the keys is overridden.

The default function key assignments are shown in Table 20-1. The triggers available for each of these assignments are shown in Table 20-2.

There are certain tasks for which key triggers are especially useful, and also certain situations in which they cannot be used. Key triggers are a good choice when you need to enable and disable function keys dynamically, disable a function key’s default behavior, or have a single keystroke perform multiple actions. Disabling default behavior and performing multiple actions are two of the more interesting capabilities, because they cannot be duplicated by a menu command like most function key actions can. Regarding their limitations, the KEY-Fn triggers are ignored in Edit mode, and the KEY-OTHERS trigger is ignored while the user is responding to a Forms Runtime prompt or viewing a list of values, the help or Keys screen, or an error screen. Also, certain function keys cannot be redefined, because they are often executed by the Forms Runtime program’s user-interface management system or the Oracle terminal, rather than by Form Builder. Table 20-3 lists these static function keys.

In this section, you will cover the following points about responding to mouse events:

• Introduction to mouse events
• Causing a form module to respond to mouse movement
• Causing a form module to respond to mouse button actions

Everything a user can do with a mouse in an application constitutes an event in Form Builder, and therefore can fire specific event triggers. In this section, you will be introduced to these triggers, learn what causes them to fire, and see how they can be used.

Form Builder comes with a variety of triggers that can fire in response to actions the user takes with the mouse. Table 20-4 lists the mouse triggers available and describes the action that causes each trigger to fire. To assist your programming efforts, Form Builder also provides a group of system variables appropriate for use in mouse triggers. These allow you to determine which mouse button the user pressed, and where the mouse pointer was when the button was pressed. Table 20-5 lists these mouse system variables and describes the data each one returns. When the Forms Runtime program executes the code in a mouse trigger, it initializes and populates any variables in the code just before running the code. Because of this, it is best to use mouse system variables only in mouse trigger code in order to ensure that the contents of the variables are up to date when a mouse trigger fires. You will see how to apply these triggers and system variables in the topics that follow.

Three of the Form Builder mouse event triggers fire in response to mouse movement: WHEN-MOUSE-ENTER, WHEN-MOUSE-LEAVE, and WHEN-MOUSE-MOVE. All of these triggers can be defined at the form, block, or item level. When defined at the Form level, they are active in any canvas or item in the form. When defined at the block level, they are active in any item in the block. When defined at the item level, they are active only when the mouse enters, leaves, or moves within the item. These triggers fire only in response to mouse movement, so they will not fire if the user enters a canvas via menu commands. Similarly, pressing a mouse button will not fire any mouse-movement triggers, because pressing a mouse button does not constitute movement. There are other triggers to respond to mouse button presses, and they will be explored in the next topic.

Four of the Form Builder mouse event triggers fire in response to mouse button presses: WHEN-MOUSE-DOWN, WHEN-MOUSE-UP, WHEN-MOUSE-CLICK, and WHEN-MOUSE-DOUBLECLICK. All of these triggers can be defined at the form, block, or item level. When used in conjunction with the SET_APPLICATION_PROPERTY built-in, the triggers enable you to change your application’s cursor in real time. For instance, the following code in a WHEN-MOUSE-DOWN trigger would alter the mouse cursor depending on what mouse button the user has pressed:

DECLARE
V_MOUSE_BUTTON_PRESSED varchar2(1);
BEGIN
V_MOUSE_BUTTON_PRESSED := :system.mouse_button_pressed;
if V_MOUSE_BUTTON_PRESSED = '1' then
set_application_property(cursor_style, 'BUSY');
else
set_application_property(cursor_style, 'CROSSHAIR');
end if;
END;

The action of the WHEN-MOUSE-DOWN trigger could be reversed by the following code in a WHEN-MOUSE-UP trigger:

set_application_property(cursor_style, 'DEFAULT');

In this section, you will cover the following points about controlling windows and canvases programmatically:

• Creating trigger code to interact with windows
• Controlling windows programmatically
• Controlling canvases

Almost by definition, a sophisticated application is likely to consist of multiple canvases and multiple windows. If your application has multiple canvases and multiple windows, you need to be able to control them programmatically. This section will get you started in the right direction, and will cover the topic to the degree necessary to answer the exam questions in this area.

Form Builder offers four different triggers that can fire in response to window-oriented events. The first window trigger, WHEN-WINDOW-ACTIVATED, fires when a window is opened, or when an open window receives focus. The second window trigger, WHEN-WINDOW-DEACTIVATED, fires when an open window loses focus. The third window trigger, WHEN-WINDOW-RESIZED, fires when a window’s size is changed by the user or programmatically. The last window trigger, WHEN-WINDOW-CLOSED, fires when the user executes the window-close command intrinsic to their operating system.

Because windows themselves cannot have triggers, window-oriented triggers are defined at the next higher level in the object hierarchy: the Form level.

Form Builder provides quite a few built-ins enabling you to control your windows via PL/SQL code. These are shown in Table 20-6. Perhaps the most versatile of these is SET_WINDOW_PROPERTY, which allows you to set any of the properties you normally see in a window’s Property Palette, such as window size, position, and title. There are also built-ins to manipulate a window’s visual status: SHOW_WINDOW, HIDE_WINDOW, MOVE_WINDOW, and RESIZE_WINDOW.

Because Form Builder allows you to assign more than one content canvas to a window, you need some way to specify programmatically which content canvas a window should display. This task is accomplished using the REPLACE_CONTENT_VIEW built-in. Table 20-7 shows this and other built-ins useful for controlling canvases via PL/SQL code.

In this section, you will cover the following points about controlling data block relationships:

• Definition of block coordination
• Creating and modifying relations
• Characteristics of relation-handling code
• Implementing a coordination-type toggle

This section introduces the Form Builder features related to coordinating master and detail data blocks in a master/detail relationship. You can use these techniques to work with non-Oracle databases, control when a detail block queries the database for records, and customize your application in other ways.

As you probably know, creating a relationship between two blocks requires that the blocks share one or more columns in common. More specifically, the contents of the master block’s primary-key column must be referred to by one or more foreign-key columns in the detail block. When you define a master/detail relationship between such tables, Form Builder ensures that the detail block always displays records related to the current record in the master block, and that new detail records will automatically be assigned to the current master record. This is called block coordination, and it relies on triggers. Each time the user does something that causes the master record to change—moving up or down in the master data block, for example, or deleting the current master record—the Forms Runtime program considers it a coordination-causing event. Internally, it goes to the detail data block, flushes existing detail records, makes the next master record current, and then repopulates the detail block with a new query.

There are many facets of this process that you can control in order to customize your application’s relation-handling capabilities, or to augment them with new features. These facets will be covered in upcoming topics.

A relation is a logical Form Builder object that defines how one master data block and one detail data block are related. The relation object is located under the master data block, in a node named Relations. You can create a relation in two ways: with the Data Block Wizard, or manually. Not surprisingly, using the Data Block Wizard is much less work. To see this approach, you will create a new form module and create a relation within it. Start now by creating a new form module and naming it RELATIONSHIPS. Then, use the Data Block Wizard to create a data block for the DEPARTMENT table. Include all the table’s columns as database items. When given the option, elect to Just create the data block. Then, invoke the Data Block Wizard again to create a second data block, this time for the EMPLOYEE table. Include all the table’s columns as database items. When presented with the Wizard’s master/detail page, ensure that the Auto-join data blocks option is enabled and then click on the Create Relationship button. Identify the master data block for the relation by selecting the DEPARTMENT data block from the dialog box that appears, and then click the OK button. Open the Detail Item list and select DEPARTMENT_ID from the available items. Your Data Block Wizard screen should now look like Figure 20-1. Click on the Next button to continue. Elect to Just create the data block and click on the Finish button. Your screen should now look like Figure 20-2. Notice that in addition to the EMPLOYEE data block, Form Builder created a form-level ON-CLEAR-DETAILS trigger, two block-level ON- triggers under the master data block, a DEPARTMENT_EMPLOYEE relation under the master data block, and three form-level program units. These objects work together to perform block coordination.

To see the properties available for a relation, open the Property Palette for the DEPARTMENT_EMPLOYEE relation. Under the Functional node, you will see that the detail block is specified (there is no need to specify the master block, since this relation is owned by the master block), as well as the SQL join condition that synchronizes the blocks. Beneath that you will see the Delete Record Behavior property. This property controls how the Forms Runtime program responds when the user attempts to delete a master record that has related detail records. The first available setting for this property, Cascading, specifies that when a master record is deleted, any related detail records will also be deleted when the action is committed to the database. The second available setting, Isolated, allows master records to be deleted without deleting any related detail records—which could result in detail records with invalid values in their foreign-key columns pointing back to the master table. The third available setting, Non Isolated, causes the Forms Runtime program to refuse to delete a master record if it has related detail records. This is the default setting for this property. One fascinating facet of the Delete Record Behavior property is that changing it causes the master data block’s triggers to be re-created. Setting the Delete Record Behavior property to Cascading causes Form Builder to generate PRE-DELETE and ON-POPULATE-DETAILS triggers immediately. Setting the property to Isolated causes the PRE-DELETE trigger to disappear, leaving only the ON-POPULATE-DETAILS trigger. Setting the property to Non Isolated causes the generation of an ON-CHECK-DELETE-MASTER trigger to accompany the ON-POPULATE-DETAILS trigger. The relationship between the settings for this property and the presence of data-block-level triggers is depicted in Table 20-8.

The next property, Prevent Masterless Operations, defines whether the user can insert detail records or query the detail data block when there is no current master record. The next two properties work together to specify when the detail block gets populated after a coordination-causing event. When the Deferred property is set to No, the detail block is repopulated immediately after any coordination-causing event in the master block. When the Deferred property is set to Yes, the next property—Automatic Query—comes into play. When the Automatic Query property is set to Yes, the detail block will be repopulated as soon as the user navigates into the detail block. This allows the user to change master records often without being subject to potential delays from having the detail block repopulate each time. When the Automatic Query property is set to No, the user must navigate into the detail block and execute a query to populate the detail block.

There may be times when you want to write your own block-coordination code: when you are using non-Oracle data sources, for instance, or when your application has especially long relation chains. There are three types of objects involved in relation handling: triggers, program units, and system variables.

Form Builder automatically generates up to three relation-handling triggers when you set a relation’s Delete Record Behavior. There is always an ON-CLEAR-DETAILS trigger at the Form level, and an ON-POPULATE-DETAILS at the master block level. In addition, implementing cascading delete requires the use of a PRE-DELETE trigger; this trigger deletes related detail records; this must be accomplished before the master record is deleted, which is why a PRE-DELETE trigger is used. If the relation’s Delete Record Behavior property is set to Non Isolated, an ON-CHECK-DELETE-MASTER trigger at the master block level is used to check whether detail records exist before a master record is deleted. Table 20-9 shows each of these triggers, what they do, where to use them, and what behavior they produce.

The relation-handling triggers generated by Form Builder rely on the presence of three program units that are also created automatically when you define a relation. In your RELATIONSHIPS form module, you will find them in the form-level node named Program Units. The first program unit, CHECK_PACKAGE_FAILURE, is used by the other two program units to determine whether a PL/SQL statement completed successfully. The second program unit, CLEAR_ALL_MASTER_DETAILS, flushes detail records from the detail block when called by the ON-CLEAR-DETAILS trigger. The third, QUERY_MASTER_DETAILS, fetches a new batch of detail records into the detail block when called by the ON-POPULATE-DETAILS trigger. Table 20-10 lists these program units in an easy-to-reference format.

Three of the system variables available in Form Builder are used in relation-handling code. All three are called by the CLEAR_ALL_MASTER_DETAILS program unit, which itself is called by the ON-CLEAR-DETAILS trigger. The first system variable is SYSTEM.BLOCK_STATUS, which determines whether any of the records in a block are new or changed. For instance, if you wanted to build a trigger that commits records in a block before the block is cleared, you could place the following code in a KEY-CLRBLK trigger:

if :system.block_status = ‘NEW’ then
commit_form;
elsif :system.block_status = ‘CHANGED’ then
commit_form;
end if;
clear_block;

The second system variable is SYSTEM.COORDINATION_OPERATION, which returns one of 20 possible results identifying what type of coordination-causing event fired the ON-CLEAR-DETAILS trigger. It works together with the third system variable, SYSTEM.MASTER_BLOCK, which returns the name of the master block driving the relation. Table 20-11 lists each system variable and identifies where and why each is used.

To use these system variables, you create local variables in your code and store the values of the system variables into the local variables. An excellent example of this is found in the CLEAR_ALL_MASTER_DETAILS program unit (which is called by the ON-CLEAR-DETAILS trigger). This program unit’s code follows, with boldface print used to identify the relevant system variables and the local variables used to store their values:

PROCEDURE Clear_All_Master_Details IS
mastblk VARCHAR2(30); -- Initial Master Block Causing Coord
coordop VARCHAR2(30); -- Operation Causing the Coord
trigblk VARCHAR2(30); -- Cur Block On-Clear-Details Fires On
startitm VARCHAR2(61); -- Item in which cursor started
frmstat VARCHAR2(15); -- Form Status
curblk VARCHAR2(30); -- Current Block
currel VARCHAR2(30); -- Current Relation
curdtl VARCHAR2(30); -- Current Detail Block

FUNCTION First_Changed_Block_Below(Master VARCHAR2)
RETURN VARCHAR2 IS
curblk VARCHAR2(30); -- Current Block
currel VARCHAR2(30); -- Current Relation
retblk VARCHAR2(30); -- Return Block
BEGIN
curblk := Master;
currel := Get_Block_Property(curblk, FIRST_MASTER_RELATION);
WHILE currel IS NOT NULL LOOP
curblk := Get_Relation_Property(currel, DETAIL_NAME);
IF ( Get_Block_Property(curblk, STATUS) = 'CHANGED' ) THEN
RETURN curblk;
ELSE
retblk := First_Changed_Block_Below(curblk);
IF retblk IS NOT NULL THEN
RETURN retblk;
ELSE
currel := Get_Relation_Property(currel, NEXT_MASTER_RELATION);
END IF;
END IF;
END LOOP;

RETURN NULL;
END First_Changed_Block_Below;

BEGIN
mastblk := :System.Master_Block;
coordop := :System.Coordination_Operation;
trigblk := :System.Trigger_Block;
startitm := :System.Cursor_Item;
frmstat := :System.Form_Status;

IF coordop NOT IN ('CLEAR_RECORD', 'SYNCHRONIZE_BLOCKS') THEN
IF mastblk = trigblk THEN
IF frmstat = 'CHANGED' THEN
curblk := First_Changed_Block_Below(mastblk);
IF curblk IS NOT NULL THEN
Go_Block(curblk);
Check_Package_Failure;
Clear_Block(ASK_COMMIT);
IF NOT ( :System.Form_Status = 'QUERY'
OR :System.Block_Status = 'NEW' ) THEN
RAISE Form_Trigger_Failure;
END IF;
END IF;
END IF;
END IF;
END IF;

currel := Get_Block_Property(trigblk, FIRST_MASTER_RELATION);
WHILE currel IS NOT NULL LOOP
curdtl := Get_Relation_Property(currel, DETAIL_NAME);
IF Get_Block_Property(curdtl, STATUS) <> 'NEW' THEN
Go_Block(curdtl);
Check_Package_Failure;
Clear_Block(NO_VALIDATE);
IF :System.Block_Status <> 'NEW' THEN
RAISE Form_Trigger_Failure;
END IF;
END IF;
currel := Get_Relation_Property(currel, NEXT_MASTER_RELATION);
END LOOP;

IF :System.Cursor_Item <> startitm THEN
Go_Item(startitm);
Check_Package_Failure;
END IF;

EXCEPTION
WHEN Form_Trigger_Failure THEN
IF :System.Cursor_Item <> startitm THEN
Go_Item(startitm);
END IF;
RAISE;

END Clear_All_Master_Details;

Earlier in this section you learned about the relation properties that control when a detail block is populated: Deferred and Automatic Query. Because these settings can effect your application’s response time, as well as overall network and database demand, there may be times when you want to change the properties dynamically, or allow the users to do so. This can be accomplished using the SET_RELATION_PROPERTY built-in. To make a detail block repopulate immediately each time the user changes focus to a new master record, you can implement code like the following:

procedure make_coordination_immediate( RELATION_NAME varchar2 ) is
RELATION_ID relation;
BEGIN
RELATION_ID := find_relation(RELATION_NAME);
set_relation_property(RELATION_ID, deferred, property_false);
set_relation_property(RELATION_ID, autoquery, property_false);
END;

Similarly, you could use code like this to cause the detail block to repopulate only when the user navigates into it:

procedure make_coordination_immediate( RELATION_NAME varchar2 ) is
RELATION_ID relation;
BEGIN
RELATION_ID := find_relation(RELATION_NAME);
set_relation_property(RELATION_ID, deferred, property_true);
set_relation_property(RELATION_ID, autoquery, property_true);
END;

The corollary command built-in, GET_RELATION_PROPERTY, can return a variety of useful information, including whether coordination is automatic or deferred, the names of the master and detail blocks, and the names of master and detail relations.

In this chapter, you have covered a substantial amount of information on advanced forms programming. The topics covered include programming function keys, responding to mouse events, controlling windows and canvases programmatically, and controlling data block relationships. The material in this chapter comprises about 32 percent of material tested on OCP Exam 4.

The first area you covered was programming function keys. You can use KEY-Fn triggers to replace the default functionality of individual function keys, and the KEY-OTHERS trigger to disable any function key that can have a trigger assigned to it but does not. Key triggers are a good choice when you need to replace a function key’s default behavior, enable and disable function keys dynamically, or have a single keystroke perform multiple actions.

The next area you covered was responding to mouse events. The triggers that respond to mouse movement are WHEN-MOUSE-ENTER, WHEN-MOUSE-LEAVE, and WHEN-MOUSE-MOVE. These triggers fire only in response to mouse movement; entering a canvas via menu commands will not fire the mouse-movement triggers. They can be defined at the form, block, or item level. When defined at the Form level, they are active in any canvas or item in the form. Defined at the block level, they are active in any item in the block. Defined at the item level, they are active when the mouse enters, leaves, or moves within the item. There are also triggers that respond to presses of the mouse buttons: WHEN-MOUSE-DOWN, WHEN-MOUSE-UP, WHEN-MOUSE-CLICK, and WHEN-MOUSE-DOUBLECLICK. These can also be defined at the form, block, or item level. When used in conjunction with the SET_APPLICATION_PROPERTY built-in, the mouse-button triggers enable you to change your application’s cursor in real time. Form Builder also provides a collection of mouse-oriented system variables enabling your code to determine which mouse button the user pressed, and where the mouse pointer was when the button was pressed. The system variables that return information about the mouse’s location are SYSTEM.MOUSE_CANVAS, SYSTEM.MOUSE_ITEM, SYSTEM.MOUSE_RECORD, SYSTEM.MOUSE_RECORD_OFFSET, SYSTEM.MOUSE_X_POS, and SYSTEM.MOUSE_Y_POS. The system variables that return information about what mouse button was pressed are SYSTEM.MOUSE_BUTTON_PRESSED and SYSTEM.MOUSE_BUTTON_MODIFIERS.

You next explored controlling windows and canvases programmatically. Form Builder offers four triggers that can fire in response to window-oriented events: WHEN-WINDOW-ACTIVATED, which fires when a window is opened or receives focus; WHEN-WINDOW-DEACTIVATED, which fires when an open window loses focus; WHEN-WINDOW-RESIZED, which fires when a window’s size is changed by the user or programmatically; and WHEN-WINDOW-CLOSED, which fires when the user executes the window-close command intrinsic to their operating system. Because windows themselves cannot have triggers, window-oriented triggers are defined at the next higher level in the object hierarchy: the Form level. Form Builder also provides quite a few built-ins enabling you to control your windows, including FIND_WINDOW, SHOW_WINDOW, HIDE_WINDOW, MOVE_WINDOW, RESIZE_WINDOW, GET_WINDOW_PROPERTY, and SET_WINDOW_PROPERTY. The SET_WINDOW_PROPERTY built-in allows you to set window properties such as window size, position, and title. For controlling canvases, Form Builder offers another group of built-ins: GET_CANVAS_PROPERTY, SET_CANVAS_PROPERTY, SHOW_VIEW, and REPLACE_CONTENT_VIEW. The REPLACE_CONTENT_VIEW and SHOW_VIEW built-ins allow you to select a window’s current content canvas programmatically; the former built-in replaces the prior canvas, while the latter allows the prior canvas to remain.

The final area you covered was controlling data block relationships. This began with an explanation of block coordination, which is the process Form Builder uses to ensure that a detail block always displays records related to the current record in the master block, and that new detail records are automatically assigned to the current master record. Block coordination occurs when the user moves to a next or prior record in the master data block, or deletes a master record; these actions are called coordination-causing events.

A master data block is connected to its detail blocks with logical objects called relations. A relation defines how one master data block and one detail data block are related. The relation object is owned by the master data block. Once created, a relation’s Delete Record Behavior property enables you to control how the Forms Runtime program responds when the user attempts to delete a master record that has related detail records. The options are Cascading, which causes any related detail records to be deleted before a master record is deleted; Isolated, which allows master records to be deleted without deleting any related detail records; and Non Isolated, which causes the Forms Runtime program to refuse to delete a master record if it has related detail records. Changing this property’s setting causes Form Builder to re-create the master data block’s triggers, because it is the triggers that enforce the settings when the application is run. The next relation property is Prevent Masterless Operations, which defines whether the user can insert detail records or query the detail data block when there is no current master record. The timing of block coordination is controlled by the next two properties: Deferred and Automatic Query. The Deferred property can be considered the more extensive of the two properties because it determines whether population of the detail block is deferred at all. If set to Yes, then the Automatic Query button specifies whether the detail block will be populated automatically when the user enters it, or only after the user enters it and executes a query within it. You can set these properties programmatically using the SET_RELATION_PROPERTY built-in.

If your application requires that you write your own relation-handling code, you will utilize specific triggers, program units, and system variables. In the area of triggers, there will always be an ON-CLEAR-DETAILS trigger at the Form level to clear detail-block records, along with an ON-POPULATE_DETAILS trigger owned by the master data block. In addition, you can use an ON-CHECK-DELETE-MASTER trigger at the master block level to check whether detail records exist before a master record is deleted, or a PRE-DELETE trigger to implement a cascading delete from master records down to detail records. The relation-handling triggers created by Form Builder rely on the presence of three program units that are also generated automatically when you create a relation. The first, CHECK_PACKAGE_FAILURE, is used by the other two program units to determine whether a PL/SQL statement completed successfully. The second, CLEAR_ALL_MASTER_DETAILS, flushes detail records from the detail block when called by the ON-CLEAR-DETAILS trigger. The third, QUERY_MASTER_DETAILS, fetches a new batch of detail records into the detail block when called by the ON-POPULATE-DETAILS trigger. The CLEAR_ALL_MASTER_DETAILS program unit employs a number of relation-oriented system variables: SYSTEM.BLOCK_STATUS, which determines whether any of the records in a block are new or changed; SYSTEM.COORDINATION_OPERATION, which identifies what type of coordination-causing event fired the coordination trigger; and SYSTEM.MASTER_BLOCK, which returns the name of the master block driving the relation.