Chapter 12

Form Builder

In this chapter, you will learn about the following facets of Form Builder:

• Working in the Form Builder environment
• Creating basic form modules
• Running a Form Builder application

In this chapter, you will get a thorough introduction to Form Builder. You will start by learning about the Form Builder environment, the main Form Builder components, and the main objects in a form module. Next, you will step through the basics of creating form modules, and learn the four steps involved in creating and using a Developer/2000 form. You will create a new form module, generate data blocks to use within the module to access database data, and create a basic form layout for viewing the data. After modifying the data blocks and form layout a bit, you will learn how to compile form modules, and once your sample application is compiled, you will run it using the Forms Runtime program. Next, you will be introduced to Form Builder file formats and their characteristics. With that under your belt, you will go on to create a relationship between data blocks, and then modify your single-table form so it shows records from two tables synchronized in a master/detail relationship. In the final section, you will learn a variety of ways to run a Form Builder application, as well as how to filter what data is retrieved into a Form Builder application. You will be shown time-saving techniques for inserting repetitive data, and you will learn how to get detailed information about database errors in the Forms Runtime program. The OCP Exam 3 will consist of test questions in this subject area worth 12 percent of the final score.

In this section, you will cover the following points about working in the Form Builder environment:

• Identifying the main Form Builder executables
• Identifying the main components of Form Builder
• Identifying the main objects in a form module

It is time to move beyond theory and start learning about the nuts and bolts of building forms in Developer/2000’s Form Builder. This section will start by defining the main executable files that make up the Form Builder application. Next, you will learn about the main components that Form Builder makes available to you, and what each component does. Finally, you will take one of those components, the Form module, and get an understanding of the main objects within it.

As shown in Table 12-1, there are three executable programs that make up the Form Builder application in Developer/2000. All three are located in the bin directory beneath your Oracle software home directory. The executable programs that are for Windows environments are listed in the following tables. To determine the name of the executable in UNIX environments, where 32-bit applications have been around long enough for it not to be so novel as to include mention of them in the executable filename, remove the 32.exe from the end of the executable filename.

The Form Compiler program takes the .fmb file containing your Forms Builder application and compiles it so it can be run by the Forms Runtime program. This process compiles the PL/SQL code in your application’s program units, producing an .fmx file as a result. The Forms Runtime program reads the .fmx file and runs your application. This entire process occurs automatically when you run your form from within Form Builder, as long as you leave the Build Before Running preference at its default setting of Yes. You can also compile your form at any time from within Form Builder by executing the Program | Compile menu command. (The File | Administration | Compile File menu command will do the same thing, but does not offer incremental compilation.)

A Form Builder application contains several different types of components, each providing a unique kind of functionality. The individual components are called modules. The modules can be grouped into the categories that follow:

• Form modules Make up the bulk of most applications. It is within form modules that you define what data is presented to the user, how it is presented, and what they can do with it. This is also where you define relationships, PL/SQL triggers, limits on the number of records fetched, and similar parameters.
• Menu modules Where you store information about custom menus you create for your application. In addition to storing the definitions and underlying code for menu and submenu items, a menu module keeps track of any libraries the menu uses, information about object grouping and classes/subclasses, help items associated with the menu, and visual attributes such as font name, size, weight, style, and spacing. Because a form’s menus are stored in a Menu module, it is possible to customize or replace the default menus. This allows you to provide exactly the functionality your application requires.
• PL/SQL Library modules Contain the client-side procedures, packages, and functions that you write. These modules are called by other modules within your application.
• Object Library modules Enable you to store objects your application uses, and reuse them wherever you need them. This can speed development dramatically, as well as make it easier to enforce standards throughout your company.

Most users see a data-entry form as a single object. As a developer, you need to think about a form’s contents not only in more detail, but also in a hierarchical structure. In Developer/2000, a form module contains numerous "large" objects that, in turn, hold smaller objects. All of these form module objects come together into a single cohesive interface that lets the user interact with database data. What follows is a description of these objects, in order from largest to smallest. For your assistance in understanding, Figure 12-1 shows a sample form and the object types within it.

This is the outermost boundary for a form—an empty frame to hold objects. All of the visual objects in a Form Builder application are contained within windows. There are two types of windows: document and dialog.

Document windows are used for standard data entry forms. If you are developing on a Windows platform, your application will automatically have a special type of document window called a multiple document interface (MDI) window that serves as a "parent" window containing all the other document windows. The MDI window usually holds the application’s main menu and toolbar, as well. While the MDI window is not included in an application’s list of windows, you can still maximize, minimize, and resize it via code. All other document windows—in other words, the ones you create—must fit within the confines of the MDI window. When a user is in a document window, they can generally enter and query data, move to other windows within the application, and interact with the application’s menu and toolbars.

Dialog windows are used for displaying messages to the user. When defining the properties of a dialog window, you can specify its size and position; whether or not it is modal (forcing users to respond to it before they can do anything else); whether the user can move, resize, maximize, minimize, or close it; and visual attributes such as colors, fill patterns, and font name, size, weight, style, and spacing.

A window can contain one or more canvases, which are the visual backgrounds on which you place form objects. There are four types of canvases: content, stacked, tab, and toolbar.

A content canvas is the essential background for any form window. Because of this, a content canvas is automatically placed in any new form layout you create. It is the default canvas type.

A stacked canvas resides on top of a content canvas, hiding a portion of the content canvas as it does so. The stacked canvas can look either similar to or different from the content canvas background, depending on the effect desired by the developer. You can use stacked canvases to make items seem to appear and disappear on the screen based on criteria you specify. You can also use a stacked canvas to create scrolling sub-windows within a window, and to display a single form header above multiple forms without having to lay the header out in each form, among other things. A window can contain numerous stacked canvases.

A tab canvas is a multiple-page object that is very familiar to users who have set the "options" for practically any Windows object. It allows the user to move between multiple pages of related information simply by clicking on intuitive tabs at the top of the pages. A tab canvas is most useful when you need to display a large quantity of related data while consuming relatively little screen space. A tab canvas resides on top of a content canvas, hiding a portion of the content canvas as it does so.

