0&&parent.frames.length) { d=parent.frames[n.substring(p+1)].document; n=n.substring(0,p);} if(!(x=d[n])&&d.all) x=d.all[n]; for (i=0;!x&&i<(a.length-2);i+=3) if ((x=MM_findObj(a[i]))!=null){document.MM_sr[j++]=x; if(!x.oSrc) x.oSrc=x.src; x.src=a[i+2];} } //Define globals: var ans = new Array; var done = new Array; var score = 0; var layerDisp ans[1] = "19"; ans[2] = "14"; ans[3] = "8"; //Define function to check object existence and return reference: function findObj(n, d) { //v4.0 var p,i,x; if(!d) d=document; if((p=n.indexOf("?"))>0&&parent.frames.length) { d=parent.frames[n.substring(p+1)].document; n=n.substring(0,p);} if(!(x=d[n])&&d.all) x=d.all[n]; for (i=0;!x&&i<(args.length-2); i+=3) if ((obj=findObj(args[i]))!=null) { v=args[i+2]; if (obj.style) { obj=obj.style; v=(v=='show')?'visible':(v='hide')?'hidden':v; } obj.visibility=v; } } //Define function to show one layer and hide others: function show(whichLayer) { if(layerDisp) { showHide(layerDisp,'','hide'); } showHide(whichLayer,'','show'); layerDisp = whichLayer; } //Define function to keep score and display feedback: function Engine(question, answer, layerSuffix) { if (answer != ans[question]) { show('incorrect'+layerSuffix); } else { if (!done[question]) { score++; } show('correct'+layerSuffix); } done[question] = -1; } //Define function to verify answer for checkbox: function checkAnswer(question, whichform, whichbox) { var checkTotal = 0; with (whichform) { for (i = 0; i < whichbox.length; i++) { if (whichbox[i].checked == true) { checkTotal = checkTotal + eval(whichbox[i].value); } } } Engine(question,checkTotal,question*10); } //Define function to show correct answer for checkbox: function showAnswer(question, whichform, whichbox) { var residue = ans[question]; var currAnswer = 1; if(layerDisp) { showHide(layerDisp,'','hide'); } with (whichform) { for (i = 0; i < whichbox.length; i++) { if ((residue>0) && (residue%(currAnswer*2)==currAnswer)){ whichbox[i].checked = true; residue-=currAnswer; } else { whichbox[i].checked = false; } currAnswer*=2; } } } //Define function to init a form function clearForm(form) { //Set all checkboxes and radio buttons on form to unchecked: for (xx=0; xx < form.elements.length; xx++) { if (form.elements[xx].type == 'checkbox' | form.elements[xx].type == 'radio') { form.elements[xx].checked = false; } } } //Define function to init all forms on reload: function InitForm() { clearForm(document.form1) clearForm(document.form2) clearForm(document.form3) } // End --> //-->

Creating a Master-Detail Form

Purpose

In this lesson you learn how to create a basic master-detail Form with Oracle Forms Builder 10g.

Topics

This lesson discusses the following:

 Overview
 Prerequisites
 Setting Up the Development Environment

Building a Data Block

 Adding Non Base Table Items to a Data Block
 Building a Detail Block

Creating a New Canvas

 Creating a New Window
 Building a Control Block
 Controlling Navigation

Modifying the Layout

 Adding Functionality with Code
 Summary

Viewing Screenshots

 Place the cursor on this icon to display all screenshots. You can also place the cursor on each icon to see only the screenshot associated with it.


Overview

The lesson guides you through many of the tasks that you must perform when using Oracle Forms Builder to create a master-detail form. You use the Data Block Wizard to quickly generate the master and detail blocks, then you add a control block (one with no base table items) to the form. You add some non base table items to the data blocks, and use PL/SQL triggers to add functionality to the form.

Back to Topic List

Scenario

You are designing a human resource application. One of the main forms in this application is intended to enable HR clerks to maintain department and employee information. You have decided to implement this as a master-detail form that displays one department with all employees in the department.

Prerequisites

Back to Topic List

In order for this lesson to work successfully, you need to have performed the following:

1.

Install Oracle Developer Suite 10g.

 

2.

Install the sample schemas to use in this lesson. See Installing the Sample Schemas and Establishing a Database Connection.

You do not need to create a database connection in JDeveloper—just have the HR schema available so that you can connect to it in Forms. Ensure that the tnsnames.ora file in the Network\Admin directory of the Developer Suite installation contains an entry to connect to the database where the HR schema is available.

 

0&&parent.frames.length) { d=parent.frames[n.substring(p+1)].document; n=n.substring(0,p);} if(!(x=d[n])&&d.all) x=d.all[n]; for (i=0;!x&&i<(a.length-2);i+=3) if ((x=MM_findObj(a[i]))!=null){document.MM_sr[j++]=x; if(!x.oSrc) x.oSrc=x.src; x.src=a[i+2];} } //-->

Setting Up the Development Environment

Back to Topic List

Oracle Developer Suite 10g includes a standalone version of Oracle 10g Containers for J2EE (OC4J), which you can use as a Web listener and a servlet container to test Forms applications. To get ready to build and test a form, perform the following steps:

1.

From the Windows Start menu, select Programs | Oracle Developer Suite iDS10g | Forms Developer | Start OC4J Instance.

Move your mouse over this icon to see the image

 

2.

You can minimize the command prompt window when the window displays the message "Oracle Application Server Containers for J2EE 10g (9.0.4.0.0) initialized".

Move your mouse over this icon to see the image

