Chapter 21

Advanced Forms Programming II

In this chapter, you will cover the following areas of advanced forms programming:

• Building multiple-form applications
• Defining data sources
• Working with record groups

This chapter introduces you to some exciting areas of the Form Builder design process. It starts by describing a variety of different ways to produce multiple-form applications, and explaining how to pass data between forms when they are opened. It then proceeds to a discussion of alternative data sources you can use to create a data block. The last section is on record groups—tabular data structures you can use not only to feed LOVs, but also to pass data to other forms as well as to graphs and reports. Overall, the contents of this chapter comprise about 27 percent of OCP Exam 4 test content.

In this section, you will cover the following points related to building multiple-form applications:

• Different ways to invoke forms
• Building robust multiple-form transactions
• Passing data between forms using parameter lists

In Chapter 17, you were introduced to the concept of multiple-form applications. The section you are about to read provides much more detailed information about the features and options you have available when you create multiple-form applications. You will explore different ways to invoke additional forms, learn how to move between forms with assuredness, and see how to pass information from one form to another when a new form is opened.

Form Builder provides three separate built-ins capable of invoking forms: CALL_FORM, NEW_FORM, and OPEN_FORM. The standard built-in used for most multiple-form work is OPEN_FORM. Its syntax is shown here, with each available option included and set to its default value:

open_form ('form_name', activate, no_session, no_share_library_data, paramlist_id);

The first option, ACTIVATE, specifies that the newly opened form will immediately receive focus. The second option, NO_SESSION, specifies that the newly opened form will share the database connection used by the calling form, as opposed to opening its own database connection. The NO_SHARE_LIBRARY_DATA option specifies that any libraries attached to the newly opened form will not share data with matching libraries attached to other open forms. The last argument specifies the internal ID (or the name) of the parameter list you want to pass to the opened form.

If your application requires that you open a new form modally so that it is the only form that can receive focus until it is closed, use the built-in CALL_FORM. Its syntax is shown here, with each available option included and set to its default value:

call_form ('form_name', hide, no_replace, no_query_only, no_share_library_data, paramlist_id);

The first option, HIDE, causes the Forms Runtime program to make the calling form invisible to the user while the called form is active. The second option, NO_REPLACE, tells the Forms Runtime program to continue using the default menu module of the calling form, even if the called form has a default menu module of its own. The third option, NO_QUERY_ONLY, specifies that users should be able to insert, update, and delete records in the called form, as opposed to only being able to fetch them.

If you want to open a new form and close the one that was active, use the built-in NEW_FORM. Its syntax is shown here, with each available option included and set to its default value:

new_form ('form_name', to_savepoint, no_query_only, no_share_library_data);

The first option, TO_SAVEPOINT, specifies that changes that have not been committed will be rolled back to the calling form’s last savepoint.

Table 21-1 compares the salient characteristics of each approach to opening forms.

When one form opens another and the calling form has pending transactions that have not been posted, the called form is opened in post-only mode. This means that the calling form cannot commit any transactions or perform a rollback; it can only post to the database whatever changes the user makes while in the called form. If the user makes changes in the called form and then exits, the Forms Runtime program displays an alert asking if they want to post their changes before returning to the calling form.

When you create the ability to open multiple forms, you will soon be faced with a new array of needs: navigating between forms in controlled ways, determining within code what form is active and what form called it, and creating additional database connections without asking the user for their logon information again, for instance. This topic explores Form Builder features that satisfy those needs.

There are three built-ins enabling you to move between open forms: GO_FORM, NEXT_FORM, and PREVIOUS_FORM. The most versatile is GO_FORM, because it allows you to jump to any form, not just the next or previous one. Its syntax is shown here:

go_form('form_name');

To support applications in which multiple instances of the same form are opened, you can specify the destination form using its internal Oracle ID, instead of its (now nonunique) name.

To supply your PL/SQL code with information about the application’s environment, you can use the GET_APPLICATION_PROPERTY built-in. To see an example of this, open a form module of your choosing, display a canvas in the Layout Editor, add a pushbutton to it, and enter the following code for its WHEN-BUTTON-PRESSED trigger:

