Chapter 22

Advanced Forms Programming III

In this chapter, you will understand and demonstrate knowledge in the following areas:

• Including charts and reports in forms
• Applying timers to form objects
• Utilizing reusable components
• Using server features in Form Builder

You’re in the home stretch now on the Forms exams! This chapter covers an assortment of interesting features related to advanced Form Builder programming. You will start by learning how to add graphic charts to your application’s forms. Then, you will get a taste of the book’s next section as you learn how to create a simple report and invoke it with a push button on a form. Next, you will create an experimental form showing how you can use multiple timers in your applications. After that comes an overview of the reusable components supplied with Form Builder, followed by tips on how to use server features more fully in your applications.

The OCP Exam 4 will consist of test questions in this subject area worth 20 percent of the final score.

In this section, you will cover the following points related to including charts and reports in forms:

• Using the Chart Wizard to embed charts in a form
• Using the Report Wizard to create and invoke reports in a form

In this section, you will get a taste of how to create visual output from your data. First, you will see how to create charts based on your own application’s data, and how to place those charts on your application’s forms. Then, you will experiment with building a report and invoking that report from one of your forms.

It’s very simple to create charts that display on your application’s forms. Form Builder provides a Chart Wizard that steps you through the process. In order to see how this works, you will create a new form module and build within it a form that displays the total salaries allocated to each department. To provide data for the form and the chart, you will start by creating a database view that sums salaries by department. Using SQL*Plus or your favorite SQL editor, enter the following code:

create view DEPARTMENT_SALARIES as (
select
DEPARTMENT_NAME,
sum(SALARY) TOTAL_SALARY
from
(select
D.DEPARTMENT_NAME, E.SALARY
from
DEPARTMENT D, EMPLOYEE E
where
D.DEPARTMENT_ID = E.DEPARTMENT_ID
)
group by DEPARTMENT_NAME
);

Create a new form module and named it GRAPH. Create a new data block based on the view you just built. (Note that this will require enabling the Views option in the Data Block Wizard’s Tables dialog box so it will include the DEPARTMENT_SALARIES view in the list of available data sources.) When offered the choice, select Create the data block, then call the Layout Wizard.

Use the Layout Wizard to create a form that shows both view columns in a Tabular layout, displaying 5 records at a time. When the Layout Editor opens, click on the canvas’s background to select the canvas, and change its Name property to DEPARTMENT_SALARIES.

To start the process of creating a chart item, click on the Chart Item button shown here:

Then, drag the mouse on the canvas to create a rectangular area that will hold the chart and its labels. This will cause a dialog box to appear asking whether or not to use the Chart Wizard to create the chart. Select the Use the Chart Wizard option and click on the dialog’s OK button. In the Chart Wizard’s Chart Title page, leave the title field empty, select a chart type of Pie, a Chart Subtype of Plain, and then click on the Next button. The next page asks which of the form’s data blocks should drive the pie chart. Since the form only has one data block, select DEPARTMENT_SALARIES and then click on the Next button to continue.

The next wizard page asks which item from the data block should produce the pie chart’s labels. Ensure that the DEPARTMENT_NAME field is selected, click on the Right Arrow button to move it to the Category Axis area, and then click on the Next button to continue. The next wizard page wants to know which data block item will provide the values for the pie slices. Select the TOTAL_SALARY item, click on the Right Arrow button to move it into the Value Axis area, then click on the Next button. In the final wizard page, change the graph file’s name to dept_slr.ogd and click on the Finish button to complete the process of creating your chart item.

What you see next will be somewhat of a mess—there will be one pie chart with four rectangular areas beneath it representing additional copies of the chart for each of the five records your data block displays on this form. To solve this, click on the pie chart to select it, open its Property Palette, change its Number Of Items Displayed property to 1. While in the Property Palette, change the Background Color property to gray. Then run your form. Once it opens in the Forms Runtime program, populate the form by clicking on the Execute Query button. Your form should now look similar to Figure 22-1.