Note: Do not close the OC4J Instance window. When you want to stop OC4J, choose Programs | Oracle Developer Suite iDS10g | Forms Developer | Stop OC4J Instance from the Windows Start menu.

 

3.

From the Windows Start menu, select Programs | Oracle Developer Suite iDS10g | Forms Developer | Forms Builder.

Move your mouse over this icon to see the image

 

4.

When Forms Builder starts, an empty form is automatically created named Module1. Select the Module1 name, then click it again to change the name of the form. Type in the name HR and press Enter.

Move your mouse over this icon to see the image

 

5.

Click Save Save button. By default, Forms saves files in the \bin directory of your Developer Suite installation. You may want to save it in a different directory; if desired, you can create a new directory called MyForms in which to save the application.

Move your mouse over this icon to see the image

 

6.

Connect to the database as the HR user by clicking Connect Connect button and then entering the userid, password, and connect string for the database that contains the HR or HR8 schema (see Prerequisites).

Move your mouse over this icon to see the image

 

0&&parent.frames.length) { d=parent.frames[n.substring(p+1)].document; n=n.substring(0,p);} if(!(x=d[n])&&d.all) x=d.all[n]; for (i=0;!x&&i<(a.length-2);i+=3) if ((x=MM_findObj(a[i]))!=null){document.MM_sr[j++]=x; if(!x.oSrc) x.oSrc=x.src; x.src=a[i+2];} } //-->

Building a Data Block

Back to Topic List

Transactional applications need to be able to insert, update, and delete records from the database. The data block is the mechanism that makes this possible in a Forms application. A data block is a logical grouping of items that usually are associated with a single database table, view, or set of stored procedures, although the data block may contain non base table items also.

Forms Builder includes a Data Block Wizard to make it possible to easily create a data block and a Layout Wizard to lay out the items on the form's canvas. To use the Data Block Wizard and the Layout Wizard to create a data block and define its layout, perform the following steps:

1.

Select the Data Blocks node in the Object Navigator, then click Create Create button .

Move your mouse over this icon to see the image

 

2.

In the New Data Block dialog, ensure that Use the Data Block Wizard is selected, then click OK .

Move your mouse over this icon to see the image

 

3.

If the Welcome to the Data Block Wizard page displays, click Next >.

You can base a data block on a database table or view, or on a set of stored procedures in which you can define complicated query, insert, update, and delete logic. For this application you base the block on a table, so ensure that Table or View is selected, then click Next >.

Move your mouse over this icon to see the image

 

4.

In the Table or view field, type departments for the table name, then click Refresh. Click the right double arrow button Right double arrow button to move all available columns to the Database Items to be included in the block, then click Next >.

Move your mouse over this icon to see the image

 

5.

Accept the default name for the block, which is the same as the table name, and click Next >.

Move your mouse over this icon to see the image

 

6.

On the final page of the Data Block Wizard, click Finish to create the data block and invoke the Layout Wizard.

Move your mouse over this icon to see the image

 

7.

If the Welcome to the Layout Wizard page displays, click Next >.

Accept the defaults and click Next > to lay out the items on a new content canvas.

Move your mouse over this icon to see the image

Note: A content canvas is one that fills the entire window. You can display only one content canvas at a time within a window. Other canvas types are toolbar, stacked, and tab canvases, which typically reside in a portion of a window while the content canvas remains at least partially visible.

 

8.

You decide to display only the department id and department name, because the location id and manager id are just numbers that are not meaningful to the user.

Control-click DEPARTMENT_ID and DEPARTMENT_NAME in the Available Items list, then click the right single arrow button Right singlearrow button to move them to the Displayed Items list.

Move your mouse over this icon to see the image

Click Next >.

 

9.

Click Next > to accept the default item prompt, width, and height for both items.

Move your mouse over this icon to see the image

 

10.

Click Next > to accept the Form layout.

Move your mouse over this icon to see the image

 

11.

For the Frame Title enter Department, then click Next >.

Move your mouse over this icon to see the image

 

12 .

On the final page of the Layout Wizard, click Finish to create the canvas and open it in the Layout Editor.

Move your mouse over this icon to see the image

 

13.

In the Layout Editor, you can perform any cosmetic changes that you want to the layout.

First, to ensure that changes to the layout frame are preserved, double-click the frame (the line that surrounds the items) to invoke its Property Palette.

Move your mouse over this icon to see the image

Note: You can leave the Property Palette open; it displays the properties for whatever object is currently selected. If you should close the Property Palette, you can reopen it by selecting T ools | P roperty Palette from the Forms Builder menu. Alternatively, double-clicking most Forms objects opens the Property Palette for that object.

 

14.

In the Property Palette for the Frame, change the property Update Layout from Automatically to Manually.

Move your mouse over this icon to see the image

 

15.

Enlarge the frame by clicking the handle in its lower right corner and dragging it downward and to the right.

Move your mouse over this icon to see the image

 

16.

Click the Rounded Rectangle tool Rounded Rectangle tool on the toolbar at the left of the Layout Editor. With the mouse, click and drag a rectangle around the department id and the department name.

Move your mouse over this icon to see the image

 

17.

With the rectangle selected in the Layout Editor, click Fill Color Fill Color button on the Layout Editor toolbar and pick a color for the rectangle. (You may need to click the arrow Toolbar bottom arrow button at the bottom of the toolbar to scroll down to the Fill Color button if the Layout Editor window is too small to display all the tools.)

Move your mouse over this icon to see the image

 

18. Click Save Save button to save your form.

