Make your own free website on Tripod.com

Oracle Developer
Back Home Up Next

 

  Theory

Projects

Articles

 Written Books

Report

Technical Report

Survey Reports

Workshop

Presentations

Tutorials

Algorithms

Contact Me

Yourself

 

 

Oracle Developer/2000

A Tutorial on Oracle Forms and Reports

Covering Developer/2000 versions 1.3 through 1.5

Rich Holowczak

 

Computers Information Systems Department
Baruch College, School of Business, CUNY
and
Center for Information Management,
Integration and Connectivity (CIMIC)
Rutgers University
richard_holowczak@baruch.cuny.edu

 

Developer/2000 v1.5 Fri Feb 20 12:35:50 EST 1998

Contents

Introduction
Prerequisites
Developer/2000 Tools
Starting Developer/2000 tools under UNIX
Starting Developer/2000 tools under Windows 95/NT
The Oracle Forms Designer Main Screen
Oracle Forms Object Navigator
Connecting to the Database
Creating a Form with a Single Block
Creating a New Block
1. Choosing a Base Table
2. Selecting Columns and Display Types for the Base Table
3. Choosing a Block Layout
4. Finishing up the New Block
The Layout Editor
Displaying Properties
Saving, Generating and Running Forms
Saving a Form
Compiling/Generating a Form
Running a Form
Creating a Master-Detail Form
The Master-Detail Relationship
Steps to Create a Master-Detail Form
1. Create the Master Block
2. Create the Detail Block
Relation Properties of a Master-Detail Form
Program Units in a Master-Detail Form
Extending Master-Detail Forms
List of Values (LOVs) and Non-Base Table Fields
Creating a List of Values
1. Create a new LOV Object
2. Specify the LOV Query Text
3. Specify the Column Mapping Properties
4. Attach the LOV to the item
5. Test the LOV
Non-Base Table fields
1. Adding a New Item to an Existing Block
2. Creating a Trigger
3. Create a new List of Values
Oracle Forms Program Units and Stored Program Units
Creating Procedures in Oracle Forms
1. Add a new item to the EMPLOYEE Block
2. Create a Procedure to Count Other Department Members
3. Create a Trigger to Call the Procedure
4. Save, Compile and Run the Form
Creating Stored Program Units (Procedures) in SQL*Plus
Oracle Reports Basics
Starting Oracle Reports under UNIX
Starting Developer/2000 tools under Windows 95/NT
The Oracle Reports Object Navigator
Creating Reports in Oracle Reports
Creating a Single-Table Report
1. Specify the Data Model
2. Specify the Layout
3. Saving, Generating and Running the Report
Creating a Master-Detail Report
1. Specify the Master-Detail Data Model
2. Specify the Master-Detail Layout
3. Saving, Generating and Running the Master-Detail Report
Creating Menus in Oracle Forms
The Oracle Forms Menu Hierarchy
Creating a Custom Menu

Introduction

This document contains a brief introduction to Oracle Forms 4.5. Oracle Forms 4.5 is one of the components of the Designer/2000 suite of development tools. This tutorial covers versions 1.3 to 1.5 of Developer/2000. The basic Oracle Forms functionality is the same across these versions as well as across operating systems such as UNIX and Windows 95/NT.

Prerequisites

Before following this tutorial, use the SQL*Plus tool to create two tables named EMPLOYEE and DEPARTMENT with the following attributes. The VARCHAR data type is the same as VARCHAR2. Note: This schema originally appears in the book: Ramez Elmasri and Shamkant B. Navathe, Fundamentals of Database Systems. Second Edition. Addison-Wesley Pub Co, 1994.

 

EMPLOYEE Table
Attribute Data Type
FNAME VARCHAR(8)
MINIT VARCHAR(2)
LNAME VARCHAR(8)
SSN NUMBER(12)
BDATE DATE
ADDRESS VARCHAR(27)
SEX VARCHAR(1)
SALARY NUMBER(7)
SUPERSSN NUMBER(9)
DNO NUMBER(1)
DEPARTMENT Table
Attribute Data Type
DNAME VARCHAR(15)
DNUMBER NUMBER(2)
MGRSSN NUMBER(12)
MGRSTARTDATE DATE



 

The following CREATE TABLE statements can be used to create the above tables in SQL*Plus:

CREATE TABLE EMPLOYEE (FNAME VARCHAR(8), MINIT VARCHAR(2), LNAME VARCHAR(8), SSN NUMBER(12) NOT NULL, BDATE DATE, ADDRESS VARCHAR(27), SEX VARCHAR(1), SALARY NUMBER(7) NOT NULL, SUPERSSN NUMBER(9), DNO NUMBER(1) NOT NULL) ; CREATE TABLE DEPARTMENT (DNAME VARCHAR(15), DNUMBER NUMBER(2) NOT NULL, MGRSSN NUMBER(12), MGRSTARTDATE DATE) ;

Developer/2000 Tools

Oracle Developer/2000 contains 3 main components:

  1. Oracle Forms - For designing data entry forms.
  2. Oracle Reports - For designing reports.
  3. Oracle Graphics - For designing graphics such as charts that can be added to forms or reports.

Two additional components include:

  1. PECS - The Oracle Performance Evaluation/Collection System - For profiling the execution and performance of applications developed with Oracle tools.
  2. Discoverer/2000 - A data browsing tool that can be customized for end-users.

In the following sections, the procedures to run Oracle Forms under UNIX and under MS Windows 95/NT will be given.

Starting Developer/2000 tools under UNIX

Each of the Oracle tools in the Developer/2000 tool set for UNIX has 3 or more modules:
Designer - Designated by des - Used to design forms, reports or graphics.
Generator - Designated by gen - Used to generate an executable form, report or graphics module.
Runtime - Designated by run - Used to run a generated form, report or graphics module.

Each tool is designated by a single letter:
Oracle Forms - f
Oracle Reports - r
Oracle Graphics - g

Finally, under UNIX, either the Motif or Open Look X-Windows interfaces can be installed. These are designed by the letters:
Motif Interface - m
Open Look Interface - o

Most current installations use the Motif version of the tools.

These letters are then strung together along with the version number to form the executable file name.

For example, version 4.5 of Oracle Forms Designer for the Motif interface has executable file name: f45desm

Version 2.5 of Oracle Reports Designer for the Open Look interface has the executable file name: r25deso

Running the Oracle Developer/2000 tools is fairly consistent across UNIX flavors. Here, the directions for running the tools under Sun Solaris are given.

The command line to run the Motif version of Oracle Forms 4.5 Designer under Sun Solaris is:

f45desm To run Oracle Forms, type the name of the executable at the UNIX prompt. If you would like to run Oracle Forms and retain control over your UNIX login session, place an ampersand (&) character after the name of the executable as follows: unix% f45desm &

unix% is the UNIX command prompt. This may vary according to your installation of UNIX.

Starting Developer/2000 tools under Windows 95/NT

A typical installation of Developer/2000 creates several folders under the Start Programs menu. The main folders for the Developer/2000 programs are found under the menu item: Developer/2000 for Win95 and include the following items:

To run Oracle Forms under Windows 95/NT, click on the Start -> Programs -> Developer/2000 for Win95 -> Forms Designer menu item.