In the exercise you just completed, you created a graphics file named dept_slr.ogd. This file is separate from the form module, and is called by a chart item on a canvas within the form module. You can use this or any other preexisting graphics file in canvases other than the ones in which they were created. To incorporate an existing graphic display into a form, you still start by using the Chart Item button in the Layout Editor. When the New Chart Object dialog box appears, select the Build a new chart manually option and click on the OK button. The .ogd file will be read by Form Builder, and its settings will be incorporated into the existing form. You then change several of the chart item’s properties: Data Source Data Block, Data Source X Axis, and Data Source Y Axis. Expect to play with this for a while after you have taken your exam, because the Oracle documentation on this feature is somewhat sparse.

To get your first glimpse of report generation in Developer/2000, you will create a simple report showing department salaries. You will then add a push button to your application’s form allowing the user to invoke that report at any time.

Begin by opening the Report Builder program and starting the Report Wizard. Enter a report title of Department Salaries, select the Tabular style, and click on the Next button. In this page, you are asked to enter the SQL query that will feed the report. Since you have already created a view that contains the code for summarizing department salaries, you need only enter this SQL command:

select * from department_salaries
order by department_name;

Your screen should now look like Figure 22-2. Click on the Next button to continue. Unless you have already connected to the database from within Report Builder, you will be asked to log on to your database. Once that is accomplished, you will be asked which items from the data source should be included in the report. Move both items into the Displayed Fields area and click on the Next button. The next screen asks whether any of the fields should be totaled. Since that is not germane to the exam, simply click on the Next button to move on to the next page, where you can change the item labels or widths. Click on the Next button to move past this page as well. The Report Wizard then asks if you would like to apply a predefined template to the report’s layout. Select the Corporate 1 template and click on the Finish button. You should now see a report preview that looks similar to Figure 22-3.

Close the report preview window. Save the report with the name DEPARTMENT_SALARIES. Close Report Builder and return to Form Builder. In your GRAPH form module, create a data block manually, name it DEPARTMENT_CONTROL, and set its Database Data Block property to No. Then open your DEPARTMENT_SALARIES canvas in the Layout Editor. Use the Block drop-down list in the toolbar to change the focus block to DEPARTMENT_CONTROL. Then, create a push button, change its Name property to DEPARTMENT_REPORT_PUSHBUTTON, and change its Label property to Run Report. Create a WHEN-BUTTON-PRESSED trigger for the new push button, and enter the following code into it:

run_product(reports, 'DEPARTMENT_SALARIES', synchronous, runtime, filesystem, '', null);

Run your form, and click on the new Run Report push button. While looking at your report in the on-screen previewer, you can print the report using the File | Print command. You can also use the File | Generate to File command to write the report out to files in a variety of formats: Adobe Portable Document Format (.pdf), Hypertext Markup Language (.htm), Rich Text Format (.rtf), and Postscript (.eps).

In this section, you will cover the following points about applying timers to form objects:

• Creating a timer and handling timer expiration
• Deleting a timer
• Modifying a timer

A timer is a programmatic construct that catalyzes a defined action in a specified period of time. Timers have many uses in certain types of applications. In this section, you will learn how to create, modify, delete, and respond to timers.

A timer’s duration is set in milliseconds; one thousand milliseconds constitutes one second. With a valid range of 1 to 2,147,483,647 milliseconds, you can create a timer that waits almost 25 days before it expires. When you create a timer, you can specify whether or not it repeats after it expires.

The timer itself does not actually execute an action when it expires. That is the job of a WHEN-TIMER-EXPIRED trigger, which you define for a form module as a whole by placing it at the Form level. Since the trigger may be fired by more than one timer, you will see how to determine in the trigger what timer fired it.