0&&parent.frames.length) { d=parent.frames[n.substring(p+1)].document; n=n.substring(0,p);} if(!(x=d[n])&&d.all) x=d.all[n]; for (i=0;!x&&i<(a.length-2);i+=3) if ((x=MM_findObj(a[i]))!=null){document.MM_sr[j++]=x; if(!x.oSrc) x.oSrc=x.src; x.src=a[i+2];} } //-->

Adding Non Base Table Items to a Data Block

Back to Topic List

The Departments block is based on the Departments database table, and so far all the items in the block represent columns in that table. However, there is often a need to include items in the block that are not in the base table for the block. One common use of non base table items is to display information from related tables.

The Departments block contains two items that do not appear on the canvas: Manager_Id and Location_Id. You can see these items in the Object Navigator. Instead of displaying these numbers which are not meaningful to the user, you want to display the manager's name and the location city, state/province, and country.

To add non base table items to display related information, perform the following steps:

1.

In the Layout Editor, click the Display Item tool Display Item tool. Click a point on the canvas just below the department name to create a Display Item. Then click the Display Item tool again, and click a point on the canvas just below the display item that you just created.

Move your mouse over this icon to see the image

Note: A Display Item is similar to a Text Item (used for Department_Id and Department_Name), except that it does not accept user input. If you want users to be able to change these values, you should use a Text Item.

 

2.

Control-click each of the Display Items to select them both. Alternatively, you can drag the mouse around both items to select them, being careful to include only those two items.

Move your mouse over this icon to see the image

 

3.

The Property Palette should now display the properties for both display items. If both display items have the same value for a property, the value is displayed. If the two display items have different values for a property, asterisks are displayed for that property.

Selecting both display items enables you to change properties to identical values for both items .

Change the following properties in the Property Palette for both display items:

Property Value
Maximum Length 65
Database Item No
Height 14
Prompt Attachment Offset 5

Hint: If you have trouble locating a particular property, type its name in the Find box at the top right of the Property Palette. As soon as you have typed enough letters to uniquely identify the property, Forms Builder highlights that property in the Property Palette.

Move your mouse over this icon to see the image

 

4.

Change the following properties for only the first display item by selecting it singly (click an area outside the two display items to deselect them, then click the first display item):

Property Value
Name LOCATION
Prompt Location

Change the following properties for the second display item only:

Property Value
Name MANAGER
Prompt Manager

Move your mouse over this icon to see the image

 

5.

Move the second Display Item up slightly so it is just under the first. An easy way to move an item a short distance is to select it and then use the arrow keys to nudge the item to its desired position.

Move your mouse over this icon to see the image

 

6.

To ensure that all items are properly aligned, select all items (do not include the prompts in this selection) and click Align Left Align Left button on the Layout Editor toolbar.

Move your mouse over this icon to see the image

 

7.

In the Layout Editor, click into the item prompts Department Name and Department Id. Delete the word "Department" from both prompts. Alternatively, you can change the item prompts in the Property Palette for each item.

Move your mouse over this icon to see the image

 

8.

Click Save Save button to save the form, then click Run Form Run Form button to test it (be sure that you have started OC4J -- see Step 1).

When the form starts running, click Execute Query Execute Query button to display data in the form. Click Next Record Next Record button and Previous Record Previous Record button to navigate between records.

Move your mouse over this icon to see the image

Note: At this point the Location and Manager items are not populated; you write code to populate these items in a later step.

 

9.

Click Exit Form Exit Form button to exit the application, then close the browser window.

CAUTION: Always exit the application by clicking the Exit Form button prior to closing the browser. Failure to do this may cause the Runform process to hang; if this happens, you need to open Task Manager and end the ifweb90.exe process.

 

Building a Detail Block

Back to Topic List

Forms Developer gives you the ability to quickly define additional data blocks. The Data Block Wizard offers you the option of defining relations between the new blocks and any existing blocks in the form.

Now that you have a Departments block, you would like to be able to additionally display all the employees in each department. To create a detail block based on the Employees table, perform the following steps:

1.

Select Departments in the Object Navigator, then click Create Create button . This ensures that the new block immediately follows Departments in the Object Navigator tree. The sequence of the blocks determines default navigation and also the sequence of commit processing.

Move your mouse over this icon to see the image

Note: If you happen to create an object in the wrong order, you can drag its icon to the desired position in the Object Navigator. For example, if the sequence of the blocks is Employees, then Departments, you can drag the Employees block icon to a position after Departments under the Data Blocks node.

 

2.

In the New Data Block dialog, ensure that Use the Data Block Wizard is selected, then click OK .

Move your mouse over this icon to see the image

 

3.

If the Welcome to the Data Block Wizard page displays, click Next >.

You are basing the block on a table, so ensure that Table or View is selected, then click Next >.

Move your mouse over this icon to see the image

 

4.

In the Table or view field, type employees for the table name, then click Refresh. Click the right double arrow button Right double arrow button to move all available columns to the Database Items to be included in the block, then click
Next >
.

Move your mouse over this icon to see the image

 

5.

Because there is already a block in the form, you are offered the option to relate this new block to it. Click C reate Relationship.... A dialog displays any foreign keys defined in the database between these two tables. Click OK to base the relationship between the blocks on the EMP_DEPT_FK foreign key.

Move your mouse over this icon to see the image

 

6.

Click Next > to accept the join condition created automatically by Forms Builder based on the foreign key, then on the next screen click Next >, and then Finish.

Move your mouse over this icon to see the image

 

7.

If the Welcome to the Layout Wizard page displays, click Next >.

Click Next > to accept the default of laying out the items on the existing canvas.

Move your mouse over this icon to see the image

 