The Oracle Forms Designer Main Screen

Once the appropriate command line has been entered (for UNIX) or menu item has been clicked on(for Windows 95/NT), the Oracle Forms Designer main screen will appear. At this point, differences between the UNIX version and Windows 95/NT version are minimal and only the Windows 95/NT screen shots will be included here.

Oracle Forms Object Navigator

After starting up Oracle Forms, the main screen will be presented.

This screen is called the Object Navigator and displays all of the form elements, menu items, user defined code libraries, built in procedures and functions, and database objects (tables, views). The display is in the form of a collapsible tree. In general, clicking on a + will expand the current item to show its details. Clicking on a - will collapse the current list details.

The menu bar across the top provides access to almost all of the features of Oracle Forms.
The File menu has options to create, open, save, compile, run and print forms. It also has an Exit option to exit Oracle Forms.
The Edit menu has options to cut, copy and paste objects, to invoke an editor and to undo an operation.
The Tools menu has options to display an object's properties, switch between several different screens including the Layout editor, Menu editor, PL/SQL editor and Object Navigator. There is also an option to create a new block and to change the default program options.
The Windows menu displays menu items for each of the windows you have open. By default, only one item - the Object Navigator - is displayed.
The Navigator menu has options to create new objects and to change the display by opening, closing or sorting the various items.

By default, a button bar is provided on the left hand side of the Object Navigator window. Each button duplicates some functionality on the menus. For example, clicking on the top icon (an opened folder) is the same as pulling down the File menu and choosing the open option. To see what a button might do if pressed, place the mouse cursor over the button and a short message will appear describing the button's function.

Connecting to the Database

The first step is establish a connection to an Oracle database. This is accomplished by pulling down the File menu and selecting the Connect menu item.

Fill in your Oracle Username and Oracle Password (press the tab key to move between the fields).

For the Database field, type in the service name for your Oracle database. For example, if you are using Personal Oracle Lite, the Database: would be ODBC:POLITE. Contact your system administrator to learn the service name for your Oracle database and provide that information in the Database: field.

Click on the Connect button to complete the connection (Under some versions of UNIX, the button is labeled OK). If the user name or password is mistyped or incorrect, the dialog box will be re-displayed. Be sure to provide your Oracle username and password (not your UNIX host password).

At the bottom of the Object Navigator screen, you will see a status bar with an entry: Con indicating Oracle Forms is now connected to a database.

Creating and Running a Form with a Single Block

In this section, we cover the basic steps for creating a basic data entry and query form for a single database table.

In general, a Block on a form corresponds to a table in the database. This is called the Base Table for the block. For each table displayed on the form, a new block will be created. For this example, only one block will be created for the EMPLOYEE table.

The main steps to create a new block are:

  1. Choosing a Base Table
  2. Selecting columns and display types for the base table
  3. Choosing layout options for the fields

Creating a New Block

To create a new block, pull down the Tools menu and select the New Block menu item. The following dialog box with 4 tabs will appear:

1. Choosing a Base Table

To associate a database table with the block, click on the Select... button to the right of the Base Table field. The following dialog box will pop up.

Make sure the Current User and Tables buttons are selected and click on the OK button. A list of your tables will appear.

Highlight the name of the database table and click on the OK button. The Block Options dialog box will reappear.

Note that the Block Name field changes to match the name of the table. If it does not, press the TAB key to move to the Block Name field or click in the Block Name field with the mouse.

2. Selecting Columns and Display Types for the Base Table

Click on the Items tab on the New block options dialog box. Click on the Select Columns... button to retrieve all of the columns in the table.

Each column has either a + or a - next to it. A + indicates the column of the table will appear as a field on the block. To toggle a column, double click on the column name under the Select Columns button.

For each column, select an appropriate column label and width for the form field. For now, leave each field as Type: Text Item.

3. Choosing a Block Layout

The default layout of the fields can be altered in a number of ways. Clicking on the Layout tab provides some of these options.

 
Style: Tabular displays consecutive records as rows as in a table. Form spreads out the fields like a paper form might.
Orientation: Dictates how multiple records should be displayed. Vertical arrangement places consecutive records in a downward direction. Typically, the fields are next to one another in columns, similar to a spreadsheet. Horizontal arrangement places consecutive records off to the right hand side.

To display multiple records in a tabular form, use a combination of Tabular style and Vertical orientation. To display a single record at a time (as in a data entry form), use the Form style with either vertical or horizontal orientation.

Records: Indicates the number of records to display at one time. For tabular style, more than one record can be displayed.
Spacing: The distance between consecutive records. 0 spacing places consecutive records next to each other.

It is also possible to include a Button Bar and/or a Scrollbar by selecting those options.

Integrity Constraints can be added by selecting that option. An example of constraints will be shown in the section on Master/Detail forms.

4. Finishing up the New Block

At this point, all of the options for the new block have been specified. Click on the OK button.

The Layout Editor

To view the actual form and its blocks and fields, pull down the Tools menu and select the Layout Editor menu item.

In the Layout Editor, fields and labels can be moved around by clicking and dragging. Other text, lines and boxes, etc. can be added using the tool palette on the left side of the window.

Some common operations on the Layout Editor include:
Move a field or a field label by clicking and dragging the item with the left mouse button.
Change the text of a label by choosing the text tool button from the tool bar and then clicking on a label. To stop editing the label, click anywhere outside of the label text.
Add text to the block by choosing the text tool button from the tool bar and then clicking on a open area. Type the new text. To change the font, highlight the text, pull down the Format menu and click on the Font menu item.
To change the text color of a label, use the pointer tool to highlight a label and then click on the Text Color palette button to choose the color.
Change the width of a field by clicking on the field. Then drag one of the handles (small black boxes around the field) to re-size the field.

Displaying Properties

While in the Layout Editor, double clicking on an object will bring up that object's properties. Optionally, clicking on an object with the right mouse button will bring up a small menu which has the Properties menu item on it. The following figure shows the properties for the FNAME item.

Each type of object has a slightly different set of properties. For example, double clicking on the EMPLOYEE block in the Object Navigator will display that block's properties.

Some properties of interest for a block include:
WHERE Clause - specify a WHERE clause to filter the selection of rows from the base table.
ORDER BY Clause - specify an ORDER BY clause to alter the displayed order of the records.
Delete Allowed, Insert Allowed, Query Allowed and Update Allowed - toggle these properties to allow or prevent the action. For example, setting Delete Allowed to FALSE prevents users from deleting records from the corresponding base table using this form.
There are numerous other properties that affect the fonts, colors, size and position of objects in the block.

Saving, Compiling and Running Forms

Forms can be saved in files in a directory of your choosing. The "source code" for a form is stored in a file with a .fmb extension. Compiled forms have a .fmx extension.

Under UNIX, each user has a home directory where their personal files are kept. This is typically something like /home/username. Note that in UNIX, the directory slashes are forward slashes. The source code and compiled versions of forms can be saved in a user's home directory or in a subdirectory.

Under MS Windows 95/NT, forms can be saved in a folder (subdirectory) on the local hard disk or on a file server.

In either case, UNIX or Windows, it is desirable to maintain separate subdirectories (folders) for forms associated with different projects.

Saving a Form

To save a form, pull down the File menu and select the Save menu item.