To see how timers work, you will create a canvas that lets you run a few of them simultaneously. Start by creating a new form module named TIMERS. Create a new data block manually, change its name to TIMER_CONTROL, and change its Database Data Block property to No. Then, create a new canvas object, change its name to TIMER_CANVAS, and open it in the Layout Editor. Click on the Display Item button and draw a display item that is approximately one inch tall and an inch and a half wide in the middle of your canvas. Name the display item DISPLAY_1, set its Initial Value property to 0, its Database Item property to No, and its Font Size property to 48. Now place a push button just above the display item, and change its name to TIMER_1_PUSHBUTTON. Change its Label property to Start Timer 1. Then, create a WHEN-BUTTON-PRESSED trigger for the push button, and enter the following code for the trigger:

DECLARE
V_TIMER_1 timer;
BEGIN
V_TIMER_1 := create_timer('ONE_SECOND_TIMER', 1000, repeat);
END;

Close the Layout Editor and return to the Object Navigator. Double-click on the TIMERS module’s Triggers node to create a new trigger, and select the WHEN-TIMER-EXPIRED trigger. In the PL/SQL Editor, enter the following code:

:TIMER_CONTROL.DISPLAY_1 := :TIMER_CONTROL.DISPLAY_1 +1;

Run your form, and when it opens in the Forms Runtime program, click on your Start Timer 1 button. You will see your display item begin to increment in intervals of approximately one second. When you have been sufficiently entertained, close the Forms Runtime program and return to Form Builder.

Your application could have multiple timers operating simultaneously. Since there is only one WHEN-TIMER-EXPIRED trigger covering an entire form module, the trigger code needs a way to determine which timer fired. The GET_APPLICATION_PROPERTY built-in has the ability to identify the most recently fired timer. To see how it works, you will modify your TIMER form module so it has two timers running concurrently. Start by opening the TIMER_CANVAS canvas in the Layout Editor. Click on the Display Item button and draw a second display item next to the first one. Name the second display item DISPLAY_2, set its Initial Value property to 0, its Database Item property to No, and its Font Size property to 48. Click on the first push button to set its size as the current default, and then click on the Button toolbar button to create a second push button. Position the new push button above the new display item, and change its name to TIMER_2_PUSHBUTTON. Change its Label property to Start Timer 2. Then, create a WHEN-BUTTON-PRESSED trigger for the push button, and enter the following code for the trigger:

DECLARE
V_TIMER_2 timer;
BEGIN
V_TIMER_2 := create_timer('HALF_SECOND_TIMER', 500, repeat);
END;

Close the Layout Editor and return to the Object Navigator. Open the form-level WHEN-TIMER-EXPIRED trigger in the PL/SQL Editor, and modify its code to match the following:

DECLARE
LAST_TIMER_EXPIRED char(20);
BEGIN
LAST_TIMER_EXPIRED := get_application_property(timer_name);
if LAST_TIMER_EXPIRED = 'ONE_SECOND_TIMER'
then :TIMER_CONTROL.DISPLAY_1 := :TIMER_CONTROL.DISPLAY_1 +1;
elsif LAST_TIMER_EXPIRED = 'HALF_SECOND_TIMER'
then :TIMER_CONTROL.DISPLAY_2 := :TIMER_CONTROL.DISPLAY_2 +1;
end if;
END;

Run your form, and when it opens in the Forms Runtime program, click on your Start Timer 1 and Start Timer 2 buttons. You will see both of your display items begin to increment at the time intervals you specified. After you have seen this, close the Forms Runtime program and return to Form Builder.

There may be times when you will want a timer to execute a certain number of times and then stop. This can be handled using the DELETE_TIMER built-in. To see how this works, modify the code of your form-level WHEN-TIMER-EXPIRED trigger to match the following:

DECLARE
LAST_TIMER_EXPIRED char(20);
LAST_TIMER_EXPIRED_ID timer;

BEGIN
LAST_TIMER_EXPIRED := get_application_property(timer_name);
LAST_TIMER_EXPIRED_ID := find_timer(LAST_TIMER_EXPIRED);