DECLARE
V_FORM_NAME varchar2(80);
BEGIN
V_FORM_NAME := get_application_property(current_form_name);
message ('Form: '||V_FORM_NAME);
END;

You can also use the GET_APPLICATION_PROPERTY built-in to provide a wealth of other system information. The most relevant of these items are listed in Table 21-2.

Using Form Builder, you can have one form module call another and pass parameters to the called form. There are two ways to do this: create the parameters under the Parameters nodes in both form modules, or create the parameters in just the called form, and write PL/SQL code in the calling form that constructs the parameter list and then calls the second form. Either way, the called form must have the parameters defined already, so they must be established in the called form at design time.

To create parameters in either the calling form or the called form, click on its Parameters node and then click on the Create button. Give the new parameter a descriptive name; set its datatype and maximum length; and in the calling form, establish an initial value if the parameter is going to have a fixed value such as the name of the calling form. The parameter name, datatype, and length properties must be identical in both the calling form and the called form.

To create a parameter list dynamically in the calling form, use the CREATE_PARAMETER_LIST built-in. Invoking this built-in returns a numeric value that is the internal ID for the parameter list created; this internal ID can be used in further references to the parameter list. Once the empty parameter list is created, you add parameters to it using the ADD_PARAMETER built-in. Finally, you invoke the called form and include the parameter list’s internal ID as the final command argument. Here is a sample bit of code showing how these built-ins work together:

DECLARE
PARAM_LIST_ID paramlist;
BEGIN
PARAM_LIST_ID := create_parameter_list('list_name');
add_parameter(PARAM_LIST_ID, 'parameter_name', text_parameter, 'parameter value');
open_form('form_name', PARAM_LIST_ID);
END;

Note that the ADD_PARAMETER built-in includes the argument text_parameter. This tells Form Builder that the value being passed is a VARCHAR2 text string; these have a maximum length of 255 characters. The alternative argument is data_parameter, which is a VARCHAR2 string containing the name of a record group in the calling form. This is useful for providing data when calling the Graphics Runtime or Reports Runtime programs, a subject that will be addressed later in the chapter.

If you choose to establish parameters in the calling program permanently by adding them under the Parameters node, those parameters will become part of a parameter list named DEFAULT that is kept internally by all form modules. To send this parameter list to a called form, include its name in the calling command, as shown here:

open_form('form_name', 'default');

In this section, you will cover the following points about defining data sources:

• Introduction to different data source types
• Selecting appropriate data sources for data blocks

This section provides a comparison of the features and shortcomings of each data source type, along with tips for selecting the appropriate data source for different application needs. These criteria will help you when the time comes to decide what data source type to explore further—after you have passed your certification exam.

So far in this book, every form data block you have created has been based on a database table. You can also create data blocks based on a database view, a from clause query, a stored procedure, or a transactional trigger. In the case of the stored procedure, there are two options for how the data is provided: data is returned to the data block either in the form of a ref cursor, which is a pointer to a server-side cursor that is populated by a select statement, or as a table of records, which is an array-like structure sent to the client computer containing every record returned by the procedure.

The most common source for a data block is still a database table. However, there are definite advantages to other data source types: increased performance, reduced network traffic, increased control and security, and shifting of processing burden to the database server. Using an alternative data source is easy to do: for instance, if you have a stored procedure that you would like to use as the basis for a data block, you can simply select Stored Procedure in the Data Block Wizard’s Type screen, identify the procedure in the following Procedure screen, and then select columns as you would from a database table. These steps are shown in Figures 21-1 and 21-2.

Selecting a from clause query as a data source type is almost as easy. You create a new data block manually, set its Query Data Source Type property to FROM clause query, and place the appropriate select statement in its Query Data Source Name property.