The save dialog box for UNIX systems looks like the following:

Fill in a file name in the Save: field. Be sure it ends with .fmb

Click on the OK button to save the file.

To enhance portability of forms across different operating systems, use file names that begin with a letter, contain no spaces or punctuation (except for the underscore character), have all lower case letters and are relatively short.

Under MS Windows 95/NT, this dialog box looks like:

Fill in a file name in the File Name: field. Be sure it ends with .fmb

Click on the Save button to save the file.

To enhance portability of forms across different operating systems, use file names that begin with a letter, contain no spaces or punctuation (except for the underscore character), have all lower case letters and are relatively short.

If you are working on a form and wish to save it under a different file name, follow the same steps as above, only select the Save As menu item in place of the Save menu item.

Compiling/Generating a Form

Before a form can be executed (run), it must be Compiled and Generated. Compiling runs a PL/SQL compiler on all of the procedures and triggers in the form. Generating create the .fmx file that is then used to run the form.

To compile a form, first make sure the form is saved. Pull down the File menu and select the Compile menu item.

The progress of the compilation will be shown in a window:

If a form has errors (e.g., in the PL/SQL code, or if the database schema changes) they will be displayed in a window as they are encountered. A record of the compilation process, including error messages, is kept in a file with a .err extension. For example, if the form is named employee then the record of the compilation will be stored in employee.err

To Generate the .fmx file for a form, pull down the File menu, click on the Administration menu item and then choose Generate from the fly-out menu. If the forms generation is successful, the phrase Module generated successfully will appear on the status bar at the bottom of the screen.

Alternatively, Oracle Forms defaults to automatically generating the form each time it is executed (run) from the forms designer as will be demonstrated in the next step. For more details on the default behavior, pull down the Tools menu and check the Options menu item.

Running a Form

After a form has been saved and compiled, it can be executed. Pull down the File menu and select the Run menu item. The form will display in a new window. The Oracle Runform program is executed by passing in the name of the form (employee.fmx in this case) as a parameter.

 

Use the various menus and items to query the form, enter new data and save new and changed data.

Querying a form in Oracle Forms is a two step process. Clicking on the Query button or choosing Enter Query from the Query menu places the form in Enter Query mode. In enter query mode, the form is cleared and the user can navigate in the various fields. Example values can be supplied as criteria for the query.

Clicking on the Query button a second time executes the current query using any supplied data as criteria in a WHERE clause. If no criteria are supplied, then all records in the table will be displayed.

Most of the commands on the menus and on the button bar can also be activated using special key combinations or function keys. For example, under MS Windows 95/NT, pressing the F7 function key places the form in Enter Query mode. Pressing the F8 function key executes a query. To see a list of keys and their functions, pull down the Help menu and choose the Keys option.

Once data has been queried into the form, the user can press the up and down arrow keys to scroll between records. To change data in a form, the user can TAB to the field of interest, and type over the existing data.

To enter new data into the form, the user can scroll to the last record and then down once more to move to a blank record. Data can then be typed into the fields and the TAB key can be pressed to move between fields on the form.

To save both changed and new records on a form, pull down the Action menu and choose the Save menu item. Any records that have been changed are collected and submitted to the database as SQL UPDATE statements. Any new records are collected and submitted to the database as INSERT statements. These statements are submitted as a single logical unit of work. Thus if a failure occurs during any of the statements, the entire transaction will be rolled back.

 

To exit from a running form, pull down the Action menu and select the Exit menu item.

 

Creating a Master-Detail Form

In this section, the basic steps for creating a Master/Detail form are introduced. A Master/Detail form is a form that has two blocks arranged in a master-detail relationship.

The Master-Detail Relationship

The Master-Detail relationship is a common relationship between entities in a business. In an Entity-Relationship diagram, these are shown as "One to Many" relationships. In a physical database design, a single Master record references one or more detail records in another table. A record in the detail table will relate to exactly one master record in the master table. Another name for this relationship is called parent-child. Examples of this relationship include:
A Customer Order with many OrderItems.
A Department with many Employees.
An Employee with many Dependents.
A Company with many Branch Offices.
A Recipe with many RecipeSteps.
An Inventory location with many InventoryItems.

Oracle Forms implements the master-detail relationship using two blocks. The first block corresponds to the master table and the second block corresponds to the detail table. There are two major functions in a Master-Detail form:
Oracle Forms coordinates values between the two blocks through a series of form and block level triggers.
Oracle Forms guarantees that the detail block will display only records that are associated with the current record in the master block.

Note that a Master-Detail form is simply one way of viewing the data in two related tables. Forms do not affect the schema in terms of creating or dropping database level referential integrity constraints.

Steps to Create a Master-Detail Form

In this section, a set of step by step instructions for creating a Master-detail form are given. The form will allow a user to query a given department in the company and then will display all of the employees in that company.

The schema used is the same one suggested in the Prerequisites section at the beginning of this tutorial. Notice that the DNO column in the EMPLOYEE table gets its values from the DNUMBER column in the DEPARTMENT table. In other words, to join the two tables in a query, one might specify a WHERE clause such that: EMPLOYEE.DNO = DEPARTMENT.DNUMBER.

1. Create the Master Block

Using the same steps given in the prior section on Creating a Form with a Single Block, create a new block named DEPARTMENT that contains all of the columns in the DEPARTMENT table. Briefly:

 

  1. Pull down the Tools menu and choose New Block.
  2. On the General tab, click on the Select button to select the DEPARTMENT table. Be sure the Block Name also changes to DEPARTMENT.
  3. Click on the Items tab and click on the Select Columns button to include all of the columns.
  4. Click on the Layout tab and choose style as Tabular and Orientation as Vertical. Allow only 1 record to be displayed with spacing of 0.
  5. When done, click on the OK button to create the block.
  6. Save the form as deptemp.fmb and then compile and run it to make sure it is working properly.

2. Create the Detail Block

Now that we have the master block DEPARTMENT created, we can now create the detail block EMPLOYEE and associate it with the master block. Perform the following steps:

  1. Pull down the Tools menu and choose New Block.
  2. On the General tab, click on the Select button to select the EMPLOYEE table. Be sure the Block Name also changes to EMPLOYEE. Notice also that this block has a Sequence ID of 2 meaning it is the second block in the master-detail form.
  3. Click on the Items tab and click on the Select Columns button to include all of the columns. Then go through the list of columns and de-select (by double-clicking) the MINIT, SSN, ADDRESS, and SEX columns.
  4. Click on the Layout tab and choose style as Tabular and Orientation as Vertical. Allow 5 records to be displayed with spacing of 0.
    Also click on the options for Integrity Constraints and for a Scrollbar.
  5. Click on the Master/Detail tab and type in the name of the Master block (DEPARTMENT) in the appropriate field. Note that in some versions of Oracle Forms, there is a bug that prevents the designer from displaying potential master blocks by clicking on the Select button.

    Type in the Join Condition in the space provided. In this case, the join condition is:

    
           EMPLOYEE.DNO = DEPARTMENT.DNUMBER
    
           

  6. When done, click on the OK button to create the block.
  7. Save the form (it should already have the name deptemp.fmb) and then compile and run it.

The following figure shows the master-detail form running:

Notice that by scrolling the master block DEPARTMENT to a new department number (using the up and down arrow keys), the employees for that department are automatically queried and displayed.

Relation Properties of a Master-Detail Form

There are a number of properties in a master-detail form that can be changed to suit particular behavior of the form. In the figure below, the Object Navigator has several new objects on it including Relations.

To view the properties for the DEPARTMENT_EMPLOYEE relation, open up the DEPARTMENT block and then open the Relations block by clicking on the + symbols. Then click on the DEPARTMENT_EMPLOYEE relation with the right mouse button and select Properties.

There are several interesting properties in the relations property sheet:

 
Detail Block - The name of the detail block specified when the detail block was created.
Join Condition - This is the join condition in effect for queries to the database. This was specified when the detail block was created.
Master delete - Used to specify how the deletion of a record in the master block affects records in the detail block. It supports the following settings:
Non-isolated: Prevents the deletion of a master record if associated detail records exist in the database.
Isolated: Deleting the master record will not affect the associated detail records in the database.
Cascading: Deletes the master record and automatically deletes any associated detail records.
Coordination - Deferred: Indicates when detail records should be queried when a master record is queried.
true: Form does not query the detail record until the user navigates to the detail block.
false: Detail records are fetched immediately when a user queries the master record.
Coordination - Auto-query: Applied to deferred queries only
true: the query is automatically executed.
false: the query is executed manually by the user.
Prevent Masterless operation - Specifies whether users are allowed to query or insert records in a detail block when no master record is in place.
true: Users may not query or insert when no master record is in place.
false: Users may query or insert when no master record is in place.

Program Units in a Master-Detail Form

When a Master-detail form is created, three basic Program Units (PL/SQL Procedures) are created by default. These can be seen listed in the Object Navigator:

PL/SQL is Oracle's procedural language extensions to SQL. To view the PL/SQL code for a Program Unit, click on the name of the program unit with the right mouse button and then choose PL/SQL Editor from the pop-up menu. The code for the CHECK_PACKAGE_FAILURE procedure is show here:

The CHECK_PACKAGE_FAILURE procedure checks to see if a prior form command or SQL statement was successful or if it failed.

Below is the PL/SQL code for the QUERY_MASTER_DETAILS procedure. The text after the -- (two minus signs) are comments.

 

PROCEDURE Query_Master_Details(rel_id Relation,detail CHAR) IS oldmsg CHAR(2); -- Old Message Level Setting reldef CHAR(5); -- Relation Deferred Setting BEGIN -- -- Initialize Local Variable(s) -- reldef := Get_Relation_Property(rel_id, DEFERRED_COORDINATION); oldmsg := :System.Message_Level; -- -- If NOT Deferred, Goto detail and execute the query. -- IF reldef = 'FALSE' THEN Go_Block(detail); Check_Package_Failure; :System.Message_Level := '10'; Execute_Query; :System.Message_Level := oldmsg; ELSE -- -- Relation is deferred, mark the detail block as un-coordinated -- Set_Block_Property(detail, COORDINATION_STATUS, NON_COORDINATED); END IF; EXCEPTION WHEN Form_Trigger_Failure THEN :System.Message_Level := oldmsg; RAISE; END Query_Master_Details;

The QUERY_MASTER_DETAILS procedure is executed whenever the user navigates to the detail block in a master detail form. If the Deferred Coordination property is set to false, then the detail block is automatically queried.

Extending Master-Detail Forms

Master-Detail forms can be extended to include additional levels of details. For example, consider a Customer listing with detail on Orders a customer has placed. For each Order, there are then many Items. An extension to the above example would be to include the DEPENDENTS of the employees as a details of the EMPLOYEES block. The steps outlined above can be repeated allowing several levels of detail to be added. Each additional level of detail will add a new block that is related to the level above it.

 

List of Values (LOVs) and Non-Base Table Fields

It is cumbersome for a user to have to remember various codes and numbers while entering data into a data entry form. For example, in our Employee form, it might be difficult to have all of the department numbers memorized when inserting new employee data.

One solution to this problem is to make a list of appropriate values available when the user navigates to Department Number field (DNO). In Oracle Forms, these are called List of Values (LOVs). The next section will illustrate creating LOVs for fields. These examples are based on the single block Employee form created at the beginning of this tutorial.

A List of Values is based on a Record Group. In Oracle Forms, a record group is a query that returns some collection of records. Record groups can be used to populate blocks or LOVs and they can be used in procedures. In the example below, a record group and LOV for the DNO (department number) field in the Employee table will be created.

Creating a List of Values

Creating a List of Values requires 3 basic steps.

1. Create a new LOV Object

To create an LOV, click on the LOVs node in the Object Navigator. Then pull down the Navigator menu and choose the Create menu item. The New LOV dialog box will appear.

2. Specify the LOV Query Text

In most cases, you will not have a record group created previously. However, one can be created on the fly at this point by specifying a query. In this example, enter the LOV query as follows: SELECT DNUMBER FROM DEPARTMENT

Then click on the OK button to create both the record group and the LOV.

3. Specify the Column Mapping Properties

In the Object Navigator, click on the new LOV that was created (Typically given a name like LOV4). Then click with the right mouse button and choose the Properties menu item.

Scroll down to the Column Mapping Property and and double click on it to display the LOV Column Mapping dialog. In this example, the column DNUMBER from the DEPARTMENT table is being returned by the record group. The returned item should be sent to the DNO item on the EMPLOYEE field. Type in the DNO item and click on the OK button.

4. Attach the LOV to the item

In the Object Navigator, select the EMPLOYEE block and the DNO item. Call up the properties for the DNO item. In the property window, set the LOV property by the entering the name of the LOV that you want to attach to the item. In this example, the LOV would be LOV4.

5. Test the LOV

Save, Compile and Run the form. When entering new data, navigate to the DNO (Department number) field. Notice at the bottom of the form, a message appears: List of Values indicating a list of values is available for this field.

Under UNIX, the key to display the list of values is Control-l.

Under MS Windows 95/NT the key to display the list of values is F9.

To see the appropriate keys to press, pull down the Help menu and choose the Keys item.

Non-Base Table fields

In the previous example of adding a List of Values, the supplied list of appropriate values can be useful in cases where the values themselves are self-explanatory. However, in the prior example, the department numbers may not have meaning to the user.

It would be more helpful to include the name of the department both in the pop-up list of values and on the form. However, the department name is not part of the EMPLOYEE table or block. Thus we need a way to display this information on the EMPLOYEE block in a non base table field.

Adding this item and making it functional will take several steps:

  1. First the new item will have to be added to the EMPLOYEE block. We'll call this new item DEPTNAME.
  2. Next, the DEPTNAME field should be populated with the department name each time the value of the DNO field changes. A trigger will be created to handle this functionality.
  3. Finally, the list of values will be changed to include the Department name in the LOV.

1. Adding a New Item to an Existing Block

Switch to the Layout Editor by clicking on the Tools menu and selecting the Layout Editor menu item.
Click on the Text Item tool and create a new field on the EMPLOYEE block by clicking and dragging the mouse next to the DNO item.

Display the properties for the new Text Item and change its name to DEPTNAME. Change the following properties for DEPTNAME:

Enabled: True
Navigable: False
Base Table Item: False
Query Only: False
Primary Key: False
Insert Allowed: False
Query Allowed: False
Update Allowed: False


2. Creating a Trigger

Next, we create a trigger to populate the DEPTNAME field whenever the value of DNO changes. Triggers in Oracle Forms have four general forms:
PRE- Trigger fires before an event is executed.
POST- Trigger fires after an event is executed.
WHEN- Trigger fires in place of the execution of an event.
KEY- Trigger fires when a particular key is pressed.

In this example, we would like to populate the DEPTNAME field with the appropriate department name just after the DNO field changes on the EMPLOYEE block. Thus we will use a POST-CHANGE trigger on the :EMPLOYEE.DNO field.
Switch to the Object Navigator view by clicking on the Tools menu and choosing the Object Navigator menu item.
Open up the EMPLOYEE block and the DNO item.
Click on the Triggers property and create a new trigger by pulling down the Navigator menu and choosing the Create menu item.
A list of possible trigger names appears. Choose the POST-CHANGE trigger and click on the OK button.

When the PL/SQL editor appears, type the following PL/SQL code: -- Populate the non-base table field :EMPLOYEE.DEPTNAME -- using a value from the DEPARTMENT table. BEGIN SELECT DNAME INTO :EMPLOYEE.DEPTNAME FROM DEPARTMENT WHERE DEPARTMENT.DNUMBER = :EMPLOYEE.DNO; END; Items on a block are preceded by a full colon to differentiate them from columns in a table.
Compile the trigger by clicking on the Compile button.

Close the PL/SQL editor by clicking on the Close button. Then save, compile and run the form to test the functionality. Notice that when the EMPLOYEE block is queried, the associated department name now appears in the new DEPTNAME field.

3. Create a new List of Values

Finally, a new list of values should be created to display both the department number and name when the user calls up the LOV.
From the Object Navigator, click on the LOVs for the EMPLOYEE form. Pull down the Navigator menu and choose the Create menu item.
Type the following SQL statement into the Query Text field:

        SELECT DEPARTMENT.DNUMBER, DEPARTMENT.DNAME

        INTO   :EMPLOYEE.DNO, :EMPLOYEE.DEPTNAME

        FROM   DEPARTMENT;

        

Then click on the OK button.

Note that by providing the INTO portion of the SQL statement above, we can skip the step of associating the values returned by the record group with the items in the block.

Associate the new LOV with the DNO item by displaying the DNO item's properties and assigning the new LOV to the LOV property.
Finally, Save, Compile and Run the form. Note that displaying the LOV for the DNO item now causes both the department number and the name to appear.

 

Oracle Forms Program Units and Stored Program Units

Oracle Forms provides a mechanism to store procedures (called Program Units) written in the PL/SQL language within a form. Program Units can be used to add functionality that is not possible through Structured Query Language (SQL) or through the Oracle Forms interface. Some examples of Program Units were given in the discussion on Program Units in a Master-Detail Form. By storing a Program Unit within a form, many blocks in the form can take advantage of the same procedure which reduces code duplication and improves the maintainability of the code.

Procedures written in PL/SQL may also be stored within the Oracle Database itself as an object in the schema. Such Stored Program Units (also called Stored Procedures) are ideal for situations where highly standardized business rules or applications logic must be implemented across many forms or applications. In addition, procedures that require several queries be made to the database may be best implemented in the database as this reduces the network traffic and can significantly improve performance. A user may create stored procedures with the Oracle SQL*Plus command line tool.

Oracle Forms can make calls to both internal Program Units as well as to Stored Program Units. This flexibility allows application designers extensive control over the execution of applications and facilitates performance tuning.

In the following sections, examples of Oracle Forms Program Units and Stored Program Units will be given.

Creating Program Units in Oracle Forms

In this section, we will augment the EMPLOYEE form by adding a count of other members of a given employee's department. To add this functionality, we will add a new field item to the EMPLOYEE block, write a Procedure in PL/SQL to gather the data and then write a Trigger that will call the Procedure each time a new Employee record is queried.

1. Add a new item to the EMPLOYEE Block

Add a new item on the EMPLOYEE block to store the count of other employees in the department. See the section on Adding a New Item to an Existing Block for details.

Call this field OTHER_MEMBERS. Be sure it is a non-base table field and that a user will not be able to navigate there, insert, update or delete data in the field. Go to the Properties and check each of them:

Enabled: True
Navigable: False
Base Table Item: False
Query Only: False
Primary Key: False
Insert Allowed: False
Query Allowed: False
Update Allowed: False


Add a label to this field by switching to the Layout Editor, choosing the text tool and clicking next to the OTHER_MEMBERS field.

2. Create a Procedure to Count Other Department Members

In this step, create a Program Unit (procedure) in Oracle Forms to count the number of other members in the same department. For this procedure, we will pass in a parameter called IN_EMPLOYEE_SSN that will contain an employee's social security number. The procedure will return a value in the parameter OUT_COUNT_OTHER_MEMBERS that will contain the number of other employees who are in the same department.

To create this procedure, switch to the Object Navigator and scroll down to the Program Units branch. Pull down the Navigator menu and choose the Create menu item. A dialog box will appear asking you to name the new procedure. Type in the name OTHER_DEPARTMENT_MEMBERS and click on the OK button.

At this point, the PL/SQL editor will appear. Type in the following procedure body. An explanation of each part of the procedure is given afterwards:

 

<tr> <td><!--mstheme--><font face="arial, helvetica">-- Given an Employee's SSN, return the number of other employees -- in the same department. PROCEDURE OTHER_DEPARTMENT_MEMBERS (IN_EMPLOYEE_SSN IN NUMBER, OUT_COUNT_OTHER_MEMBERS OUT NUMBER) IS TEMP_COUNT NUMBER; -- Placeholder for count BEGIN TEMP_COUNT := 0; -- Initialize to 0 -- Try and fill TEMP_COUNT with the count of other -- Employees in the department SELECT COUNT(*) INTO TEMP_COUNT FROM EMPLOYEE WHERE EMPLOYEE.DNO = (SELECT EMPLOYEE.DNO FROM EMPLOYEE WHERE EMPLOYEE.SSN = IN_EMPLOYEE_SSN); -- See if we got a meaningful result IF (NVL(TEMP_COUNT, 0) <> 0) THEN OUT_COUNT_OTHER_MEMBERS := (TEMP_COUNT - 1 ); ELSE OUT_COUNT_OTHER_MEMBERS := 0; END IF; END;

The procedure is written in three main sections. First off, any lines starting with -- are comments and are ignored by Oracle forms.

The heading for the procedure definition indicates that one parameter, IN_EMPLOYEE_SSN, is a NUMBER data type and the procedure should expect a value to be passed in. The other parameter, OUT_COUNT_OTHER_MEMBERS, is also a NUMBER data type and a value will be passed back to the calling program through it.

To finish off the procedure header, the TEMP_COUNT variable is declared for use within this procedure only.

The BEGIN statement starts the body of the procedure. TEMP_COUNT is initialized to 0 and then a query is performed on the database to find the total number of employees in the same department as the employee whose SSN was supplied to the procedure.