if LAST_TIMER_EXPIRED = 'ONE_SECOND_TIMER'
then
if :TIMER_CONTROL.DISPLAY_1 < 10
then :TIMER_CONTROL.DISPLAY_1 := :TIMER_CONTROL.DISPLAY_1 +1;
else delete_timer(LAST_TIMER_EXPIRED_ID);
end if;
elsif LAST_TIMER_EXPIRED = 'HALF_SECOND_TIMER'
then
if :TIMER_CONTROL.DISPLAY_2 < 10
then :TIMER_CONTROL.DISPLAY_2 := :TIMER_CONTROL.DISPLAY_2 +1;
else delete_timer(LAST_TIMER_EXPIRED_ID);
end if;
end if;
END;

This code incorporates two new built-ins: FIND_TIMER and DELETE_TIMER. The FIND_TIMER built-in returns the internal ID number of whatever timer name is used as an argument. As you no doubt recall, using the ID number can speed up processing when the object will be referred to multiple times in a routine. The FIND_TIMER built-in can work together with the DELETE_TIMER built-in to identify which timer to delete. To see all of this in action, run your form, and when it opens in the Forms Runtime program, click on your Start Timer 1 and Start Timer 2 buttons. You will see both of your display items begin to increment at the time intervals you specified, and then they will each stop after reaching the value of 10, as shown in Figure 22-4. After you have seen this, close the Forms Runtime program and return to Form Builder.

There are other ways you can modify a timer besides just deleting it. The SET_TIMER built-in allows you to change a timer’s duration or iteration setting. The syntax of this built-in is as follows:

set_timer('timer_name', duration, iteration);

or

set_timer(timer_id, duration, iteration);

If you want to change a timer’s duration but leave its iteration setting alone—or vice versa—you can specify NO_CHANGE for the value of the parameter you don’t want to change. For example, the following code would set the HYPER_TIMER to expire every tenth of a second, without changing its iteration status:

set_timer('HYPER_TIMER', 100, no_change);

In this section, you will cover the following points about utilizing reusable components:

• Introduction to reusable components
• Using the Calendar class in an application

Reusability is the essence of object-oriented design. This section identifies the reusable components provided with Form Builder and gives an overview of how to utilize one common component, a calendar, in your own applications.

Form Builder comes with an assortment of reusable components that can provide valuable functionality to your applications. In addition to providing you with useful features requiring very little work to implement, reusing components makes it easier to standardize the look and feel of your application. Table 22-1 lists Form Builder’s reusable components and describes the functionality each component offers.

The certification exam will not require you to attach the Calendar class to an application, but it will expect you to understand the process involved in doing so. There are four steps:

1. Create your form module, data block, and canvas.
2. Attach the Standard Object Library file stndrd20.olb. Copy or subclass the Calendar class from the resultant STANDARDS object library into your form module. This will add a variety of relevant objects to your application.
3. Attach the PL/SQL library file calendar.pll in order to gain access to the DATE_LOV package it contains.
4. To trigger the calendar from the appropriate date item in your form, create a KEY-LISTVAL trigger on the item. Place code in the trigger employing the DATE_LOV package to show the calendar.

The key to making these steps work is the triggering device: the KEY-LISTVAL trigger. Without that trigger, none of the functionality from the Standard Object Library or the Calendar PL/SQL Library will ever be used.

In this section, you will cover the following points about using server features in Form Builder:

• Introduction to Oracle Server features in Form Builder
• Partitioning PL/SQL program units
• Handling errors raised by the Oracle Server
• Performing DDL commands

As this chapter draws to a close, it is an excellent time to discuss incorporating the database server more completely within your applications. In this section, you will read about the abilities Developer/2000 offers to move a substantial amount of your application’s processing to the server. More work done on the server means more potential for server errors, so you will also review how to handle database errors. The section, and the chapter, wrap up with examples showing how you can make your application generate and execute SQL Data Definition Language (DDL) commands dynamically at run time.

Form Builder provides features enabling you to easily create multitier applications. You can partition your application’s program units, PL/SQL libraries, and triggers by simply dragging the objects into the Object Navigator’s Database Objects node. Doing so can yield substantial rewards in system performance, data integrity, ease of maintaining standard objects, and simplicity. Performance can improve because record-oriented actions occur on the server, close to the data, thereby reducing network traffic and resultant delays. Data integrity benefits because anything you have defined to ensure data integrity is stored with the data tables, and will thus be available for action no matter what application is accessing the data. Standards benefit because often-used routines can reside on the server, where they are easy to locate and maintain.