8.

In the Available Items column, select for display all items except Manager_Id and Department_Id, and then click the single right arrow button Single Right Arrow button to move them to the Displayed Items column.

Move your mouse over this icon to see the image

 

9.

Change the following item prompts:

Name Prompt
EMPLOYEE_ID ID
PHONE_NUMBER Phone
COMMISSION_PCT Commission %

Click Next >.

Move your mouse over this icon to see the image

 

10.

You want to display several employees at a time in a tabular format, so select the T abular option and click Next > .

Move your mouse over this icon to see the image

 

11.

For the Frame T itle, type Employees. Change R ecords Displayed to 5. Check Display S crollbar, then click Next > , and on the final screen, click Finish .

Move your mouse over this icon to see the image

 

12.

The Layout Editor opens showing how five employee records are displayed. If you maximize the Layout Editor window, you can see that the canvas has been enlarged to accommodate all the items, but the viewport, which is the area that shows data in the running form, does not show the entire record.

Move your mouse over this icon to see the image

Note: Although in this case you could enlarge the viewport so that the entire record is visible, for this exercise you use a different technique that shows you one way to make it possible for users to view more data than can display in a single window.

 

13.

Restore the Layout Editor window to its original size. Observe that the Object Navigator shows that a relation, some triggers, and some program units have been automatically created. These are for master-detail coordination.

Move your mouse over this icon to see the image

 

14.

Click Save Save button to save the form, then click Run Form Run Form button to test it (be sure that you have started OC4J -- see Step 1).

When the form starts running, click Execute Query Execute Query button to display data in the form. Click Next Record Next Record button. Notice that the form automatically displays employees associated with the next department record. Click Next Block Next Block button to navigate to the Employees block. You must tab through the items to be able to see them all.

Move your mouse over this icon to see the image

In the next step, you see one technique to better display data that does not fit in a single window.

 

15.

Click Exit Form Exit Form button to exit the application, then close the browser window.

 

Creating a New Canvas

Back to Topic List

By running the master-detail form, you can see that Forms has already done the job of building and coordinating the master-detail blocks for you. The form is useful as it is, with only the default functionality that Forms has built for you. In the remaining steps, you make adjustments to enhance the usability of the form. The first thing you do is create a new canvas to display most of the details about an employee.

In addition to the content canvas, which takes up all the space in the window, you can create three other types of canvases:

 Stacked canvas Can display in front of a content canvas and is usually smaller than the content canvas
 Tab canvas Specialized type of stacked canvas containing multiple tab pages
 Toolbar canvas Canvas that displays above or to the left of the content canvas, typically used for buttons

You decide to create a second content canvas and move some of the items from the Employees block to that canvas. In addition, you define on that second canvas some items that are synchronized with the items that appear on the main canvas.

To create the second content canvas, move some of the Employees block items to it, and create some synchronized items, perform the following steps:

1.

Select the canvas in the Object Navigator and click Create Create button.

Move your mouse over this icon to see the image

 

2.

Change the names of both canvases by clicking the name in the Object Navigator, then clicking again and overtyping the existing name. Call the original canvas MAIN_CV and the new canvas EMP_DETAILS_CV.

Move your mouse over this icon to see the image

Note: Alternatively you could change the name in the Property Palette.

 

3.

Open the MAIN_CV canvas in the Layout Editor. In the Employees block, control-click (or drag the mouse) to select the Email, Phone_Number, Hire_Date, Salary, and Commission_Pct items. Choose E dit | Cu t from the menu.

Move your mouse over this icon to see the image

 

4.

Select the EMP_DETAILS_CV canvas from the Canvas pop-up list at the top left of the Layout Editor and select EMPLOYEES from the Block pop-up list. Choose E dit | P aste from the menu. Drag the items to a location near the upper left corner of the canvas.

Move your mouse over this icon to see the image

 

5.

With all items still selected, open the Property Palette and change the following properties:

Property Value
Number of Items Displayed 1
Prompt Attachment Edge Start
Prompt Attachment Offset 5

Then drag the items to arrange them on the canvas, leaving some room at the top for the synchronized items that you create next.

Move your mouse over this icon to see the image

 

6.

Change the background color of the canvas. Click a blank spot on the canvas so that the Property Palette displays the canvas properties. Change the Background Color property to a color of your choosing.

Move your mouse over this icon to see the image

 

7.

Select MAIN_CV from the Canvas pop-up list of the Layout Editor. Select together the Employee_Id, First_Name, and Last_Name items. From the menu, choose E dit | C opy.

Move your mouse over this icon to see the image

 

8.

Select EMP_DETAILS_CV from the Canvas pop-up list of the Layout Editor. From the menu, choose E dit | P aste.

Move your mouse over this icon to see the image

 

9.

Change the property values as you did in step 5, then change the names of each of the items to M_EMPLOYEE_ID, M_FIRST_NAME, and M_LAST_NAME (the M is just an indication that these items mirror, or are synchronized with, other items).

In the Synchronize with Item property for each item, select from the pop-list the item with which this item should be synchronized. For example, M_Employee_Id should be synchronized with Employee_Id.

Change other individual properties as shown:

Item Property Value Note : If you fail to set these properties, the form still functions, but you receive annoying messages when you run the form from Forms Builder. If you would prefer not to set these properties, you can ignore the messages.
M_EMPLOYEE_ID and M_LAST_NAME

Required

No

M_FIRST_NAME and
M_LAST_NAME

 

Data Length Semantics
Maximum Length

Null
0

Drag the items to the top of the canvas and arrange them near the top.