The result in TEMP_COUNT is checked to see if it is a NULL value. If TEMP_COUNT is not NULL and it is not 0, then OUT_COUNT_OTHER_MEMBERS is set to equal TEMP_COUNT-1. Otherwise OUT_COUNT_OTHER_MEMBERS is set to 0.

The last END statement ends the body of the procedure.

Be sure to check to see that the procedure compiles by clicking on the Compile button. Any errors in the procedure will be highlighted. Some common problems include forgetting to use := in an assignment statement (like the Pascal language) and leaving off the END IF to finish up IF ... THEN statements.

When done, click on the Close button to close the PL/SQL editor.

3. Create a Trigger to Call the Procedure

Create a trigger to call the OTHER_DEPARTMENT_MEMBERS procedure. In the Object Navigator, open up the EMPLOYEE block and the SSN item. Highlight the Trigger branch for the SSN item, pull down the Navigator menu and choose the Create menu item.

A pop up list of trigger names will appear. Choose POST-CHANGE and click on the OK button to open the PL/SQL editor for this new trigger. Type in the following code for the POS-CHANGE trigger on the SSN item:

 

<tr> <td><!--mstheme--><font face="arial, helvetica">BEGIN DECLARE RETURN_COUNT NUMBER; BEGIN -- Call the Forms Procedure to get the -- count of others in the department. OTHER_DEPARTMENT_MEMBERS(:EMPLOYEE.SSN, RETURN_COUNT); -- Assign the return count to the field on -- the EMPLOYEE block. :EMPLOYEE.OTHER_MEMBERS := RETURN_COUNT; END; END;

Again, click on the Compile button to be sure the trigger compiles correctly and then click on the Close button to return to the Object Navigator.

4. Save, Compile and Run the Form

To this point, we have added a new non-base table field to the EMPLOYEE block, written a procedure (Program Unit) to count other employees in the same department and added a trigger to call the procedure each time a new employee record is queried.

To save you work, use the Save As menu item on the File menu and save this form under the name: emp_memb.fmb

Compile, Generate and Run the form to check its functionality. Note that when scrolling to a new employee's record, the OTHER_MEMBERS field is automatically populated with values indicating the number of other employees in the same department.

Creating Stored Procedures in SQL*Plus

In the previous example, we added a procedure (Program Unit) in Oracle Forms to perform a query on the database. Upon examination, each call of this procedure produces:

  1. A local call from the trigger to the procedure passing an employee's SSN.
  2. A remote access from the procedure to the database passing a SQL query.
  3. A remote return from the database containing a count.
  4. A small amount of processing in the procedure (IF ... THEN).
  5. A local return from the procedure to the trigger.

In this exchange, the highest costs are incurred by the network traffic required to go back and forth between client and server (steps 2 and 3). The lowest costs are incurred by the local calls between trigger and procedure and the local processing (steps 1, 4 and 5).

In this example, the SQL Query in step 2 and 3 does not return a significant amount of data. There are situations where, if a large amount of data were to be returned for each query, such queries would severely impact performance.

One solution to this problem is to move the queries and as much processing as possible "closer" to the data in the database. Thus our example for this section will be to implement the OTHER_DEPARTMENT_MEMBERS procedure in the Oracle database. To achieve this, we will need to use SQL*Plus to create a stored procedure.

Log in to SQL*Plus and enter the following CREATE PROCEDURE statement. Alternatively, save this statement in a text file and execute it using the START command in SQL*Plus.

 

<tr> <td><!--mstheme--><font face="arial, helvetica">CREATE PROCEDURE OTHER_DEPARTMENT_MEMBERS (IN_EMPLOYEE_SSN IN NUMBER, OUT_COUNT_OTHER_MEMBERS OUT NUMBER) AS -- Given an Employee's SSN, return the number of other employees -- in the same department. TEMP_COUNT NUMBER; -- Placeholder for count BEGIN TEMP_COUNT := 0; -- Initialize to 0 -- Try and fill TEMP_COUNT with the count of other -- Employees in the department SELECT COUNT(*) INTO TEMP_COUNT FROM EMPLOYEE WHERE EMPLOYEE.DNO = (SELECT EMPLOYEE.DNO FROM EMPLOYEE WHERE EMPLOYEE.SSN = IN_EMPLOYEE_SSN); -- See if we got a meaningful result IF (NVL(TEMP_COUNT, 0) <> 0) THEN OUT_COUNT_OTHER_MEMBERS := (TEMP_COUNT - 1 ); ELSE OUT_COUNT_OTHER_MEMBERS := 0; END IF; END;

After typing in this statement, be sure to RUN the command in the SQL*Plus buffer by typing RUN or by typing the forward slash /

Note: While creating a stored procedure, you may receive an error similar to the following:


    ORA-01031: insufficient privileges

This indicates the DBA has not granted you the authorization to create stored procedures.

The only major difference between the stored procedure created in the database schema through SQL*Plus and the procedure created in Oracle Forms is the way the header is formatted. Creating a stored procedure uses the following syntax:




CREATE PROCEDURE OTHER_DEPARTMENT_MEMBERS

         (IN_EMPLOYEE_SSN IN NUMBER,

          OUT_COUNT_OTHER_MEMBERS OUT NUMBER)  AS

Contrast this with the procedure done in Oracle Forms:


PROCEDURE OTHER_DEPARTMENT_MEMBERS

         (IN_EMPLOYEE_SSN IN NUMBER,

          OUT_COUNT_OTHER_MEMBERS OUT NUMBER) IS

Once the OTHER_DEPARTMENT_MEMBERS procedure (Program Unit) has been created in SQL*Plus, it is stored as an object in your schema and will remain there until a DROP PROCEDURE OTHER_DEPARTMENT_MEMBERS statement is issued. Any Oracle Form, Report or other procedure can make calls to OTHER_DEPARTMENT_MEMBERS as if it were a local procedure.

To see the stored procedure in action, open the emp_memb.fmb form and remove the Program Unit (procedure) OTHER_DEPARTMENT_MEMBERS from it. Save the form, compile, generate and run it.

The POST-CHANGE trigger on :EMPLOYEE.SSN automatically makes a call to the stored procedure version of OTHER_DEPARTMENT_MEMBERS.

To view the available Stored Program Units from within Oracle Forms, switch to the Object Navigator and open up the Database Objects tree. >From there, a list of schemas (users) will appear. Scroll down to your username and open it up. Then open up the Stored Program Units tree.

 

Oracle Reports Basics

We now turn our attention to another Developer/2000 tool called Oracle Reports. The Oracle Reports Designer allows the developer to create sophisticated reports in a variety of layouts and contains many customization features. In this section, the basic steps for creating a simple report and a Master-Detail report will be given.

Starting Oracle Reports under UNIX

Running the Oracle Developer/2000 tools is fairly consistent across UNIX flavors. Here, the directions for running the tools under Sun Solaris are given.

The command line to run the Motif version of Oracle Reports 2.5 Designer under Sun Solaris is:

r25desm To run Oracle Reports, type the name of the executable at the UNIX prompt. If you would like to run Oracle Reports and retain control over your UNIX login session, place an ampersand (&) character after the name of the executable as follows: unix% r25desm &amp;

unix% is the UNIX command prompt. This prompt may vary according to your installation of UNIX.

Starting Developer/2000 tools under Windows 95/NT