When a program unit is placed on the server, it is called a stored program unit. Form Builder lets you create stored program units containing a procedure, function, package spec, or package body. These are stored in specific schema areas on the server. You can also attach a database trigger directly to a table, thereby ensuring the trigger will fire at the appropriate times regardless of the front-end application used to access the data.

This is not the same as storing the application itself on the server, a feature offered by Form Builder. This feature relates to where the application is stored for retrieval by users, and the options are the database server or a standard file system available to the user. The choice between these two options does not affect how the application partitions its operations once it is loaded into the client computer’s memory.

To see how to create program units on the server, open the Database Objects node in the Object Navigator. Beneath that node, you will see each schema available via your current database connection. Open a schema, and you will see subnodes for Stored Program Units, PL/SQL Libraries, Tables, and Views. Double-click on the Stored Program Units node, and you will be given the opportunity to create a new procedure, function, package spec, or package body directly on the server. You can also copy program units from an existing client-side PL/SQL library into the Stored Program Units node of a schema simply by dragging the program units in the Object Navigator and dropping them on the appropriate Stored Program Units node.

When a procedure or function is created on the server, it can be called in exactly the same way as a procedure of function residing on the client computer. There are some limitations on what stored procedures can do, however. They cannot refer to bind variables: form items, global variables, or system variables. If you need to pass values to a stored procedure, you must do it using parameters. In addition, all form processing pauses when a stored procedure or function is called; the form waits until the procedure or function completes, so you will want to consider the performance of your server and network when deciding what parts of your application to store on the server.

As discussed in Chapter 16, you can trap errors returned by the Oracle server. This becomes especially important as you move more of your application’s processing onto the server. Chapter 16 introduced one of the two ways to trap server error messages: the DBMS_ERROR_TEXT built-in, which returns the message text of the most recent Oracle database error. An example of its use follows:

DECLARE
DBERR_NUM number := dbms_error_code;
DBERR_TXT varchar2(80) := dbms_error_text;
ERROR_TYPE varchar2(3) := error_type;
BEGIN
if DBERR_NUM = -1 then
message('Primary key for new record duplicates key in existing record.');
else
message('Cannot insert this record...reason undetermined.');
end if;
else
message(ERROR_TYPE || '-' || to_char(DBERR_NUM) || ': ' || DBERR_TXT);
end if;
end;

You can write PL/SQL code in Form Builder that dynamically generates and executes DDL commands while the application is running. Based on the FORMS_DDL built-in, this very cool feature affords you a lot of flexible control over the database while your application is running. For instance, if you want to store posted records in a temporary database table until they are committed, you can have your application build the temporary table dynamically and then drop it when it is no longer needed. What follows is a very simple example of how to do this:

BEGIN
forms_ddl('create table TEMP_RECS (COL_1 varchar2(20), COL_2 number) ');
if form_success then
message ('Temporary table successfully created.');
else
message ('Temporary table could NOT be created.');
end if;
END;

Note that in this example, the SQL DDL statement did not have its own terminating semicolon. For multiple-line SQL statements, you should include semicolons in the standard places, with the exception of the last, terminating semicolon. The contents of the SQL DDL command itself can be constructed dynamically at run time. One simple application of this is demonstrated in the following code, which is adapted from a clever example in the Oracle documentation:

PROCEDURE Create_N_Column_Number_Table (N number) is
V_SQL_STRING varchar2(2000);
BEGIN
V_SQL_STRING := 'create table tmp (COL1 number';
for I in 2..N loop
V_SQL_STRING := V_SQL_STRING || ',COL' || to_char(I) || ' number';
end loop;
V_SQL_STRING := V_SQL_STRING || ')';