Toolbar canvases are available in several flavors: horizontal, vertical, and MDI. A window can have one or more toolbars—even multiple horizontal or vertical toolbars—and you have programmatic control over which toolbars are visible at a given time, as well as over what items within each toolbar are visible. If you are developing within a Windows environment, you can also create toolbars for the MDI window.

In Forms Builder, a block is a logical container that holds form objects such as data items and control buttons. There are two types: data block and control block.

A data block creates a bridge between your form and the data in your database. It allows you to access data from a database table, a database view, a procedure, or a transactional trigger. Form Builder automatically puts a frame around a data block, and you can use the frame not only to move the block on the layout, but also to select the block for modification.

A control block holds items that do not interact directly with your data, but instead exert control over the application itself. For instance, when you add buttons to a form, you place them within a control block.

The items are the individual objects on your forms with which your users interact. The most common item is a text item, which contains fields enabling your users to view, enter, and modify database data. There are also items to display read-only data (chart item, display item, image item, and OLE container); to depict changeable data in a graphical representation (check box, list item, and radio group); to control the application (button); to manipulate audio objects (sound item); and to simplify the creation of user interfaces (ActiveX control).

In this section, you will cover the following points about creating basic form modules:

• Creating a form module
• Using the Data Block Wizard to create and modify data blocks
• Creating and modifying layouts with the Layout Wizard
• Saving, compiling, and running a form module
• Form Builder file formats and their characteristics
• Creating data blocks with relationships
• Running a master/detail form module

When you want to create a new database form with Developer/2000, there are four steps to go through. First, you create a new form module in Form Builder. Next, you create a data block to supply the new form module with data. After that, you create a form layout depicting the data block’s items on one or more canvases. Finally, you save, compile, and run the module. In this section, you will go through all of these steps. Starting with a sample requirement for a one-table form, you will create a new form module, give it a data block, lay the data block’s items out on a form canvas, and compile and run it in the Forms Runtime environment. You will then learn about the different file types that Form Builder produces when you generate form modules. Next, you will learn how to create relationships between data blocks. The section will wrap up by stepping you through the process of creating a form utilizing a master/detail relationship.

Creating a new form module is very simple. The first step is to start the Form Builder application. If you see the Welcome dialog box shown in Figure 12-2, deselect the Display at startup option. Then select the option labeled Build a new form manually and click on the OK button. You should see the Form Builder Object Navigator, with the Forms node open and a module showing, usually with the name MODULE1. If your Forms node does not have any modules, simply double-click on the node name Forms to create one.

Take a moment to look at the form module that has been created on your screen. Examine the module nodes shown in the tree beneath the module name: Triggers, Alerts, Attached Libraries, etc. Notice that one of the nodes has a plus to the left of its name: Windows. Click on the plus sign to the left of Windows, and you will see that a default window has automatically been created for your form module. A form module must have at least one window, so Form Builder created one for you.

In order to manipulate data, a form needs a data block to serve as a link between the form and the database—or more accurately, between the form and the data source, since a data block can be based not only on a table, but also on a view, a procedure, or a transactional trigger. Form Builder provides a Data Block Wizard to simplify the process of creating data blocks.

Before starting the Data Block Wizard, you will want to create the test tables used in this chapter. A listing of the SQL code to create and populate the test tables follows. For your reference, an entity-relationship diagram for the test tables is shown in Figure 12-3. If you would rather not type in the SQL code, you can find it in the file named erdscript.sql in the appropriate directory for this chapter on the book’s CD-ROM.

CREATE TABLE DEPARTMENT
( DEPARTMENT_ID NUMBER(8,0) NOT NULL,
DEPARTMENT_NAME VARCHAR2(25) NOT NULL );

CREATE UNIQUE INDEX DEPARTMENT_BRW_P1 ON DEPARTMENT
( DEPARTMENT_ID ASC );

ALTER TABLE DEPARTMENT
ADD ( PRIMARY KEY (DEPARTMENT_ID) ) ;

CREATE TABLE EMPLOYEE
( EMPLOYEE_ID NUMBER NOT NULL,
LAST_NAME VARCHAR2(20) NOT NULL,
FIRST_NAME VARCHAR2(15) NOT NULL,
HIRE_DATE DATE NOT NULL,
DEPARTMENT_ID NUMBER(8,0) NOT NULL,
SALARY NUMBER(8,2) NOT NULL
);

CREATE UNIQUE INDEX EMPLOYEE_BRW_P1 ON EMPLOYEE
( EMPLOYEE_ID ASC );

ALTER TABLE EMPLOYEE
ADD ( PRIMARY KEY (EMPLOYEE_ID));

CREATE TABLE SKILL
( SKILL_CODE VARCHAR2(8) NOT NULL,
SKILL_FULL_NAME VARCHAR2(30) NOT NULL);

CREATE UNIQUE INDEX XPKSKILL ON SKILL
( SKILL_CODE ASC );

ALTER TABLE SKILL
ADD ( PRIMARY KEY (SKILL_CODE));