There are many factors to consider when deciding which data source type to use for a data block. Take a look at Table 21-3, which provides a comprehensive comparison of each data source type’s advantages and shortcomings, and you will get an idea of the number of decisions that go into selecting the right data block source type. You can simplify the selection process and narrow your choices quickly by asking a few key questions about the reason you are considering other data source types, and the data block’s requirements. For instance, if you want to minimize the network traffic required to fetch large numbers of records, a stored procedure returning a table of records would be your first choice because it reduces network transactions to the bare minimum. If you need to get data from multiple data blocks and don’t want to have the DBA (or yourself) create any new objects on the server, a from clause query is the way to go. If your users will need to update the records in the data block, your choices are limited to data source types of table, view, or stored procedure returning a table of records. If you are interacting with a non-Oracle database, a transactional trigger is likely to be your best choice. If the data block needs Query By Example capabilities, your choice of data source types is restricted to table or view. And if you want to implement array processing, you will limit your data source selection to table, view, from clause query, or a stored procedure returning a ref cursor. Study Table 21-3 until you know it inside and out, and it will serve you well on your exam.

In this section, you will cover the following points about working with record groups:

• Creating record groups at design time
• Creating and modifying record groups programmatically
• Building dynamic list items by using record groups
• Applying record groups in other useful ways
• Using a global record group to communicate between forms

A record group is a Form Builder object that makes tabular (column/row) data available to your application. Be default, a record group is specific to the form module in which it is defined. You have worked with a record group already, when you created a DEPARTMENT_LOV list earlier in the book. Now it is time to learn more about record groups and what they can do for you. There are three types of record groups:

• Query record group, which is based on a SQL select statement. This provides a functionality similar to a database view, with two added benefits: the select statement that produces the record group can be dynamically created at run time, and you don’t need to add a view to the database.
• Nonquery record group, which can also be created and populated dynamically at run time but has no underlying select statement.
• Static record group, whose structure and contents are defined at design time and cannot change as the application runs.

To create a query record group, click on the Record Groups node and then click on the Create button. You will see the New Record Group dialog box, as shown in Figure 21-3. Ensure that the Based on the Query below… option is selected and then enter the following SQL select statement:

select d.department_name, e.last_name, e.first_name, e.salary
from department d, employee e
where d.department_id = e.department_id
order by d.department_name, e.salary;

Then click on the OK button to continue. You will see a new record group added beneath the Record Groups node. Change the record group’s name to DEPT_EMP_SALARY. The record group is not currently populated; it can be populated at run time using the POPULATE_GROUP built-in, as shown here:

populate_group ('group_name');

To create a static record group, click on the Record Groups node and then click on the Create button. Select the Static Values option and then click on the OK button. You will be presented with a dialog box similar to Figure 21-4, asking you to define the names, datatypes, lengths, and values for your static record group. For this example, create a sample STATE record group, as depicted in Figure 21-5. Then, click on the OK button to complete the process. Change the name of the new record group to STATE.

Two types of record groups can be created programmatically: query record groups and nonquery record groups. There are two built-ins that create record groups: CREATE_GROUP and CREATE_GROUP_FROM_QUERY. The syntax of these commands will be covered later; what you need to know to pass this portion of the certification exam is which built-in to use for a specific task.

The CREATE_GROUP built-in creates nonquery record groups. Because a nonquery record group has no select statement to provide it with column information, you must tell it what columns to use and what to populate them with. There are two ways to do this. If your data is coming from a queryable data source, you can use the POPULATE_GROUP_WITH_QUERY built-in to specify a select statement that will define the record group’s structure and contents. Populating the record group in this way essentially turns the nonquery record group into a query record group, a useful trick when you have a record group you thought wasn’t going to change during the application’s lifetime and—surprise!—it is going to change after all. If the values for the record group are going to be generated programmatically, the process of populating the record group is, of course, more involved. You first define the record group’s columns using one ADD_GROUP_COLUMN built-in for each column. You then add and populate rows using one ADD_GROUP_ROW built-in for each row, along with one SET_GROUP_CHAR_CELL or SET_GROUP_NUMBER_CELL built-in for each item in the row you wish to populate.