Move your mouse over this icon to see the image

 

10.

Open the MAIN_CV canvas again. To ensure that changes to the layout of the Employees block are preserved, click its Frame (the line that surrounds the items in the Employees block). In the Property Palette for the Frame, change the property Update Layout from Automatically to Manually.

Move your mouse over this icon to see the image

 

11.

Move the scrollbar from the far right of the MAIN_CV canvas to a position just to the right of the remaining items, then resize the frame width appropriately.

Move your mouse over this icon to see the image

 

12.

Click Save Save button to save the form, then click Run Form Run Form button to test it (be sure that you have started OC4J -- see Step 1).

When the form starts running, click Execute Query Execute Query button to display data in the form. Click Next Block Next Block button to navigate to the Employees block. You still must tab through the items to be able to see them all, but at least now the employee detail displays all in one window. You may notice that navigation through the items on the detail page is not what you might expect; you change the navigation in a later step.

Move your mouse over this icon to see the image

Note: You could use a stacked canvas, rather than a content canvas, to display the employee details. If you had a larger number of items, you could use a tab canvas to display them, arranging the items on different tab pages.

 

13. Click Exit Form Exit Form button to exit the application, then close the browser window.

Creating a New Window

Back to Topic List

Because you have two content canvases and one window, you can display only one content canvas at a time. Both canvases display in Window1 that Forms Builder created by default.

If you would like to enable users to view both canvases at once, you can create another window. To create a new window and display the EMP_DETAILS_CV canvas in it, perform the following steps:

1.

Select the window in the Object Navigator and click Create Create button.

Move your mouse over this icon to see the image

2.

Change the names of both windows by clicking the name in the Object Navigator, then clicking again and overtyping the existing name. Call the original window MAIN_WIN and the new window EMP_DETAILS_WIN.

Move your mouse over this icon to see the image

Note: Alternatively you could change the name in the Property Palette.

 

3.

In the Property Palette for each window, change its Title property. This changes the title that displays in each window of the running form.

Change the title for MAIN_WIN to Departments and Employees. Change the title for EMP_DETAILS_WIN to Employee Details.

Move your mouse over this icon to see the image

 

4.

Select the EMP_DETAILS_CV canvas in the Object Navigator. In the Property Palette, change its Window property by selecting EMP_DETAILS_WIN from the pop-up list.

Move your mouse over this icon to see the image

 

5.

In the Layout Editor for the MAIN_CV canvas, place your cursor at the point where you want the new window to be displayed, and make note of the X and Y coordinates shown at the bottom of the Layout Editor window. In this case, the X and Y coordinates are 210 and 120.

Move your mouse over this icon to see the image

 

6.

In the Property Palette for the EMP_DETAILS_WIN window, set the X Position and Y Position properties to the values of the X and Y coordinates in the previous step. The example shows setting X Position to 210 and Y Position to 120.

Move your mouse over this icon to see the image

 

7.

Open the EMP_DETAILS_CV canvas in the Layout Editor and resize the Viewport (black rectangle) so that it is just large enough to display all the items. You may need to maximize the Layout Editor window to be able to see the original lower right point of the viewport.

Move your mouse over this icon to see the image

Note: Resizing the viewport actually resizes the window in which the canvas is displayed. It is a visual way to change the height and width properties of the window without the need to change them directly in the Property Palette.

 

8.

Click Save Save button to save the form, then click Run Form Run Form button to test it (be sure that you have started OC4J -- see Step 1).

When the form starts running, click Execute Query Execute Query button to display data in the form. Click Next Block Next Block button to navigate to the Employees block. Tab through the items so that the separate window is invoked.

Move your mouse over this icon to see the image

Design suggestion: Because you can now see the details along with the main canvas, you may decide to eliminate the synchronized items from the detail canvas.

It may also be helpful to differentiate the current row on the main canvas visually, such as by specifying a different background color for the current item. You can do this by:

 Defining a visual attribute (see the online help topic Creating a Named Visual Attribute)
 Setting a property of the Employees block called Current Record Visual Attribute group to the name of the visual attribute that you defined

 

9. Click Exit Form Exit Form button to exit the application, then close the browser window.

Building a Control Block

Back to Topic List

Although most data blocks are based on tables, views, or stored procedures, you can create blocks that are not related to the database. Such blocks are referred to as Control Blocks. Control Blocks typically contain buttons, calculated items, or other objects that pertain to the form as a whole, rather than just a single block.

To create a control block to display the sum of department salaries, perform the following steps:

1.

Select the Employees block in the Object Navigator and click Create Create button.

Move your mouse over this icon to see the image

 

2.

Select the option to Build a new data block m anually, then click OK .

Move your mouse over this icon to see the image

 

3.

In the Property Palette for the new block, set Name to CONTROL and set Single Record to Yes. The Single Record property is appropriate for a Control Block because it does not contain multiple records.

Move your mouse over this icon to see the image

Note: Single Record must be set to Yes for a Control Block that contains a summary calculated item.

 

4.

In the Layout Editor, open the MAIN_CV canvas. Select CONTROL from the Block pop-up list.

Move your mouse over this icon to see the image

 

5.

Click the Display Item tool Display Item tool, then click in the canvas to the right of the Manager item.

Move your mouse over this icon to see the image

 

6.

Using the Property Palette, set the following properties on the new item:

Property Value
Name SAL_SUM
Data Type Number
Maximum Length 14
Format Mask $9,999,999.00
Calculation Mode Summary
Summary Function Sum
Summarized Block EMPLOYEES
Summarized Item Salary
Height 14
Prompt Total Salaries
Prompt Attachment Edge Top