forms_ddl(V_SQL_STRING);
if form_success then
message ('Table successfully created.');
else
message ('Table could NOT be created.');
end if;
end;

In this chapter, you have covered some powerful concepts regarding advanced forms programming. Several topics are covered, including embedding charts and reports into your forms, utilizing timers in your applications, incorporating reusable components, and using server features more fully in your applications.

The first area you covered was embedding charts and reports into your forms. To add a chart to a Form Builder form, all you need to do is make sure the form module has a data block that provides the data the chart needs, and then use the Chart Wizard to create the chart. The Chart Wizard generates a separate graphics file with a file type of .ogd, and it generates a chart item on your form to display the chart. That same graphics file can be used by other canvases as well, by creating a chart item and setting its Data Source Data Block, Data Source X Axis, and Data Source Y Axis properties. To create a report, you invoke the Report Builder program and start the Report Wizard. This, too, results in a separate file on disk, with a file type of .rdf. Once the report file is created, you can invoke it from a push button or any other code-containing object in your application, using the RUN_PRODUCT built-in. While the report is running, you can export its contents to files formatted into Adobe Portable Document Format (.pdf), Hypertext Markup Language (.htm), Rich Text Format (.rtf), and Postscript (.eps).

The next section you explored was timers. A timer is a programmatic construct that catalyzes a defined action in a specified period of time. A timer’s duration is set in milliseconds, which are thousandths of a second. With a valid duration of 1 to 2,147,483,647 milliseconds, you can create a timer that waits almost 25 days before it expires. When you create a timer, you can specify whether or not it repeats after it expires. The timer itself does not execute code when it expires. That is the job of a WHEN-TIMER-EXPIRED trigger, which you define at the form level. Your application can have multiple timers operating simultaneously. Since all timers will cause the same WHEN-TIMER-EXPIRED trigger to fire, the trigger needs a way to determine in the trigger what timer fired it. The GET_APPLICATION_PROPERTY built-in allows you to determine the name of the most-recently fired timer in an application, which you can use as the argument in an if statement within the trigger to determine what action to take based on which timer expired. You can delete a timer by using the DELETE_TIMER built-in. You can determine a timer’s internal ID with the FIND_TIMER built-in. You can modify an existing timer using the SET_TIMER built-in, which allows you to change a timer’s duration or iteration setting.

After working with timers, you were introduced to the reusable components supplied with Form Builder. These include the ActiveX control, Calendar class, Navigator class, Picklist class, Standard Object Library, and Wizard class. The ActiveX control enables augmenting your forms with predefined ActiveX control objects providing features such as word processing, spreadsheets, and handling of video clips. The Calendar class makes it possible to easily add a calendar or date LOV to your application. The Navigator class allows you to add an Explorer-type interface to your application, much like the Object Navigator in Form Builder. The Picklist class enables you to include a picklist in your applications. The Standard Object Library contains a collection of predefined alerts, buttons, form input items, layouts, visual attribute groups, and the Calendar, Navigator, Picklist, and Wizard components. It can be extended or modified by the developer, and its objects can be set as standard or customized SmartClasses. Finally, the Wizard class allows you to create customized wizards for your applications. You still have to do a little bit of work to use these classes; for instance, to use a Calendar class after all the pieces have been attached, you can create a KEY-LISTVAL trigger for your LOV field.

The last topic was the server features you can use in your Form Builder applications. Form Builder provides features enabling you to easily create multitier applications by partitioning your application’s program units, PL/SQL libraries, and triggers between the client computer and the database server. You can move program units, PL/SQL libraries, and triggers to the server by dragging the objects into the Object Navigator’s Database Objects node. When a program unit is placed on the server, it is called a stored program unit. You can also create new stored program units directly from within Form Builder. To catch and handle errors returned by the server when it executes stored program units, use the DBMS_ERROR_TEXT built-in, which returns the message text of the most recent Oracle database error. If you need to have your application dynamically generate and execute DDL commands at run time, you can do so using the FORMS_DDL built-in.

All in all, this chapter comprises about 20 percent of material tested on OCP Exam 4.