A typical installation of Developer/2000 creates several folders under the Start Programs menu. The main folders for the Developer/2000 programs are found under the menu item: Developer/2000 for Win95 and include the following items:

To run Oracle Reports under Windows 95/NT, click on the Start -> Programs -> Developer/2000 for Win95 -> Reports Designer menu item.

The Oracle Reports Object Navigator

The Oracle Reports main screen is called the Object Navigator (similar to Oracle Forms) and can be seen in the following figure:

The main sections of the Object Navigator are:

  1. Data Model - Contains information about queries used for a report.
  2. Layout - Contains information about how a report is formatted including headers, footers, margins, fonts, etc.
  3. Parameter Form - Contains information about the initial screen that is displayed when a form first runs.
  4. Report Triggers - PL/SQL code that can be executed before, during or after a report has been executed.

As with Oracle Forms, the first step in using Oracle Reports is to Connect to an Oracle server. This is accomplished by pulling down the File menu and selecting the Connect menu item.

Fill in your Oracle Username and Oracle Password (press the tab key to move between the fields).

For the Database field, type in the service name for your Oracle database. For example, if you are using Personal Oracle Lite, the Database: would be ODBC:POLITE. Contact your system administrator to learn the service name for your Oracle database and provide that information in the Database: field.

Click on the Connect button to complete the connection (Under some versions of UNIX, the button is labeled OK). If the user name or password is mistyped or incorrect, the dialog box will be re-displayed. Be sure to provide your Oracle username and password (not your UNIX host password).

At the bottom of the Object Navigator screen, you will see a status bar with an entry: Con indicating Oracle Reports is now connected to a database.

Creating Reports in Oracle Reports

Creating reports follows a 4 step process:

  1. Define the Data Model - This step specifies which queries should be run on the database including how multiple queries are related and how they are grouped. This step must be done by hand. Queries that have been created elsewhere can be imported into Oracle Reports.
  2. Define the Layout - This step specifies the layout of the report including the overall orientation of query results and the suppression of repeating groups. There are a number of default report layouts that can automatically be applied to a data model.
  3. Create and/or Customize the Parameter Form - If some user input is required in order to run the report, then a parameter form must be customized. All reports have a default parameter form.
  4. (optional) Create any triggers or program units that will be executed with the report.

Creating a Single-Table Report

In this section, we go through the steps for creating a report that views the contents of a single table.

1. Specify the Data Model

The first step is to specify the data model. From the Object Navigator, highlight the Data Model branch, click on it with the right mouse button and choose Edit from the pop-up menu. The Data Model Editor will appear.

The Data Model editor has a small tool palette with 8 tools on the left hand side of the screen. These tools are:

 

  1. Pointer tool - to select, move and resize objects.
  2. Magnify tool - to zoom in and zoom out.
  3. Query Tool - to create new queries in the data model.
  4. Data Link Tool - to link queries together.
  5. Summary Tool - to create summaries.
  6. Formula Tool - to create formulas on data items.
  7. Placeholder Tool - to create placeholders.
  8. Cross Product tool - to create cross products.

To get the data model started, click on the query tool button and then click on the open area of the Data Model editor. A new query called Q_1 will be created.

Return to the pointer tool and then double click on the Q_1 query to display its properties. In the text box labeled SELECT Statement type in a simple query such as:


SELECT FNAME, LNAME, SSN, SALARY

FROM EMPLOYEE ;

Then click on the OK button. After a short pause, the Data Model editor will re-appear with the query Q_1 and its associated columns below it in a group named G_1.

At any time, clicking on the query or the group with the right mouse button will produce a pop-up menu with the Properties menu item. Thus properties for the query, the group and each of the columns can be seen.

Some properties that can be changed include the sorting order of the columns in a group, the names of the query and the group (these can be given more meaningful names than Q_1 and G_1). To change the order of the columns in a group simply click and drag the column name to its new position with the left mouse button.

At this point, the Data Model has been completed and the Data Model Editor can be closed by clicking on the control box (in the upper left corner of the Data Model Editor window) and choosing the Close menu item.

2. Specify the Layout

Once the data model has been completed, the layout of the report must be specified. There are two ways of accomplishing this. First, it can be done by hand by placing each of the columns onto the Layout Editor screen. Or, a default layout can be created automatically. Here we will do the latter.

To automatically create a default layout for the report, pull down the Tools menu and chose the Default Layout menu item. The Default Layout screen will appear:

The six layout choices include:

  1. Tabular - Simple table with column headings at the top and data records in consecutive rows below.
  2. Master/Detail - Multiple tabular reports broken up by related collections of data.
  3. Form - Column headings on the left hand side with data values next to them on the right.
  4. Form Letter - Arbitrary placement of data items within a text body.
  5. Mailing Label - No column headings and records grouped into repeating sections sized to print directly to a sheet of mailing labels.
  6. Matrix - Column labels on both the left and the top with data values in the middle. Similar to a spreadsheet.

For this report, choose the Tabular Layout and click on the OK button. This will cause the Layout Editor to appear with the default layout.

Each of the concentric Frames indicates either the entire report (outer most box), the current group or the actual columns themselves (inner most). Columns can be resized, headings can be moved and formatted (change font, etc.) boxes, lines and text can be added, etc.

There are a number of drawing and editing tools on the palette to left side of the screen. To see what each one might do, move the mouse pointer over one of the buttons and pause for a few seconds. A pop-up text box will indicate the potential use for the tool.

Note: To get a sense of the behavior of the report and group frames, try placing some text inside one of the frames and run the report. Then place the text inside another frame and run it again.

At this point, the data model has been created, and a default layout has been created. The report can now be generated and run.

A default parameter form will be created automatically so we can safely omit this step for now.

3. Saving, Generating and Running the Report

To save a report, pull down the File menu and choose the Save option. This is similar to saving an Oracle Form. The source code for Oracle Reports are saved in files with an .RDF file name extension. Compiled and generated reports are saved with a .rep extension.

For this example, save this report as EMPLOYEE.RDF

Once the report is saved, it can be run by pulling down the File menu and choosing the Run menu item. The default Parameter Form will appear.

The default parameter form has two fields. One is a list of possible Destination Types for the report including: Screen, Printer, E-Mail, File and Preview. The Destination Name field will change depending on the destination type.

Keep the default parameter form set at Destination type Screen and click on the Run Report button.

As the report is running, an Activity screen will appear giving an indication of the processing that is currently underway.

The Activity will go through 3 stages: Client Activity while the queries are prepared, Server Activity when the queries are executed and finally Client Activity as the report is formatted. When this is finished, the report will appear on screen:

 

Creating a Master-Detail Report

In this section, we go through the steps for creating a report that views the contents of two tables in a Master-Detail (one to many) relationship. To avoid any confusion, save and Close any existing reports before proceeding.

1. Specify the Master-Detail Data Model

As in the single-table report, we begin by specifying the data model for the Master-Detail report.

Begin in the Object Navigator and invoke the Data Model Editor by clicking the right mouse button on the Data Model branch. Then choose the Data Model Editor menu item from the pop-up menu.

Add two queries to the Data Model by clicking on the Query Tool icon and then clicking on the Data Model Editor window.

In query Q_1, specify the SELECT Statement as:


SELECT * FROM DEPARTMENT;