The CREATE_GROUP_FROM_QUERY built-in creates query record groups. Once created, this type of group can be populated by simply executing the POPULATE_GROUP built-in. If you find you need to dynamically change the select statement that underlies an existing query record group, you can do so programmatically with the POPULATE_GROUP_WITH_QUERY built-in. This replaces the record group’s select statement for the duration of that runtime session. This replacement works as long as the new select statement returns records with the same structure as the original select statement, and varies only in the content of the records returned.

When you experimented with creating a List of Values earlier in this book, a record group was automatically created to feed data to the LOV. Every LOV needs a record group. However, you can create record groups without creating LOVs, and then later creating an LOV to view the records in an existing record group. The LOV can be considered a viewing portal into the record group’s rows; each time the LOV is opened, the attached record group is populated automatically. You can also change the contents of the LOV dynamically, or even make the list show an entirely different record group.

To create an LOV based on an existing record group, click on the LOVs node in the form module, then click on the Create button. Select the Existing Record Group radio choice, then click on the Select button to choose the record group on which to base the LOV. Then, click on the OK button to dismiss the Select dialog box and click on the next OK button to complete the process. Finally, rename the LOV so its name reflects its purpose.

To populate an LOV with values from a two-column record group created at runtime, use the POPULATE_LIST built-in. Its syntax follows:

populate_list('list_name', 'record_group_name');

If you want to modify an LOV so it shows values from a record group that wasn’t necessarily created at run time, or that has more than two columns, use the SET_LOV_PROPERTY built-in. Its syntax is

set_lov_property('lov_name', GROUP_NAME, 'record_group_name');

Generally, record groups remain within the scope of the form module that owns them. You can, however, specify that a record group have global scope, so that it is visible to all forms in an application. You can even make the contents of a record group available to other Developer/2000 products, such as Graphics Builder and Report Builder. This gives you the ability to alter the contents of a graphic or report at run time.

To make a record group available to all form modules, include the GLOBAL_SCOPE parameter in the built-in that creates the group. Thus, the syntax would be

create_group('record_group_name', GLOBAL_SCOPE, array_fetch_size);

or

create_group_from_query('record_group_name', 'query_text', GLOBAL_SCOPE, array_fetch_size);

To pass a record group to a graph or report, use the RUN_PRODUCT built-in. To do this, the record group must have the same data structure as the query on which the graph or report is already based.

run_product(GRAPHICS, 'graph_module_name', SYNCHRONOUS, RUNTIME, FILESYSTEM,
parameter_list_id, 'graph_block.graph_item');

An example of the code to realize this function follows. The example passes data from a record group called GRAPH_RECS to a graph named DPTGRAPH.

PROCEDURE Run_Department_Graph IS
PARAM_LIST_ID ParamList;
BEGIN
/* Check to see if the 'GRAPH_DATA' parameter list exists. */
PARAM_LIST_ID := Get_Parameter_List('GRAPH_DATA');
/* If parameter list exists, delete it to ensure it contains
** only the parameters we want. */
IF NOT Id_Null(PARAM_LIST_ID) THEN
Destroy_Parameter_List( PARAM_LIST_ID );
END IF;
/* Create the 'GRAPH_DATA' parameter list. */
PARAM_LIST_ID := Create_Parameter_List('GRAPH_DATA');
/* Populate the parameter list with a data parameter whose key is
** the name of the query currently driving the graph, and whose
** value is the name of the record group to pass from this form. */
Add_Parameter(PARAM_LIST_ID,'GRAPH_QUERY',DATA_PARAMETER,'GRAPH_RECS');
/* Run graph and pass it the parameter list */
Run_Product(GRAPHICS, 'DPTGRAPH', SYNCHRONOUS,
RUNTIME, FILEYSTEM, PARAM_LIST_ID, NULL);
END;

In this chapter, you covered some fascinating information on advanced forms programming. The topics covered included building multiple-form applications, defining data sources, and working with record groups. The contents of this chapter represent about 27 percent of material tested on OCP Exam 4.