Move your mouse over this icon to see the image

 

7.

A summarized block must have its Query All Records or Precompute Summaries property set to Yes so that the sum includes all records, not only those already retrieved into the block. In the Property Palette for the Employees block, set one of these properties to Yes.

Move your mouse over this icon to see the image

 

8.

Click Save Save button to save the form, then click Run Form Run Form button to test it (be sure that you have started OC4J -- see Step 1).

When the form starts running, click Execute Query Execute Query button to display data in the form. Click Next Record Next Record button to navigate to a department with more than one employee.

Click Next Block Next Block button to navigate to the Employees block. Tab through the items so that the separate window is invoked.

While the cursor is in the separate window, click Next Record Next Record button through all existing employee records and verify that the total department salaries figure is correct.

Move your mouse over this icon to see the image

 

9.

Click Exit Form Exit Form button to exit the application, then close the browser window.

 

Controlling Navigation

Back to Topic List

As you were testing the form, depending on how you arranged the items you may have noticed that the cursor did not move in the desired sequence when you tabbed through the items. In addition, you may prefer that when you tab out of the last item in an Employees record, that the cursor should move to the next record, and that when you tab out of the last item in a Departments record, that the cursor should move to the Employees block. In this step, you change the navigation order so that these things are accomplished.

To change navigation order, perform the following steps:

1.

By default, navigation order is determined by the sequence in which objects appear in the Object Navigator.

Open the EMP_DETAILS_CV canvas in the Layout Editor. In the Object Navigator, drag the items in the Employees block to the order in which you want the navigation to occur.

For example, when items are arranged on the canvas as shown below, you want the cursor to move to HIRE_DATE when you tab out of PHONE_NUMBER. To change the sequence, you drag HIRE_DATE in the Object Navigator to the position just after PHONE_NUMBER.

Ignore the items JOB_ID, MANAGER_ID, and DEPARTMENT_ID, because those items are not displayed on any canvas, so navigation to those items does not occur.

Move your mouse over this icon to see the image

 

2.

You can also use block properties to affect navigation order.

In the Property Palette for the Departments block, change the Navigation Style property to Change Data Block.

Move your mouse over this icon to see the image

 

3 .

In the Property Palette for the Employees block, change the Navigation Style property to Change Record.

Move your mouse over this icon to see the image

 

4.

Click Save Save button to save the form, then click Run Form Run Form button to test it (be sure that you have started OC4J -- see Step 1). Test that the navigation order is the same as what you determined by moving the items in the Object Navigator, that the cursor moves into the Employees block when you tab out of the Department_Name item, and that the cursor moves to the next Employees record when you tab out of the last item in the Employees block.

Click Exit Form Exit Form button to exit the application, then close the browser window.

 

5.

You can also affect navigation with item properties.

In preparation for some new functionality that you implement with code in a later step, change the following item properties in the Employees block:

Item Property Value
EMPLOYEE_ID

Previous Navigation Item

LAST_NAME

LAST_NAME

Next Navigation Item

EMPLOYEE_ID

Move your mouse over this icon to see the image

Note: When viewing a queried record, this makes it impossible to navigate to the employee details shown on the EMP_DETAILS_CV canvas. You add the ability to navigate to these items in a later step.

 

6.

Click Save Save button to save the form.

Modifying the Layout

Back to Topic List

When you attempt to insert a new employee record, you discover that you have omitted a required database column, JOB_ID, from the layout. You did include JOB_ID in the data block itself; if you had not, you could use the Data Block Wizard in reentrant mode to add the JOB_ID to the data block. To invoke the Data Block Wizard in reentrant mode, you just right-click the block in the Object Navigator and choose Data Block Wizard from the context menu.

So that it is be possible to use the form to insert employee records, you could use the Layout Wizard in reentrant mode to add the item to the layout for that required database column . However, using the Layout Wizard arranges the items in their original tabular layout within the frame on the MAIN_CV canvas, and this is not desirable for this application. If you want to try this to see the effect, save the form first so that you can revert to the saved copy, then right-click the Employees frame on the MAIN_CV canvas and select Layout Wizard from the context menu to open the Layout Wizard in reentrant mode. You can tell that you are in reentrant mode because there are tabs along the top of the Wizard and there is an Apply button that does not exist unless the Wizard is in reentrant mode. Then on the Data Block tab, move JOB_ID to Displayed Items and click Finish. After you experiment with this, close the form without saving it (select the HR form in the Object Navigator and click Delete), then reopen the saved form to continue.

Instead of using the Layout Wizard in reentrant mode to modify the layout, in this step you simply change the properties of the JOB_ID item. To add JOB_ID to the EMP_DETAILS_CV canvas, perform the following steps:

1.

Select the JOB_ID item in the Object Navigator and display its Property Palette. Change its Canvas Property to EMP_DETAILS_CV, change Number of Items Displayed to 1, and change Prompt Attachment Offset to 5.

Move your mouse over this icon to see the image

 

2.

Open the Layout Editor for the EMP_DETAILS_CV canvas. The JOB_ID item is displayed in the upper left corner. Drag it to the desired position on the canvas.

Move your mouse over this icon to see the image

 

3.

In the Object Navigator, drag the JOB_ID item to the proper position for the desired navigation order.

Move your mouse over this icon to see the image

 

4.

Click Save Save button to save the form, then click Run Form Run Form button to test it (be sure that you have started OC4J -- see Step 1).

Before querying any data, attempt to insert a new Departments record. Enter 99 for the Department_Id and Test for the Department_Name. Click Save Save button. The record should be inserted without error.