Query Q_1 will act as the Master query for the report.

In query Q_2, specify the SELECT Statement as:


SELECT FNAME, LNAME, SALARY, DNO

FROM EMPLOYEE;

Query Q_2 will act as the Detail query in this Master-Detail report. The Data Model Editor should look like the following (Note the two groups):

The final step in the Data Model is to link the Master query (Q_1) with the detail query (Q_2). To accomplish this:
Switch to the Data Link Tool by clicking on its icon.
Click on the DNUMBER column in group G_1 and drag the mouse over to the DNO column in group G_2.

A Link (solid black line) will appear between group G_1 and query Q_2.

At this point, the Data Model is completed. Close the Data Model Editor.

2. Specify the Master-Detail Layout

Create a default layout for the Master-Detail report by pulling down the Tools menu and choosing the Default Layout menu item. When the default layout screen appears, choose Master/Detail and click on the OK button. The default Master-Detail layout will appear.

3. Saving, Generating and Running the Master-Detail Report

At this point, the report can be saved, generated and Run.

For this example, save the Master-Detail report as DEPT_EMP.RDF

Once again, the parameter form is created by default and includes options to output the report to the Screen, Printer, E-Mail, etc.

 

Creating Menus in Oracle Forms

Thus far, we have covered the basics for creating forms and reports using Developer/2000. Some advanced features such as using stored procedures (Program Units) have also been introduced.

In a complete Database System, the applications consist of many forms and reports that might be used by a number of users. Access to forms and reports is typically accomplished through menus. In Developer/2000, menus can be created to guide users into forms and reports of interest. Menus are designed as part of the Oracle Forms designer.

In Oracle Forms, every form runs with one of the following:

  1. The default menu which includes the Action, Edit, Block, Field, Record, Query, Window, Help menus. The structure of the default menu cannot be changed.

  2. A custom menu that you define as a separate module and then attach to the form for runtime execution.
  3. No menu at all.

The default menu is part of the form module. However, custom menu modules are separate from form modules. So, when a single-form application runs using a custom menu, there will be two executable files:
an .FMX form module
an .MMX menu module

In a multi-form application, Multiple forms can share the same menu, or each form can invoke a different menu.

To explicitly specify the default menu:

  1. Select the name of the form/module in the Object Navigator. Click with the right mouse button and choose Properties from the pop-up menu.
  2. In the Menu Module property, type DEFAULT.

    If you want the form to run without a menu, leave the Menu Module property blank.

The default menu is suitable for control over the current form being executed, however, it does not contain custom menu items pertaining to a specific application. In the following section, the steps to create a custom menu structure will be introduced.

The Oracle Forms Menu Hierarchy

The Oracle Forms menu hierarchy contains three object categories:

  1. Menu modules - Like form modules, a menu module can contain its own items (menus and menu items), program units and other objects.
  2. Menus - Including main menus, individual menus, and submenus
  3. Menu items

Creating a Custom Menu

Designing a custom menu requires five basic steps:

  1. Creating a menu module
  2. Creating menus and menu items
  3. Assigning commands to menu items
  4. Saving and generating the menu module
  5. Attaching a menu module to a form

 

1. Creating a Menu Module

The first step in designing a custom menu is to create a menu module. There are several ways to create a new menu module:

  1. Pull down the File menu, highlight the New menu item and then select the Menu menu item.
  2. Navigate down to the Menus subtree in the Object Navigator, pull down the Navigator menu and choose the Create menu item.

A new menu module will be create and a default name will be assigned. Click on this default name and assign it a more meaningful name such as mymenu.

2. Creating menus and menu items

Creating menus and menu items can be accomplished using the Menu Editor. To activate the menu editor, first highlight the name of your menu, then pull down the Tools menu and click on the Menu Editor item.

The main menu items go across the top of the screen. To add a new menu across the top, click on the Build Across icon on the toolbar:

Sub-menus and menu items appear below each one. To add a new sub-menu or menu item, click on the Build Down icon on the toolbar:

To change the name of a menu or menu item, click in the menu object and over-write the name that appears there.

Using the icons, create and re-label several menus and menu items to appear as follows:

Once this is completed, the basic menu structure will be in place. The next step is to add functionality to each menu item.

3. Assigning Commands to Menu Items

The default behavior for clicking on any menu is for that menu's items to be displayed. The next step is to assign commands to each of the menu items so that when a user clicks on a menu item, a form, report or SQL*Plus script is executed.

To assign a command to a menu item:

  1. Select the desired menu item in the Menu Editor. Click with the right mouse button and choose Properties to bring up the properties of that menu item.
  2. The properties of interest here are:
    Command type: Indicates what kind of command should be run: Form, Report, PL/SQL, other menu. In general, the PL/SQL command type is used to launch Oracle Forms and Oracle Reports. The specific Form and Report command types are maintained for backwards compatibility only.
    Command Text: The text of the actual command to run.

    To run a form from the menu item, choose Command Type PL/SQL. Double click on the Command Text property and type in the PL/SQL editor:

    
    BEGIN
    
      RUN_PRODUCT(FORMS, 'employee', SYNCHRONOUS, RUNTIME,
    
                  FILESYSTEM, NULL, NULL);
    
    END;
    
         

    RUN_PRODUCT is a PL/SQL built-in function that runs another Oracle Developer/2000 product. The parameters to RUN_PRODUCT are:
    Product: FORMS or REPORTS
    Document/Module: name of the file in single quotes
    Command Mode: SYNCHRONOUS or ASYNCHRONOUS
    Executable Mode: RUNTIME
    Location: FILESYSTEM or DATABASE
    Parameter List: A list of parameters to pass to the form or NULL
    Display: Type of display to show the product on. Can be NULL to indicate the current display.

    Compile this code by clicking on the Compile button. Then close the PL/SQL editor.

Repeat the above steps for each menu item.

A final menu item should be created to Exit the application. Call the menu: Exit Application and enter the following PL/SQL code:


BEGIN

  EXIT_FORM;

END;

EXIT_FORM is a built-in procedure that exits the current form.

A View of the complete mymenu is show below:

 

 

4. Save and Generate the Menu Module

At this point, all of the menu structure has been created and commands for each menu item have been specified. The menu module must now be saved to a file (with a .mmb extension) and Generated.

To save the menu module, pull down the File menu and choose the Save menu item. Specify a file name with a .mmb extension. This is the "source code" for the menu. For this example, use the file name mymenu.mmb.

To generate the menu module, pull down the File menu, click on the Administration menu item and choose Generate from the flyout menu. Generating a menu module results in a file with a .mmx extension. In this example, the generated menu module becomes: mymenu.mmx

 

5. Attaching a Menu Module to a Form

A custom menu must be attached to a form. Typically, a form with no data entry on it is used as the main screen to host the custom menu.

 

  1. Create a new form with no blocks or items on it. Pull down the File menu, select New and then select Form.
  2. Display the properties for the form and set the Menu Module property to the file name of the menu module (see previous step). For this example, it should be: mymenu.mmx
  3. Make sure the Use File property is set to True.

  4. Save and generate this form (for example: blankform.mmb).
  5. Run the form.

Notice that when running this blank form, the default menu is replaced by the mymenu Menu Module.

 

All material Copyright 1997, 1998 R. Holowczak