The first area you covered was building multiple-form applications. Form Builder provides three separate built-ins capable of invoking forms: CALL_FORM, NEW_FORM, and OPEN_FORM. The standard built-in used for most multiple-form work is OPEN_FORM. If you need to open a new form modally, use the built-in CALL_FORM. If you want to open a new form and close the one that was active, use the built-in NEW_FORM. When one form opens another and the calling form has pending transactions that have not been posted, the called form is opened in post-only mode, which means that the calling form cannot commit any transactions or perform a rollback; it can only post to the database whatever changes the user makes while in the called form. There are three built-ins enabling you to move between open forms: GO_FORM, NEXT_FORM, and PREVIOUS_FORM. The most versatile is GO_FORM, because it allows you to jump to any form, not just the next or previous one.

To supply your PL/SQL code with information about the application’s environment, you can use the GET_APPLICATION_PROPERTY built-in. This built-in can return the name of the current form, calling form, and current form filename; the type of data source, operating system, and user interface on the client computer; the display’s height and width; the user’s name and password; and the language in which the application is operating.

You can pass data between forms using parameter lists whenever one form module calls another. There are two ways to do this: create the parameters under the Parameters nodes in both form modules, or create the parameters in just the called form, and write PL/SQL code in the calling form that constructs the parameter list and then calls the second form. Either way, the called form must have the parameters defined already, so they must be established in the called form at design time.

After establishing the basic premises of building multiple-form applications, your attention turned to defining data sources. You can create data blocks based on a database table, database view, from clause query, stored procedure, or transactional trigger. In the case of the stored procedure, there are two options for how the data is provided: data is returned to the data block either in the form of a ref cursor, which is a pointer to a server-side cursor that is populated by a select statement, or as a table of records, which is an array-like structure sent to the client computer containing every record returned by the procedure. While a database table is still the most common source for a data block, the other data source types offer advantages in specific situations. If you want to minimize the network traffic required to fetch large numbers of records, a stored procedure returning a table of records would be your first choice. If you need to get data from multiple data blocks and don’t want to create any new objects on the server, a from clause query is the way to go. If your users will need to update the records in the data block, you will look at data source types of table, view, of stored procedure returning a table of records. If you are interacting with a non-Oracle database, a transactional trigger is likely to be your best choice.

The final area covered in this chapter was working with record groups. A record group is a Form Builder object that makes tabular data available to your application. Record groups are the basis for all LOVs. There are three types of record groups: query, nonquery, and static. A query record group is based on a SQL select statement, and it provides a functionality similar to a database view, with two added benefits: the select statement that produces the record group can be dynamically created at run time, and you don’t need to add a view to the database. A nonquery record group does not have an underlying select statement, and so must be populated explicitly each time it is used. A static record group’s structure and contents are defined at design time and cannot change as the application runs. Query record groups and nonquery record groups can be created programmatically. To create a nonquery record group, you use the CREATE_GROUP built-in, and populate it either with the POPULATE_GROUP_WITH_QUERY built-in or with a series of ADD_GROUP_COLUMN, ADD_GROUP_ROW, SET_GROUP_CHAR_CELL, and SET_GROUP_NUMBER_CELL built-ins. To create a query record group, you use the CREATE_GROUP_FROM_QUERY built-in, followed by a POPULATE_GROUP built-in to populate it. If you find you need to dynamically change the select statement that underlies an existing query record group, you can do so programmatically with the POPULATE_GROUP_WITH_QUERY built-in, which replaces the record group’s select statement for the duration of that runtime session.

An LOV acts as a viewing portal into the record group’s rows; each time the LOV is opened, the attached record group is populated automatically. You can change the contents of the LOV dynamically, or make the list show an entirely different record group. To populate an LOV with values from a two-column record group created at run time, use the POPULATE_LIST built-in. If you want to modify an LOV so it shows values from a record group that wasn’t necessarily created at run time, or that has more than two columns, use the SET_LOV_PROPERTY built-in.

If you want to make a record group visible to all forms in an application, include the GLOBAL_SCOPE parameter in the built-in that creates the group. If you wish to make the contents of a record group available to other Developer/2000 products, such as Graphics Builder and Report Builder, you can do so using the RUN_PRODUCT built-in.