Move your mouse over this icon to see the image

 

5.

Click Remove Record Remove Record button to delete the record that you just inserted, then click Save Save button. The record should be deleted without error.

Move your mouse over this icon to see the image

 

6.

Click Execute Query Execute Query button. Navigate to the Employees block and click Insert Record Insert button.

Enter 999 for the Employee_ID, and enter your own first and last name. Click Save Save button.

Because there are some required items that have no values, you receive an error and Forms automatically navigates to the first null required item. Enter IT_PROG for the Job_Id, then fill in any values you desire for the other items. Then click Save Save button. The record should be saved successfully; notice that the Total Salaries amount is recalculated automatically. You may delete the new record if desired.

Move your mouse over this icon to see the image

Click Exit Form Exit Form button to exit the application, then close the browser window.

 

Adding Functionality with Code

Back to Topic List

Now you have a fully functional master-detail form that enables you to query, insert, update, and delete records. The form even performs calculations automatically, and you have not written a single line of code to achieve this functionality. Forms Builder makes it easy to declaratively develop a complete application.

However, you may desire to add functionality by coding PL/SQL program units and triggers in your form. A trigger is a block of code that executes in response to a certain action, such as when the user clicks a button.

In this step, you write code to perform the following:

 Execute a query automatically
 Populate non base table items when querying
 Use lists of values for user input

Create a button to display details

 Adjust navigation programmatically
 Close a window programmatically


Execute a Query Automatically

Back to List

It is common for the user to want data to be displayed in the form when it first opens. To execute a query automatically when the form starts, perform the following steps:

1.

In the Object Navigator, right-click the form-level Triggers node, then select Smart T riggers | WHEN-NEW-FORM-INSTANCE from the context menu.

Move your mouse over this icon to see the image

 

2.

The PL/SQL Editor opens up for the form-level When-New-Form-Instance trigger. Enter the following code:

DO_KEY('EXECUTE_QUERY');

Then click Compile Compile button in the PL/SQL Editor. A message showing that the trigger is compiled displays in the lower right corner of the editor.

Move your mouse over this icon to see the image

Click Save Save button.

Populate Non Base Table Items When Querying

Back to List

One way to look up related data and display it automatically in non base table items is to use a post-query trigger. This technique requires a round trip to the database for each row that is retrieved by the query, so it can result in slower performance. For a large number of records, you may want to consider basing the table on a view or on a set of stored procedures, which may provide better performance.

To automatically populate the non base table items when querying, perform the following steps:

1.

Under the Departments block in the Object Navigator, select the Triggers node, then click Create Create button .

Move your mouse over this icon to see the image

 

2.

Type p in the Find box, then select POST-QUERY from the list. Click OK.

Move your mouse over this icon to see the image

 

3.

The PL/SQL Editor opens up for the block-level Post-Query trigger for the Departments block. The Post-Query trigger fires for every row that is retrieved by a query of the Departments block. Enter the following code:

SELECT CITY || ', ' || STATE_PROVINCE || ', '
|| COUNTRY_ID
INTO :departments.location
FROM locations
WHERE :departments.location_id = location_id;

SELECT FIRST_NAME || ' ' || LAST_NAME
INTO :departments.manager
FROM employees
WHERE :departments.manager_id = employee_id;

Then click Compile Compile button in the PL/SQL Editor. A message showing that the trigger is compiled displays in the lower right corner of the editor.

Move your mouse over this icon to see the image

Click Save Save button.

Use Lists of Values for User Input

Back to List

A List of Values (LOV) can be attached to any input item to help users to enter accurate data. There is little coding involved in defining an LOV other than typing in the SELECT statement to use.

You could use LOVs for Location and Manager, then populate those non base table items along with their associated base table items, Location_Id and Manager_Id. However, in this example, you use an LOV to enable the user to select a valid Job_Id for an employee.

To define an LOV for the Job_Id item, perform the following steps:

1.

Click the LOVs node in the Object Navigator, then click Create Create button .

Move your mouse over this icon to see the image

 

2.

Leave the Use the LOV W izard option selected and click OK.

Move your mouse over this icon to see the image

 

3.

On the first page of the LOV Wizard, click Next to base the LOV on a new record group based on a query.

Move your mouse over this icon to see the image

 

4.

For the S QL Query Statement, enter the following:

SELECT job_title TITLE, job_id ID
FROM jobs
ORDER BY job_title

Do not end the statement with a semicolon.

If you want to ensure that the statement is syntactically correct, click Chec k Syntax. You should receive a message affirming the correctness of the statement; click OK to dismiss this message, then in the LOV Wizard, click Next.

Move your mouse over this icon to see the image

 

5.

In the LOV you want to display the Job_Title to the user, because this contains information that is meaningful to the user. You decide to also display the Job_Id. Click the double right arrow button Double Right Arrow button to move both record group columns to the LOV, then click Next.

Move your mouse over this icon to see the image

 

6.

What you are trying to accomplish is to populate the Job_Id item in the Employees block with the ID selected from the query.

Click the Return value column for the ID column, then click Look up r eturn item.... In the Items and Parameters list, select :EMPLOYEES.JOB_ID, then click OK.

Check A utomatically size columns, then click Next.

Move your mouse over this icon to see the image

 

7.

Enter Jobs for the LOV T itle, 250 for the W idth, and 300 for the H eight. Click Next.

Move your mouse over this icon to see the image

 

8.

Click Next to accept the defaults of retrieving 20 rows at a time and refreshing the record group before displaying the LOV.