CREATE TABLE EMPLOYEE_SKILL
( EMPLOYEE_ID NUMBER NOT NULL,
SKILL_CODE VARCHAR2(8) NOT NULL,
SKILL_LEVEL NUMBER(1,0) NOT NULL
CHECK (SKILL_LEVEL BETWEEN 1 AND 5 );

CREATE UNIQUE INDEX XPKEMPLOYEE_SKILL ON EMPLOYEE_SKILL
( EMPLOYEE_ID ASC, SKILL_CODE ASC );

ALTER TABLE EMPLOYEE_SKILL
ADD (PRIMARY KEY (EMPLOYEE_ID, SKILL_CODE));

ALTER TABLE EMPLOYEE
ADD (FOREIGN KEY (DEPARTMENT_ID) REFERENCES DEPARTMENT);

ALTER TABLE EMPLOYEE_SKILL
ADD (FOREIGN KEY (SKILL_CODE) REFERENCES SKILL);

ALTER TABLE EMPLOYEE_SKILL
ADD (FOREIGN KEY (EMPLOYEE_ID) REFERENCES EMPLOYEE);

CREATE INDEX XIF1EMPLOYEE ON EMPLOYEE
( DEPARTMENT_ID ASC );

CREATE INDEX XIF6EMPLOYEE_SKILL ON EMPLOYEE_SKILL
( EMPLOYEE_ID ASC );

CREATE INDEX XIF7EMPLOYEE_SKILL ON EMPLOYEE_SKILL
( SKILL_CODE ASC );

INSERT INTO DEPARTMENT VALUES (1, 'Sales');
INSERT INTO DEPARTMENT VALUES (2, 'Research and Development');
INSERT INTO DEPARTMENT VALUES (3, 'Information Technology');
INSERT INTO DEPARTMENT VALUES (4, 'Maintenance');

INSERT INTO EMPLOYEE VALUES (1001, 'Smith', 'Don', '30-JUN-72', 1, 45000);
INSERT INTO EMPLOYEE VALUES
(1002, 'Atkinson', 'Arlene', '29-OCT-88', 2, 50000);
INSERT INTO EMPLOYEE VALUES
(1003, 'Zuckerman', 'Laren', '30-JUN-97', 3, 41369);
INSERT INTO EMPLOYEE VALUES
(1004, 'Rusch', 'Grace', '28-JUL-69', 2, 75000);
INSERT INTO EMPLOYEE VALUES
(1005, 'Haskins', 'John', '29-OCT-98', 3, 95000);
INSERT INTO EMPLOYEE VALUES
(1006, 'Smythe', 'Nancy', '15-SEP-88', 3, 105000);
INSERT INTO EMPLOYEE VALUES
(1007, 'Campbell', 'Scott', '04-JAN-99', 4, 56000);
INSERT INTO EMPLOYEE VALUES
(1008, 'Miller', 'John', '06-DEC-99', 3, 87000);

INSERT INTO SKILL VALUES ('SALES', 'Selling techniques');
INSERT INTO SKILL VALUES ('ACCTG', 'Accounting');
INSERT INTO SKILL VALUES ('C', 'C Programming');
INSERT INTO SKILL VALUES ('DBA', 'Database Administrator');
INSERT INTO SKILL VALUES ('D2K', 'Oracle Developer 2000');
INSERT INTO SKILL VALUES ('DA', 'Data Architecture');
INSERT INTO SKILL VALUES ('SI', 'Systems Integration');
INSERT INTO SKILL VALUES ('JAVA', 'Java Programming');
INSERT INTO SKILL VALUES ('HM', 'Humorous in Meetings');

INSERT INTO EMPLOYEE_SKILL VALUES (1005, 'D2K', 5);
INSERT INTO EMPLOYEE_SKILL VALUES (1005, 'DBA', 4);
INSERT INTO EMPLOYEE_SKILL VALUES (1005, 'DA', 5);
INSERT INTO EMPLOYEE_SKILL VALUES (1004, 'D2K', 4);
INSERT INTO EMPLOYEE_SKILL VALUES (1004, 'DA', 2);
INSERT INTO EMPLOYEE_SKILL VALUES (1004, 'HM', 5);
INSERT INTO EMPLOYEE_SKILL VALUES (1002, 'SI', 5);
INSERT INTO EMPLOYEE_SKILL VALUES (1002, 'DA', 1);
INSERT INTO EMPLOYEE_SKILL VALUES (1001, 'SALES', 4);
INSERT INTO EMPLOYEE_SKILL VALUES (1006, 'DA', 4);
INSERT INTO EMPLOYEE_SKILL VALUES (1006, 'SI', 5);
INSERT INTO EMPLOYEE_SKILL VALUES (1006, 'ACCTG', 5);
INSERT INTO EMPLOYEE_SKILL VALUES (1006, 'JAVA', 4);
INSERT INTO EMPLOYEE_SKILL VALUES (1007, 'HM', 5);
INSERT INTO EMPLOYEE_SKILL VALUES (1008, 'DBA', 3);
INSERT INTO EMPLOYEE_SKILL VALUES (1008, 'C', 4);

COMMIT;

To start the Data Block Wizard, right-click on the Data Blocks node beneath your Form module, and select Data Block Wizard from the context menu that appears. You will be presented with the dialog box shown in Figure 12-4, asking whether to base the data block on a table, view, or stored procedure. Select the Table or View option and click on the Next button.

In the next page, the Data Block Wizard asks what table or view you wish to use as a base for your data block. For this first exercise, you will create a data block on the DEPARTMENT table. You can either type in the table’s name and click the Refresh button to get a list of the table’s columns, or you can click the Browse button, select Current user and Tables from the dialog box that results, click on the OK button, and then select the DEPARTMENT table. Whichever approach you prefer, you will end up with a dialog box filled in as shown in Figure 12-5. Press the button with the two right arrows on it to move all of the table’s column names from the Available Columns area to the Database Items area of the dialog box. Then, click on the Next button. This will take you to the Finish page, which is depicted in Figure 12-6. Select the Just create the data block option and click on the Finish button.

In addition to simplifying the creation of new data blocks, the Data Block Wizard can assist you in modifying existing data blocks. When used in this way, the Data Block Wizard offers the same options it did when you initially created the data block: control over the data source, selection of columns included, and ability to generate relationships between data blocks. To change other data block parameters, you must open the data block’s property sheet, just like any other Form Builder object.

You can start the Data Block Wizard on an existing data block in either of two ways. You can either right-click on the data block and then choose Data Block Wizard from the context menu or you can click on the data block and then choose Tools | Data Block Wizard from the standard Form Builder menu. Once you have started the Data Block Wizard, you will see the familiar three tab pages to control the data block’s type, table, and master/detail relationships. For instance, in the tab page labeled Table, you can use the arrows between the Available Columns and the Database Items areas to change which columns are included in the data block, or to change their column order. Once your changes are complete, you click the Finish button to exit the Data Block Wizard.

The Layout Wizard is Form Builder’s tool for creating and modifying forms quickly. It works by asking you a series of questions and then generating a basic form that fulfills the criteria you have specified. The Layout Wizard can later modify the form if you want to change its fundamental design or contents. You can also modify the layout manually, if you wish, to better suit your application’s needs.

It is very simple to create a basic entry form using the Layout Wizard. First, open the Developer/2000 Form Builder application. Then, right-click the data block you would like to use as the basis for a form. If you do not yet have a data block with the desired data, create one using the steps in the previous section. For the purposes of this example, select the DEPARTMENT data block from the test data provided with this book.

Start the exercise by choosing Layout Wizard from the context menu that appeared when you right-clicked on your data block. You may or may not see the Layout Wizard Welcome screen, depending on how your system is configured. If you do see it, deselect the Display this page next time option and click the button labeled Next. You will see the dialog box shown in Figure 12-7.

The Canvas page starts by asking you what canvas you wish to use to display the form you are about to create. Remember, the canvas is the background that underlies all forms. If this is the first form you have created for this project, there will not be any existing canvases to select, so the proper choice for canvas is (New Canvas). For the canvas Type, leave the selection at the default choice of Content. Click the Next button.

You should now be on Layout Wizard’s Data Block page, shown in Figure 12-8. On this page, you select the data block that the Wizard will use as the basis for this layout. By default, the data block you started with is displayed, although you can change that at this point. More likely, you will use this page to select which items (that is, data columns) from the data block you wish to have displayed in the layout. Most developers are familiar with selection interfaces like the one used on the Data Block page. The left area, labeled Available Items, contains all the items in the data block you have selected. The right area, labeled Displayed Items, shows the items you have selected for inclusion in the layout. Move the DEPARTMENT_ID and DEPARTMENT_NAME items into the Displayed Items column. Then, click the Next button.

You should now be on the Layout Wizard Style page. Here you can change the onscreen prompt label that will be displayed next to each data item. If the label contains more than one word and there is a vertical bar (|) displayed between the two words, that means the Layout Wizard intends to split the prompt onto more than one line. Figure 12-9 demonstrates this in the prompt for the DEPARTMENT_NAME item. To the right of the Prompt column are two columns showing the field width and height that the Layout Wizard is planning to give each item. By default, these measurements are shown in points. There is no reason to change the measurements at this time, so you can just click the Next button.

You will next see the Layout Wizard’s Style page, shown in Figure 12-10. Obviously, this is where you specify whether the data you selected should be depicted in a form layout, or in a table layout, which looks like a spreadsheet. For the purposes of this exercise, select the Tabular choice and click the Next button.

You should now be on the Layout Wizard Rows page. In this Layout Wizard page, shown in Figure 12-11, you start by entering a title for the frame the Layout Wizard is going to place around your data items. This frame serves as a container for all of the items. For the purposes of this exercise, enter DEPARTMENT Frame Title as the frame title. The next field, Records Displayed, allows you to specify how many records are shown at a time in this layout. Since you chose a tabular layout, it makes sense to design the layout so that more than one record shows at a time. Specify that five records should be shown at a time. The next field, Distance Between Records, specifies how much vertical space is placed between each row. This parameter only applies to layouts in which the Records Displayed option is set to more than one. Even then, it is generally better to leave the parameter at its default setting of zero. If for some reason the records prove to be too close together, you can easily increase the space between them in the Layout Editor, which provides versatile features for spreading items evenly and displays the results instantly so you can quickly determine the optimal amount of vertical space. The final field on this page specifies whether the frame will include a scrollbar to move through records. This can be a good idea if your layout will be displaying more than one record at a time. Since your DEPARTMENT layout will show more than one record at a time, click on the Display Scrollbar parameter so that it is enabled. Then click the Next button.

The Layout Wizard now displays its Finish page. Read the text on this page and then click the Finish button. Form Builder will generate your form and display it in the Layout Editor. Later sections of this book will go into great detail about the editing changes you can make to your form in the Layout Editor. For now, the discussion will be limited to modifications you can make using the Layout Wizard you just employed.

By running the Layout Wizard again on an existing layout, you can make fundamental changes to the layout quickly. You can invoke the Layout Wizard from within the Layout Editor by clicking the Layout Wizard toolbar button, which looks like this:

This is called running the Layout Wizard in reentrant mode. The most useful reason to rerun the Layout Wizard on an existing form is to add or remove data items from the displayed canvas. For instance, if you have an Employee form that includes sensitive information such as Social Security Number, you may be asked at some point to remove that information from a canvas so it is not displayed to all employees. It may seem natural to just open the canvas in the Layout Editor, select the item you no longer want to display, and press the Delete key. Doing this will certainly remove the item from your layout, but it will also remove the item from the underlying data block. If the form needs that item for some other purpose, but you do not want to display the item, that’s the time to use the Layout Wizard. You can restart the Layout Wizard on that block in your layout, and move the appropriate items from the Displayed Items column back to the Available Items column. Once you have finished, your form will display only the items you want, while your data block continues to contain all the items it had originally.

You can also use the Layout Wizard to change the size of your data items. While you can do this in the Layout Editor just by clicking on an item’s field and dragging its edges to make it short or longer, the Layout Wizard incorporates more intelligence about the block’s physical layout on the canvas. If you use the Layout Wizard, rather than the Layout Editor, to increase the length of a field beyond what the data block’s frame can contain, the Layout Wizard will automatically increase the size of the frame, and even rearrange the fields within the frame if necessary. Because of this ability, the Layout Wizard is your best tool for increasing a field’s size when the increase is enough to make the field extend outside the data block’s current frame. To finish this section, close the Layout Editor and return to the Object Navigator.

As you know, you can create form modules in Form Builder, but you cannot run them there. For that, you must invoke the Forms Runtime program. Before you do, however, you need to save the form and compile it.

You will find this extremely easy. To save the form, perform either of these actions. Either execute the File | Save command from Form Builder’s menu, or click the familiar Save button, whose icon is shown here:

The form modules you create in Form Builder cannot be run directly. There is an interim step in which the form module file is converted into a separate, executable file in a format that the Forms Runtime program can read. This process is called compiling. You can compile your form modules at any time by executing the File | Administration | Compile File menu command.

You can configure Form Builder to automatically compile your module before you run it. Go to Tools | Preferences, and under the General tab select the Build Before Running option. The rest of this chapter will assume that the Build Before Running option is enabled, so you should enable it on your system now. Note that this option does not cause the module to be saved, so you will need to either save the module manually, or enable the Save Before Building preference option discussed previously. In addition, the Build Before Running option does not cause library or menu modules attached to a form to be compiled when the form is compiled, so when you have an application that includes custom library or menu modules, you will need to compile them manually before running the form.

To run your form module, perform either of these actions. Either execute the Program | Run Form command, or click on the Run Form button, as shown in this illustration:

Form Builder will start the Forms Runtime program, which will load the executable version of your form. Figure 12-12 shows how the form will look.

The first thing you’re likely to notice is that the form does not display any data. The Forms Runtime program is making an assumption that when you first open a form, you want to enter new data. If you want to see existing data, you must query the database. To do that, take either of these actions. Either run the Query | Execute menu command, or click on the Execute Query button shown here:

Later in this book, you will learn how to customize the form so it displays data as soon as it opens.

Now that you have gone through a complete cycle of creating, compiling, and running a form module, it is time to step back for a moment and discuss a bit more theory: the different types of Form Builder files. Each of the main Form Builder nodes generates a different set of files, and each file has a different purpose.

The Form node generates three file types. The design file you work with directly is given an extension of .fmb (short for Form Module Binary). The executable file generated when you compile a form module is given an extension of .fmx (Form Module Executable). The form module’s parameters can be exported to an ASCII text file using the File | Administration | Convert menu command, and that file’s extension is .fmt (Form Module Text).

The Menu node also generates three file types. The design file you work with (coming up later in this book) has an extension of .mmb. The executable file generated when you compile has an extension of .mmx. The menu module can be exported to an ASCII text file whose extension is .mmt.

The PL/SQL Library node generates three file types, but their file extensions follow a different pattern than those presented so far. The design file is given an extension of .pll (Programming Language Library), and unlike any other node, the design version contains both source and executable code, and thus can be used as run time. The executable file, whose file extension is .plx, contains only the executable code—no source code. The PL/SQL Library can be exported to an ASCII text file, and that file’s extension is .pld.

The Object Library node generates only two file types. The design file has the extension .olb. The Object Library can be exported to an ASCII text file, and that file’s extension would be .olt.

Table 12-2 summarizes each of the Form Builder file types and characteristics.

The sample tables used in this chapter include the DEPARTMENT and EMPLOYEE tables. The contents of these tables have inferred relationships; for instance, a department will have one or more employees. This particular type of relationship is often called a master/detail relationship. It is also called a parent/child relationship. In order for this type of relationship to exist, the master, or parent, table must have a primary key. A primary key consists of one or more columns that cannot be empty and that must contain unique values for every record. These unique primary-key values are what the detail, or child, table stores and uses to refer to master table records. The detail table column that store values from the master table’s primary key are called the foreign key to the master table’s primary key. So in a master/detail relationship, the master table’s primary key is referred to by the detail table’s foreign key.

It is very common for database applications to use a single form to display data that is in a master/detail relationship. Form Builder makes it very easy to define master/detail relationships between data blocks. Once this is done, any canvas containing both the master and the detail data block will also have automatic query synchronization between the two, so the detail data block will only show those records that are related to the selected record in the master data block.

In order to experiment with this, you will modify the DEPARTMENT canvas so it displays two data frames: the current one for department data, and a new one that will show employees assigned to the selected department. To do this, you will create a new data block to get data from the EMPLOYEE table, and assign a master/detail relationship between the new Employee data block and the Department data block.

Follow the entire procedure described here. Within your Form Builder file, start the Data Block Wizard by either executing the Tools | Data Block Wizard command, or right-clicking on the Data Blocks item in the Forms node and selecting Data Block Wizard from the context menu that appears. On the Type page, specify that the data block will be based on a Table or View. On the Table page, specify that the data block should be based on the EMPLOYEE table. Move all six EMPLOYEE table columns from the Available Columns area to the Database Items area. Notice that the EMPLOYEE table includes a column called DEPARTMENT_ID. This will be the foreign key to the DEPARTMENT table’s primary key. The next page you will see is new: the Master/Detail page. Click on the Create Relationship button. From the list of tables that appears, select DEPARTMENT and click OK. Your screen should now look like the one shown in Figure 12-13.

Note that the field labeled Detail Item currently shows EMPLOYEE_ID. This is the primary key for the EMPLOYEE table, but it is not the correct item to serve as the foreign key back to the DEPARTMENT table. Open the drop-down list for the Detail Item field, and you will see the DEPARTMENT_ID item in the list. Select the DEPARTMENT_ID item from the list. You will see that not only did the Detail Item change to the DEPARTMENT_ID item you selected, but the Data Block Wizard also automatically placed DEPARTMENT_ID in the Master Item field. You can see the SQL join condition that this relationship creates in the following code block:

EMPLOYEE.DEPARTMENT_ID = DEPARTMENT.DEPARTMENT_ID

Now, click on the Next button. This takes you to the Finish page. Select Create the data block, then call the Layout Wizard and click on the Finish button. When the Layout Wizard starts, ensure that the Canvas field shows the canvas that contains your Department data block, and not New Canvas. Click on the Next button. On the Data Block page, note that the Data Block field is grayed out—that is because you have already selected the data block indirectly by calling the Layout Wizard from the Data Block Wizard. In the Available Items column, select every item except DEPARTMENT_ID. Click the Next button.

You’re close to the end. On the Items page, simply click on the Next button to accept all default values for item prompt, width, and height. On the Style page, select Tabular. On the Rows page, enter a frame title of Employees in this department: Increase the Records Displayed value to 5, and enable the Display Scrollbar option. On the Finish page, click the Finish button to close the Layout Wizard. You will now see your form in the Layout Editor, with data frames for both Department and Employees. Close your form to return to the Object Navigator. Notice that the Object Navigator now shows two frames beneath the canvas you were working with: the first frame holds the Department data block, and the second frame holds the Employee data block.

Running a master/detail form module is even easier than creating one. From within Form Builder, follow this process. First, select the Form module you want to run by clicking on the module name. If you have been following the exercises in this chapter, you will have only one form module available. Then, run the module using the Run button or the Program | Run Form menu command. When the form opens in the Forms Runtime program, populate it with data using the Execute Query button or the Query | Execute command. After that, select different Department records by clicking on either the records directly, or the Next Record and Previous Record buttons. Note that each time you select a different department record, the Employees frame changes to show only those employees in that department. Finally, use the Action | Exit menu command to close the Forms Runtime program and return to Form Builder.

In this section, you will cover the following points related to running a Form Builder application:

• Understanding the runtime environment
• Navigating a Form Builder application
• Understanding the two modes of Forms Runtime operation
• Retrieving data into a Form Builder application
• Inserting, updating, and deleting records
• Displaying database errors using the Help facility

You have now used Developer 2000’s Forms Runtime program to run your first form. This is a good time to go into detail about the Forms Runtime program’s purpose, requirements, and operational techniques. This section covers understanding the runtime environment and navigating the Form Builder application. You will also understand the two modes of Forms Runtime operation, and gain experience retrieving data into a Form Builder application. You will practice insert, update, and delete operations as well. Finally, you will learn how to display database errors using the Help facility.

Each of the Builder programs provides an environment for creating application modules. However, they do not run those modules. For that, Developer/2000 supplies runtime programs. There are three runtime programs—one each for the Form, Report, and Graphics Builders. The runtime programs must be present on the client machine when the application is distributed, but the development programs do not need to be, and generally are not. This allows the development tool to be rich with convenience-enhancing features, while the runtime program can be lean and efficient. It also allows companies to distribute applications to many users while only having to purchase development-tool licenses for the actual developers.

To run an application built in Developer/2000, you call the appropriate runtime program. When you are developing an application and testing it, you will usually call the Forms Runtime program directly from within the Builder you are using. This is done by either clicking on the Run button—which was introduced earlier—or using the Program | Run Form command.

If you want to run the Forms Runtime program independently of any Builder, you can do so by opening the Start menu in Windows environments and navigating to Programs | Developer 2000 | Forms Runtime. When you do, you will see a screen similar to the one depicted in Figure 12-14.

Using this screen, you can enter all the information necessary to open and run a form module: the filename (remember that it will be looking for an .fmx file), the userid and password for logging in to the database, and the alias for the database itself. There are also a number of other options, which are beyond the scope of discussion at this point.

To have the Forms Runtime program run the form you created in the previous section of this book, fill in the filename, userid, password, and database alias. When filled in, the Forms Runtime Options screen will look similar to the one shown in Figure 12-15. Once you have filled in the necessary information, click the OK button to run your form.

When you distribute an application, the client computers will need to have the runtime programs installed, as well as all the compiled files for your application. To enable you to start your application from a user-friendly icon and still specify runtime arguments, Developer/2000 provides a way for you to include the desired application’s name and other parameters as command line arguments. For instance, this is an example of the command line used to call the Developer/2000 "Benefits and Features" demo program:

"C:\Program Files\Ora8Home\BIN\F50RUN32.EXE"start20.fmx user@db_alias/password

Navigating a Form Builder application in the Forms Runtime program is relatively straightforward. This section will discuss the four parts of a standard Forms Runtime display: the Menu, Toolbar, Form Area, and Console.

The default menu used in the Forms Runtime program is shown here:

If you do not create a custom menu for your application, this is the menu that Forms Runtime provides. It is somewhat unusual in that its first menu category is not File. This is because a Runtime instance is specific to the .fmx file opened within it; close the .fmx file, and the Forms Runtime program will close as well. Thus there is no need for commands like File | Open.

The menus displayed are described as follows. The Action menu provides choices to commit data inserts, updates, and deletes; to clear all displayed records from the form (which does not remove them from the underlying tables—it simply blanks the form); to print the form; and to exit the Runtime program. The Edit menu provides the standard cut, copy, and paste commands, which operate at field-level granularity. Further down this menu, the Edit | Edit command opens a dialog box containing the contents of the currently selected field, with a convenient search-and-replace function for making repetitive changes within a large text field. The final choice on this menu, Edit | Display List, opens a List of Values (LOV) if the currently selected field has one.

Two other menus include the Query and Block menus. The Query menu provides functions to enter and utilize data-filtering criteria, including a very handy Counts Hits function to determine how many records will match your current criteria without having to actually retrieve the records. The Block menu provides navigational control, enabling the user to move between data blocks on a multiple-block canvas. The menu also includes a command to clear, or blank out, the records in the currently selected block. As with the Action | Clear All command, the Block | Clear command does not actually remove any data from the database; its function is purely cosmetic.

The Record menu provides standard navigation functions: previous and next record, along with scroll up and down (which act similar to a page down and page up within the data block’s frame). It also provides commands to insert, lock, and remove records—and "remove" will actually delete the record from the underlying table when the action is committed, unlike the "clear" command farther down the menu. There is also a convenient duplicate function that, when invoked within a new blank row, copies the values from whichever existing record was more recently current. Many of these functions are also available from the Runtime toolbar, which will be addressed shortly.

Other menus abound. The Field menu provides a subset of the functions described on the Record menu: move to previous or next, clear, and duplicate. In this case, each function operates at the field level instead of the record level. For instance, when invoked from a new blank record, the Field | Duplicate command copies the contents of that field from the record just above the new one and places the contents into the new record. This can be useful when entering repetitive data. The Window menu offers standard windows-management commands for applications that employ more than one window. The Help menu provides a limited amount of help: details about the field you are currently in, a reminder of the keyboard shortcut keys available, and information about errors, if any have occurred.

The next area for study is the toolbar. The default toolbar used in the Forms Runtime program looks like this:

If you do not create a custom toolbar canvas for your application, this is the toolbar Forms Runtime provides. Several of its buttons will undoubtedly be familiar to you, while others are specific to the Developer/2000 environment. Remember that you can not only create your own toolbar, you can create horizontal and vertical toolbars for your application, if you wish.

There are several remarks to be made about the items you will find in the Forms Runtime default toolbar. The Save button performs a SQL commit command, saving any inserts, updates, or deletions the user has performed. Its menu equivalent is Action | Save. The Print button prints the current form and its contents. Its menu equivalent is Action | Print. The Print Setup button provides standard options for page size, source, orientation, and margins. Its menu equivalent is Action | Print Setup. The Exit button closes the application and the Forms Runtime program. Its menu equivalent is Action | Exit. The Cut, Copy, and Paste buttons provide exactly the functions you would expect in any Windows program. Their menu equivalents are Edit | Cut, Edit | Copy, and Edit | Paste. Like their menu equivalents, the buttons operate at field-level granularity.

The Enter Query button places the user in a mode where they can enter query by example (QBE) data into the form. This function will be covered later in more detail. The button’s menu equivalent is Query | Enter. The Execute Query button performs a select statement against the database, using whatever criteria are present from the Enter Query mode. If no criteria have been entered, all records are returned. The button’s menu equivalent is Query | Execute. The Cancel Query button clears any criteria that were entered in Enter Query mode. It does not cause an additional select statement to be run, however, so the contents of the form will not change until the Execute Query function is performed again. The button’s menu equivalent is Query | Cancel.

The Previous Block button moves the user’s cursor to a prior data block on the current canvas. If the canvas has no prior data block but the form module contained additional canvases, the button causes the prior canvas to be displayed, with the cursor in the last data block on that canvas. The button’s menu equivalent is Block | Previous. The Next Block button moves the user’s cursor to the next data block on the current canvas. If the canvas has no succeeding data block but the form module contained additional canvases, the button causes the next canvas to display, with the cursor in the first data block on that canvas. The button’s menu equivalent is Block | Next.

The Prior Record button causes the user’s cursor to move up one record in the current data block. If the user is already on the block’s first record, the button does nothing. Its menu equivalent is Record | Previous. The Next Record button causes the user’s cursor to move down one record in the current data block. If the user is already on the block’s last record, the button moves the cursor to a blank row and allows the user to enter a new record. Its menu equivalent is Record | Next. The Insert Record button creates a blank row below the user’s current cursor location (or a blank form if the form is designed to show one record at a time) and allows the user to enter new data. The insertion will occur in the database when the user’s next commit occurs. The button’s menu equivalent is Record | Insert. The Remove Record button deletes the current record from the table. The deletion will occur in the database when the user’s next commit occurs. The button’s menu equivalent is Record | Remove. The Lock Record button attempts to lock the database row corresponding to the record at which the user’s cursor is currently located. Its menu equivalent is Record | Lock.

Finally, The Help button shows detailed information about the field in which the user’s cursor currently resides. This can be handy during development, but it is of little interest to most users, and so you may want to reprogram its function in a custom menu.

Beneath the default Forms Runtime toolbar is the form area. This is the area where your users will put most of their attention—as will you as a developer. Because this area simply displays the canvases you create, it will be covered in greater detail later in the book, in the portions dealing with customizing the canvas contents.

At the bottom of the Forms Runtime display is the console area. This area consists of two lines. The first line displays Oracle messages such as "FRM-40400: Transaction complete: 10 records applied and saved.", as well as informational items like "Enter a query; press F8 to execute, ctrl-q to cancel." (By the way, that ctrl-q command is case sensitive, so if you have the caps lock key on, it will not work.) The second line displays the record number the user’s cursor is on, along with status information such as "Enter-Query" when the user is in Enter Query mode.

The phrase "Enter Query mode" has been used a few times already in this section, and it is time to explain this mode in more detail. A form running in the Forms Runtime program is always in either of two states: the normal insert/update/delete mode, or Enter Query mode. When the user invokes Enter Query mode, the form blanks its contents and waits for the user to enter data into the form that will serve as filtering criteria for a where clause when the user invokes the Execute Query command. The user can enter only one record’s worth of criteria, although they can enter criteria into multiple fields on that record. This means that there is no record-to-record movement while in the Enter Query mode.

To test this operation, follow these steps. First, open your sample application in the Forms Runtime program. Then, invoke Enter Query mode by either clicking on the Enter Query button, performing the Query | Enter command, or pressing the f7 function key. Next, move to the Department Name field and type Maintenance. Finally, execute the query by either clicking on the Execute Query button, performing the Query | Execute menu command, or pressing function key f8. You will see that the Maintenance department record will appear, along with the employee record related to that department. You are now back in the normal mode—the only difference is that you are viewing a filtered subset of the available records. Since you are no longer in Enter Query mode, executing another query will produce a result set containing all records.

By doing the chapter exercises up to this point, you have already retrieved data into a Form Builder application by opening the application with the Forms Runtime program and doing the Execute Query command. When you retrieve records without using selection criteria to limit the number of records returned, you are performing an unrestricted query. It’s the same as using a select statement with no where clause. Unrestricted queries win points for simplicity, but they can hurt overall application performance by clogging the available network bandwidth with records the user does not really need.

The solution to this can be a restricted query, which utilizes selection criteria to limit the number of records returned. In the last section you used Enter Query mode to limit the department records retrieved to just those matching the name Maintenance. That was a restricted query. You can perform more sophisticated restricted queries as well, using Oracle wildcard characters and SQL statements.

There are a number of characters you can use as wildcards and comparison operators when entering field criteria for Enter Query mode. These characters are shown in Table 12-3.

Each of these would be valid criteria to enter into a field in Enter Query mode:

• Sm_th%
• Pine Ave%
• >50000
• <50000
• >G
• <01-JUL-2001
• <>Backup
• <>5
• !=5
• >=5
• <=5

If you enter search criteria into more than one field while entering a query, all of your search criteria will be combined with and clauses in the query’s where clause.

You can also employ full SQL statements to restrict the records returned in a query, and even to control their sort order. To try this, follow the process described here. First, open your sample application in the Forms Runtime program, if it is not open already. If necessary, execute a query to populate the Department frame with all four departments. Then, place your cursor on the department record Information Technology. Place your cursor on any record in the Employees data frame. Next, invoke Enter Query mode by either clicking on the Enter Query button, performing the Query | Enter menu command, or pressing function key f7. After that, move to the Last Name field and type :LAST_NAME. (The variable name does not have to be the same as the column name, but you might find it provides more clarity to make it so.) The colon at the beginning of the name indicates that the name is to be treated as a variable. Then, move to the Hire Date field and type :HIRE_DATE, and move to the Salary field and type :SALARY. Your screen should now look like Figure 12-16.

Continuing the process, execute the query by either clicking on the Execute Query button, performing the Query | Execute menu command, or pressing function key f8. You will see a dialog box titled Query/Where. Enter the following text as your query’s where clause. After doing so, click OK. You will see that the number of Information Technology employees displayed has dropped from four to two, and that the two shown are the ones who fulfill the criteria you specified.

HIRE_DATE between ‘01-JAN-1998’ and ‘31-DEC-1999’
and SALARY < 100000
order by LAST_NAME asc

So far in this chapter, you have only queried existing records. Now it is time to add records of your own, change their contents, and delete them. Before you do, it is important to note that each of these Data Manipulation Language (DML) functions occurs with the records you are looking at locally; changes you make are not made in the underlying database until you commit the changes with a Save command. Each of the three topics that follows will assume that you have your sample application open in the Forms Runtime program.

You can initiate a record-insert process in any of the following ways: choosing the Record | Insert menu option, clicking on the Insert Record button in the toolbar, or navigating to the last record in the table, and then use the down arrow cursor key to proceed to a blank record. Use one of these methods now to add a new record to the DEPARTMENT table. Specify that the Department ID is 5 and the Department Name is Quality Assurance. Now add two employees with information shown in Table 12-4. Commit the records to the database using the Save command.

Commit the records to the database using the Save button.

Updating records is very straightforward. You simply navigate to the desired record, move your cursor to the field you want to change, make the change, and then commit the change with a Save command. Experiment with this process by changing the hire date for Lois Paterson to March 2. Be sure to commit the change.

Deleting records is even easier than updating them. The process begins with navigating to any field in the desired record. Next, delete the record by either performing the Record | Remove menu command, clicking on the Remove Record button in the toolbar, or pressing shift-f6 keys on your keyboard. Finally, commit the change with a Save command. Experiment with this process by deleting the record for Bob Unca. Be sure to commit the deletion.

As a foundation for this topic, add the record shown in Table 12-5 to your sample application. If you have been following the exercises, this insert should fail when you try to commit it. As you probably noticed, the new record has the same Employee ID as a record that already exists. The Employee ID is the primary-key column for the EMPLOYEE table, so all records must have unique Employee ID values.

Look down at the console area of your Forms Runtime program, and you will see a message like the one shown in the following code block.

FRM-40508: ORACLE error: unable to INSERT record.

Unfortunately, the error message doesn’t say why the insert failed. To get more information, you can use the Forms Runtime program’s Help facility. Here is the process. First, choose the Help | Display Error menu option. You should see a dialog box similar to the one shown in Figure 12-17. The top half of the dialog box shows a segment of the code the application was attempting to execute. The bottom half has more useful information: the Oracle database error message. Here, you learn that the table has a unique constraint that your new record would violate. When you are finished reading the error information, click the OK button to close the Database Error dialog box and exit the Forms Runtime program without saving the new record.

In this chapter, you have covered a substantial amount of information on Form Builder. You started by learning about the Form Builder environment: the main Form Builder executables (Form Builder, Form Compiler, and Forms Runtime), and the main Form Builder components (form modules, menu modules, PL/SQL libraries, and Object libraries). You then learned the main objects in a form module: window, canvas, block, and item.

In the next section, you covered the basics of creating form modules. You learned the four steps involved in creating and using a Developer/2000 form: creating a new form module in Form Builder, creating a data block to supply the new form module with data, creating a form layout depicting the data block’s items on one or more canvases, and saving, compiling, and running the module using the Forms Runtime program. You then took the steps to actually create a new form module, and then utilized the Data Block Wizard to create data blocks to use within the module to access database data. Once you had access to data, you employed the Layout Wizard to create a basic form layout on a canvas, which you then viewed in the Layout Editor. Next, you modified the layout by re-running the Layout Wizard in reentrant mode and changing specific parameters in the already-existing layout. You then learned about compiling form modules, and once your sample application was compiled, you ran it using the Forms Runtime program.

Next you learned to identify Form Builder file formats and their characteristics. The Form module file types are .fmb (Form Module Binary), .fmx (Form Module Executable), and .fmt (Form Module Text). The Menu module file types are .mmb (Menu Module Binary), .mmx (Menu Module eXecutable), and .mmt (Menu Module Text). The PL/SQL library file types are .pll (PL/SQL Library), .plx (PL/SQL eXecutable), and .pld (PL/SQL Documentation). The Object library file types are .olb (Object Library Binary) and .olt (Object Library Text). To finish up the section, you learned how to use the Data Block Wizard to create data blocks with relationships, generating a master/detail relationship between two data blocks. You then used the Layout Wizard to modify your single-table form layout so that it displayed both tables involved in the master/detail relationship. When you ran the resultant form in the Forms Runtime program, you saw how Developer/2000 automatically synchronizes the two tables so the contents of the details table changes each time a different master record is selected.

In the final section, you learned about running a Form Builder application. You started by getting an overview of the runtime environment, which included information about how to specify information about your compiled forms on the Forms Runtime command line. You then learned more about navigating a Form Builder application, learning details about the four sections of the runtime display: the menu, the toolbar, the form area, and the console. Next, you discovered the difference between the two modes of Forms Runtime operation: normal insert/update/delete mode and Enter Query mode. Then, you learned how to control what data a Form Builder application retrieves, either by employing field-level criteria utilizing the _, %, <, >, =, and != operators, or by implementing form variables allowing you to use statements similar to SQL where clauses. You learned the fine points of inserting, updating, and deleting records, including how to save time by using record-level and field-level duplicate commands to speed the entry of repetitive data. Finally, you learned how to display detailed database error information using the Help facility in the Forms Runtime program. All in all, the material in this chapter comprises about 12 percent of material tested on OCP Exam 3.