Move your mouse over this icon to see the image

 

9.

You want the LOV to be available from the Job_ID item in the Employees block. Click the right arrow button Right Arrow button to move EMPLOYEES.JOB_ID from R eturn Items to A ssigned Items, then click Next, and on the final screen, click Finish.

Move your mouse over this icon to see the image

Click Save Save button.

 

Create a Button to Display Details

Back to List

Because there is now no easy way to navigate to items on the EMP_DETAILS_CV canvas, you create a button enabling users to do so.

In most cases, you would create buttons in the Control block. However, the button needs to know which record in the Employees block to display details for, and the easiest way to do this (there are other ways) is to place the button in the Employees block.

For most buttons, you set the Mouse Navigate property to No. In this case, however, you want to select an employee record and display its details with one click, so you leave Mouse Navigate set to its default value, which is Yes.

To create a button to display employee details, perform the following steps:

1.

Open the Layout Editor for the MAIN_CV canvas. Enlarge the Employees frame to the right by selecting it and dragging its resize handle. Move the scrollbar to the right side of the frame.

Move your mouse over this icon to see the image

 

2.

Select EMPLOYEES from the block list in the Layout Editor. Click the Button tool in the Layout Editor, then click in the area between Last_Name and the scrollbar.

Move your mouse over this icon to see the image

 

3.

Open the Property Palette for the button and set the following properties:

Property Value
Name DETAILS_BTN
Label Details
Width 50
Height 14

Move your mouse over this icon to see the image

 

4.

In the Object Navigator, drag the button to a position just after Last_Name.

Move your mouse over this icon to see the image

 

5.

Right-click the DETAILS_BTN button, and from the context menu choose Smart T riggers | WHEN-BUTTON-PRESSED.

Move your mouse over this icon to see the image

 

6.

In the PL/SQL Editor, enter the following code:

GO_ITEM('job_id');

Click Compile Compile button.

Move your mouse over this icon to see the image

Click Save Save button.

 

Adjust Navigation Programmatically

Back to List

You previously set the Navigation Style property of the Employees block to Change Record, which means that when the user tabs out of the last item in the block, the cursor goes to the first item in the next record. However, because Last_Name is not the last item in the block, tabbing out of it simply navigates to the Employee_Id of the same record.

To adjust navigation so that tabbing from the employee's last name navigates to the next record, perform the following steps:

1.

In the Object Navigator, expand the Last_Name node under the Employees block, and select the Triggers node. Click Create Create button.

Move your mouse over this icon to see the image

 

2.

In the Triggers window, scroll down and select KEY-NEXT-ITEM, then click OK.

Move your mouse over this icon to see the image

 

3.

In the PL/SQL Editor, enter the following code, then click Compile Compile button:

do_key('next_record');

Move your mouse over this icon to see the image

Click Save Save button.

 

Close a Window Programmatically

Back to List

When the user clicks the X on a Forms window to close it, by default nothing happens. If you want the user to be able to close the window, you must code the action to take.

To close the EMP_DETAILS_WIN window when the user clicks the X, perform the following steps:

1.

Select the Triggers node under the HR form, click Create Create button , and choose WHEN-WINDOW-CLOSED from the trigger list, then click OK.

Move your mouse over this icon to see the image

 

2.

In the PL/SQL Editor, enter the following code, then click Compile:

IF :SYSTEM.CURSOR_ITEM IN ('EMPLOYEES.M_EMPLOYEE_ID',
   'EMPLOYEES.M_FIRST_NAME',
   'EMPLOYEES.M_LAST_NAME',
   'EMPLOYEES.JOB_ID',
   'EMPLOYEES.EMAIL',
   'EMPLOYEES.PHONE_NUMBER',
   'EMPLOYEES.HIRE_DATE',
   'EMPLOYEES.SALARY',
   'EMPLOYEES.COMMISSION_PCT') THEN
   GO_ITEM('employee_id');
   HIDE_WINDOW('emp_details_win');
END IF;

Move your mouse over this icon to see the image

Click Save Save button.

 

3.

Click Run Form Run Form button to test it (be sure that you have started OC4J -- see Step 1).

Demonstrate that:

 The form displays the data (executes the query) automatically.
 The Location and Manager items are populated automatically.
 When navigating in the Employees block on the Main canvas, tabbing out of Last_Name navigates to the next employee record.
 Employee details display when you click Details in the Employees block.
 In the Job_Id item you can display and select from an LOV when you press Ctrl + L, and the Job_Id item is populated with the job that you select.
 You can close the detail window.

Click Exit Form Exit Form button to exit the application, then close the browser window.

 

Summary

Back to Topic List

This exercise showed you how easy it is to create a master-detail form using the Data Block and Layout Wizards in Forms Builder. You also manually created a control block on which you placed an item to automatically calculate the sum of salaries for a department.

You enhanced a data block with non base table items to display data from related tables, and you moved some items to a new canvas to display in a separate window. You adjusted navigation and added another base table item to the layout. This fully functional master-detail form was achieved using only the visual and declarative IDE of Forms Builder.

You then added code to execute a query automatically, populate the non base table items, define a list of values to aid user input, create a button to display employee details, further refine navigation programmatically, and programmatically close the separate window.

This brief overview only scratches the surface of what you can accomplish with Forms Builder. For more information, see Related Topics below.

Related topics

Oracle Education Online Library eClass: Oracle Forms Developer 10g: Move to the Web

Oracle Education inClass course: Oracle Forms Developer 10g: Build Internet Applications (currently in development)

OTN: Forms Products page


 Place the cursor on this icon to hide all screenshots