`
wsql
  • 浏览: 11715327 次
  • 性别: Icon_minigender_1
  • 来自: 深圳
文章分类
社区版块
存档分类
最新评论

Oracle9i Developer Suite - A Tutorial on Oracle9i Forms and Reports

 
阅读更多

Oracle9i Developer Suite
A Tutorial on Oracle9i Forms and Reports

Covering Developer version 9i for Windows NT/2000/XP

Richard Holowczak


Tutorial Version 5.2 - 1:11 PM 8/16/2011

Please feel free to use these tutorials for your own personaluse, education, advancement, training, etc. However, I kindlyask that you respect the time and effort I have put intothis work by not distributing copies, either in whole or in part,for your personal gain. You may not under any circumstancesdownload, cache or host copies of these tutorials on your ownservers. Use of these tutorials for commercial trainingrequires a special arrangement with the author or authors.


Contents


1. Introduction

This tutorial introduces the Oracle9iDeveloper Suite Release 2 that includes Oracle Forms 9.0 andOracle Reports 9.0. The main objectives areto demonstrate and provide hands-on instructions oncreating and modifying data entry and query formsin various configurations, reports and graphics.

Caveats: Please note that Oracle tends to changethings like menu items, prompts and other smallthings between each major (certainly) and oftenminor release. Depending on the exact release of Oracle9i Developer Suite you have, you may find some smalldiscrepencies between what is shown in this tutorialand what you see on your screen.

2. Prerequisites

Before following this tutorial, a studentmust have a valid user account in an Oracleserver or a local installation of an Oracle database. Contact your DBA or systems administratorto learn the details of how Oracle server isset up in your organization.

If you have a local instance of the Oracle database,please do not use the SYSTEM (DBA) account to practicedevelopment. There is a good chance you can corruptthe entire database by mistake. You are much betteroff making use of the SCOTT/TIGER schema or (even better)creating a separate user (schema) for this tutorial workand for practicing.

One more word of caution. Oracle has designed Oracle9ias a three tier architecture. This means that thedatabase (Oracle9i server) should run on one server,the application Server (oracle9i Application Server orOracle9iAS Containers for Java (OC4J) server as used here) runs on a second server andthe client application (written in Oracle Forms and Reportsfor example) runs on a third machine. One can get all ofthese components to run on the same machine, but it requiresboth a fast processor and a lot of RAM. Here is one example:

Service/Application RAM consumed while running
Oracel9i Database (minimal instance) 140 MB
Oracle9i Forms Builder 42 MB
Oracle9i Reports Builder 80 MB
Oracle9i Forms Runtime web launcher 12 MB
OC4J Service (Java) 36 MB
Netscape 4.7 running a form 35 MB
Oracle9i SQL*Plus 8 MB
Total 353 MB

Therefore, having at least 512 MB of real RAM (not swap space) isa necessity. 1 GB of RAM will yield significantly betterperformance. If you have less than 512 MB of RAM, you shouldcreate a swap file (Virtual memory) of at least 1 GB.

The student should also be familiarwith Oracle's SQL*Plus tools and should be comfortable connecting to a database schema,creating tables, inserting and manipulating data(with SQL INSERT, UPDATE and DELETE statements) andwith querying table data (with SQL SELECT statements).

If you are new to Oracle and are looking for an introduction to SQL*Plusplease visit the SQL*Plus Tutorial Page.

This tutorial relies on the existence of afew tables in the student's schema. Use theOracle SQL*Plus tool to create three tables namedEMPLOYEE, DEPARTMENTand DEPENDENTwith the following attributes. 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.

Attribute Data Type
EMPLOYEE Table
FNAME VARCHAR(8)
MINIT VARCHAR(2)
LNAME VARCHAR(8)
SSN NUMBER(12)
BDATE DATE
ADDRESS VARCHAR(30)
SEX VARCHAR(1)
SALARY NUMBER(7)
SUPERSSN NUMBER(12)
DNO NUMBER(2)
Attribute Data Type
DEPARTMENT Table
DNAME VARCHAR(15)
DNUMBER NUMBER(2)
MGRSSN NUMBER(12)
MGRSTARTDATE DATE
Attribute Data Type
DEPENDENT Table
ESSN NUMBER(12)
DEPENDENT_NAME VARCHAR(10)
SEX VARCHAR(1)
BDATE DATE
RELATIONSHIP VARCHAR(10)


The following CREATE TABLE and INSERT statementscan be used to create the above tables and insertsome baseline data using the Oracle SQL*Plus tool.Note that different versions of Oracle accept either2 digit or 4 digit year designations in the DATEdatatype. It seems the latest versions (Oracle8 and Oracle8i)default to accept 4 digit years. The INSERT statementsbelow should be formatted accordingly.

CREATE TABLE employee (FNAME VARCHAR2(15), MINIT VARCHAR2(2), LNAME VARCHAR2(15), SSN NUMBER(12) NOT NULL, BDATE DATE, ADDRESS VARCHAR2(35), SEX VARCHAR2(1), SALARY NUMBER(7) NOT NULL, SUPERSSN NUMBER(12), DNO NUMBER(2) NOT NULL) ;ALTER TABLE employee ADD CONSTRAINT pk_employeePRIMARY KEY (ssn);CREATE TABLE DEPARTMENT (DNAME VARCHAR2(15), DNUMBER NUMBER(2) NOT NULL, MGRSSN NUMBER(12), MGRSTARTDATE DATE) ;ALTER TABLE department ADD CONSTRAINT pk_departmentPRIMARY KEY (dnumber);CREATE TABLE DEPENDENT (ESSN NUMBER(12), DEPENDENT_NAME VARCHAR2(15), SEX VARCHAR2(1), BDATE DATE, RELATIONSHIP VARCHAR2(12)) ;ALTER TABLE dependent ADD CONSTRAINT pk_dependentPRIMARY KEY (essn, dependent_name);ALTER TABLE dependent ADD CONSTRAINT fk_employeeFOREIGN KEY (essn) REFERENCES employee (ssn);ALTER TABLE employee ADD CONSTRAINT fk_departmentFOREIGN KEY (dno) REFERENCES department (dnumber);INSERT INTO DEPARTMENT VALUES ('RESEARCH', 5, 333445555, '22-MAY-1978') ;INSERT INTO DEPARTMENT VALUES ('ADMINISTRATION', 4, 987654321, '01-JAN-1985') ;INSERT INTO DEPARTMENT VALUES ('HEADQUARTERS', 1, 888665555, '19-JUN-1971') ;INSERT INTO EMPLOYEE VALUES('JOHN','B','SMITH',123456789,'09-JAN-1955','731 FONDREN, HOUSTON, TX', 'M',30000,333445555,5) ;INSERT INTO EMPLOYEE VALUES('FRANKLIN','T','WONG',333445555,'08-DEC-1945','638 VOSS,HOUSTON TX', 'M',40000,888665555,5) ;INSERT INTO EMPLOYEE VALUES('ALICIA','J','ZELAYA',999887777,'19-JUL-1958','3321 CASTLE, SPRING, TX', 'F',25000,987654321,4) ;INSERT INTO EMPLOYEE VALUES('JENNIFER','S','WALLACE',987654321,'20-JUN-1931','291 BERRY, BELLAIRE, TX', 'F',43000,888665555,4) ;INSERT INTO EMPLOYEE VALUES('RAMESH','K','NARAYAN',666884444,'15-SEP-1952','975 FIRE OAK, HUMBLE, TX', 'M',38000,333445555,5) ;INSERT INTO EMPLOYEE VALUES('JOYCE','A','ENGLISH',453453453,'31-JUL-1962','5631 RICE, HOUSTON, TX', 'F',25000,333445555,5);INSERT INTO EMPLOYEE VALUES('AHMAD','V','JABBAR',987987987,'29-MAR-1959','980 DALLAS, HOUSTON, TX', 'M',25000,987654321,4) ;INSERT INTO EMPLOYEE VALUES ('JAMES','E','BORG',888665555,'10-NOV-1927', '450 STONE, HOUSTON, TX', 'M',55000,NULL,1) ;INSERT INTO DEPENDENT VALUES (333445555,'ALICE','F','05-APR-1976','DAUGHTER') ;INSERT INTO DEPENDENT VALUES (333445555,'THEODORE','M','25-OCT-1973','SON') ;INSERT INTO DEPENDENT VALUES (333445555,'JOY','F','03-MAY-1948','SPOUSE');INSERT INTO DEPENDENT VALUES (123456789,'MICHAEL','M','01-JAN-1978','SON');INSERT INTO DEPENDENT VALUES (123456789,'ALICE','F','31-DEC-1978','DAUGHTER');INSERT INTO DEPENDENT VALUES (123456789,'ELIZABETH','F','05-MAY-1957','SPOUSE');INSERT INTO DEPENDENT VALUES (987654321,'ABNER','M','26-FEB-1932','SPOUSE');

Make certain you issue a COMMIT; after inserting these records otherwise yourforms and reports will not see the uncommitted data. Existing out of SQL*Plus will alsoissue a COMMIT for you automatically.

3. Oracle9i Developer Suite Tools

Oracle9i Developer Suite contains the following main components:
  1. Oracle9i Forms - For designing data entry forms.
  2. Oracle9i Reports - For designing reports.
  3. Oracle9i JDeveloper - For developing Java applications or applets
  4. Oracle9i Warehouse Builder and Discoverer - OLAP tools for creating data warehouses and OLAP user interfaces
  5. Oracle9i Designer - Oracle's CASE tools that include modeling and code generation capabilities.

This tutorial covers the Developer Forms and Reports components.

In the following section, the procedures to run Oracle Formsunder MS Windows (98/NT/2000/XP) will be given.

3.1 Starting Developer tools under Windows

A typical installation of Oracle9i Developer Suite (9iDS) creates severalfolders under the Start Programs menu. The two main foldersare both named after the Oracle Home you chose to install 9iDS under.A brief guide to installing 9iDS can be found here: http://cisnet.baruch.cuny.edu/holowczak/oracle/install/9ids/

Pay particular attention to the post-installation steps regarding how theOracle9iAS Containers for Java (OC4J) service runs and on how to install theJInit plugin for your web browser.

The main 9iDS programs represented in this tutorial are found underthe Oracle9i Developer Suite home and includethe following items:

To run the 9iDS Forms Builder under Windows , click on theStart -> Programs -> Oracle9i Developer Suite - Home -> Forms Developer -> Form Buildermenu item.

To run the 9iDS Reports Builder under Windows , click on theStart -> Programs -> Oracle9i Developer Suite - Home -> Reports Developer -> Reports Buildermenu item.


For older versions of Oracle Developer the start menuwould might have items such as: Developer 2000 R2.1,Developer 2000 R2.0, Devloper 6i and so on.

For Oracle9iDS installs in UNIX or Linux, use the following scripts:

Forms Builder f90desm.sh
Reports Builder rwbuilder.sh

Note: In your organization, 9iDS may beinstalled or configured slightly differently. In particular, the programs may be grouped underdifferent menus.Please contact your DBA or systems administrator, orrefer to any local documentationto learn how to run the 9iDS tools.

4. The 9iDS Forms Builder Main Screen

Once the appropriate menu item has been clicked on (for Windows), the Oracle FormsBuilder Object Navigator will appear.

4.1 Oracle Forms Object Navigator

After starting up Oracle Forms, the main screenwill be presented.

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

The menu bar across the top provides access toalmost all of the features of Oracle Forms.

  • The File menu has options to create,open, save and print forms. This menu alsoincludes an option to connect to thedatabase and the Administration option that isused to compile forms and menu code.The last option on the File menu is the Exit option that will exitOracle Forms Builder.
  • The Edit menu has options to cut, copy andpaste objects, to invoke an editor and to undoan operation.
  • The View menu toggles the object navigatordisplay between visual elements and ownership elements.
  • The Layout menu has items thatcontrol objects on a form much in the way a drawing packagedoes. Objects can be resized, moved, painted and otherwisemanipulated using items on this menu.
  • The Program menu has items that controlform compilation, generation and execution (Run).Triggers, Procedures and other PL/SQL codecan also be created from this menu. The Smart Triggers menu option displays themost commonly used triggers according to the type ofobject currently selected in the object navigator.
  • The Debug menu has optionsfor debugging forms as they are running. It includesmenu items to establish break points and to step overthe execution of code one line at a time.
  • The Tools menu has options toswitch between severaldifferent screens including the Layout editor, Menu editor andObject Navigator. There are also severalwizards that can be launched from this menu includingthe Data Block wizard, the Layout wizard, and the LOV (List of Values) wizard.
  • The Windows menu displays menuitems for each of the windows you have open.By default, only one item - the Object Navigator -is displayed.
  • Finally, the Help menu can be usedto display help topics.

By default, a button bar is provided on the left handside of the Object Navigator window. Each buttonduplicates some functionality on the menus. For example,clicking on the top icon (an opened folder) is thesame as pulling down the File menu andchoosing the Open option.To see what a button might do if pressed,place the mouse cursor over the button and ashort message will appear describing the button'sfunction.

As with earlier versions of Developer, in Developer 9.0, a form, report or graphicis generally referred to as a Module. Whenfirst starting the Forms Builder, a default module iscreated called: MODULE1

The content of the Object Navigator is broken downinto 6 major sections:

  1. Forms - Contains form objects and allof their sub-objects such as data blocks, triggers,alerts, canvases, program units (procedures and functions), parameters and windows. More than one form can be open at a time.
  2. Menus - Contains menu objects and all oftheir sub-objects such as menu items, parametersand program units. More than one menu can be open ata time.
  3. PL/SQL Libraries - These are libraries of PL/SQL code (stored either in the database or in .plb files). Libraries can be shared amongdifferent forms and among different developers.
  4. Object Libraries - These are libraries containinga collection of objects (anything that can appear on a form).
  5. Built-in Packages - Lists all of the packages(PL/SQL procedures) available by default to all forms, reports, etc.
  6. Database Objects - Lists all of the databaseobjects (Tables, Views, PL/SQL Libraries, and StoredProgram Units) in the user's Oracle database schema.

As objects are created, they are displayed under the appropriatesection. Context sensitive menus can be displayedfor each object by clicking on an object with the right mousebutton.

4.2 Connecting to the Database

The first step in working with any of the Developer tools isto establish a connection to an Oracle database.This is accomplished by pulling down the Filemenu 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 inthe service name for your Oracle database.For example, if you are using Personal Oracle Lite, theDatabase: would be ODBC:POLITE. If youhave a local database running on the same machine, youmay be able to connect directly to it by leaving thisfield blank. If you are working in a client/server environmentwhere the Oralce server resides on another machine, youwill have to configure the Oracle Net8 middleware to establish a connection with that server. You would then supply the Net8 service name in the Database field.Alternatively, contactyour system administrator to learn the service name for your Oracledatabase and provide that information in the Database: field.

Click on the Connect button to completethe connection (Under some versions of UNIX, thebutton is labeled OK). If the user name orpassword is mistyped or incorrect, thedialog box will be re-displayed.Be sure to provide your Oracle username andpassword (not your UNIX host or Local Area Network password).

Each time any of the Developer tools areexecuted, the first step will be to connect toan Oracle database in this fashion.

5. Forms Design

A typical database schema contains dozens of tables,each with several columns of various data types.We develop applications (forms, reports, menus, etc.) tomake the job of manipulating data in those tableseasier for users.In Oracle Forms, a form (or data entry form) acts like awindow into the database schema. An individual focuses the attention of the user to one or a few of the tablesat a time. In addition, a form can give prompts so theuser knows what kind of input is expected and how datais to be entered and manipulated.

By default, every form in Oracle Forms has the capabilityto query existing data in a table, modify existing dataand add new data (records) to the table. A form is built upusing one or more data blocks that correspond to tables in the database. Fields within the data blockcorrespond to columns in the database table. A data block is similar to a DataWindow object in PowerBuilder.In the following figure, a typical form is shown.

This form has two data blocks, one for the EMPLOYEEtable, and one for the DEPENDENT table. Thesedata blocks are arranged in a Master/Detail setupwhere a single Employee record (the master) is associatedwith one or more Dependents records (the details).

By default, forms also gives a button bar and a menu.These can be used to scroll through the records in a block,navigate between blocks, set up and perform queries against the tables, insert, update and delete records, clear theform and exit the form. Finally, at the bottom of each formis a status bar that displays any relevant prompts orerror messages and an indication of the records in thecurrent data block.

There are four main types of forms that can be designed.
Single Block Form. This form contains a single data block corresponding to a single database table. Single block Form with lookup field. This form contains asingle data block corresponding to a single database tablewith the addition of one or more fields that display datafrom some other tables. Such data is "looked up" whenthe form runs.
Master/Detail Form. This form contains two data blocksthat are arranged in a master/detail (one to many) relationship. Master/Detail Form with lookup fields. This form is similarto the Master/Detail but has the additional lookup fieldsin the master and/or detail blocks.

There are additional variations such as a Master/Detail/Detail form with 3 blocks and so on. However, the four types illustratedabove are the most common ones.In this tutorial, all four types will be demonstrated.

6. Creating and Running a Form with a Single Block

In this section, the basic steps forcreating a basic data entry and query form fora single database table will be covered.

In general, a Data Block on a form corresponds toa table in the database. This is calledthe Base Table for the data block. For eachtable displayed on the form, a new data block will becreated. For this example, only one data block willbe created for the EMPLOYEE table.

A block appears on a Canvas which iscontained in a Window. There are many optionsto control multiple canvas views and multiple windowsper form. In these examples, we concentrate ona simple arrangement using a single canvas anda single window.

There are a number of ways to create a data block.One way is to manually define the base table andcolumns, and their positions on the form. Whilethis is possible, it can be very tedious.

Oracle Forms Builder provides two main wizards (programs thatguide the developer in performing common tasks)to create data blocks:

  1. The Data Block wizard guides the developerthrough the steps of choosing a base table andcolumns.
  2. The Layout wizard guides the developerthrough arranging the base table and columns onthe form.

6.1 Creating a New Block

To create a new block, pull down the Toolsmenu and select the Data Block wizard menu item.The following dialog box will appear:


Click on the Next button. The following dialog box will appear:


There aretwo types of data blocks that can be created.Table/View and Procedures. For this example,choose Table/View and click on the Next button.

The next step is to choose a base tableand columns that will belong to the data block.The following dialog box should appear:


To associate a database table with the block, click on the Browse... button to theright of the Table or View field. The followingdialog box will pop up.

Make sure the Current User andTables buttons are selected Alist of your tables will appear. If no list oftables appears, make certain you have created thetables and inserted data as directed in thePrerequisites section 2.If you are still logged in to SQL*Plus, make certainyou issue a "COMMIT;" statement to commit all of thechanges to the schema.

Highlight the name of the database table(EMPLOYEE in this example) andclick on the OK button.

The wizard should reappear with the nameof the table and a list of availablecolumns displayed. To include acolumn in the data block, highlight thecolumn name and click on the right arrow.The name of the column should move overto the right hand side.

For this example, select the FNAME, LNAME,SSN, BDATE, SALARY and DNO as in thefollowing figure:


Click on the Next button, a dialog box willappear allowing you to name the data block.In general, it is best to simply use the nameof the table as the name of the data block asshown below:


Click the Next button once more and the finaldialog box for the Data Block wizard willappear:


In this example, we want to continue onto the Layout wizard in order to specify howthe new form will appear. Make sure theCreate the block, then call the Layout wizardoption is selected and click on the Finish button.

The data block will be created (as can be seen inthe Object Navigator in the background). The objects createdinclude the EMPLOYEE data block containing items foreach of the columns that were selected in the third step ofthe wizard.

Once the new data block and items are created, the first dialog box in the Layout wizard will appear:


Click on the Next button and the followingdialog box will appear:


The layout for a data block may be placed onany existing canvas. In this case, there areno existing canvases so the only option available isto create a new canvas.

Click on the Next button to display thefollowing dialog box:


In this dialog box, the columns froma given base table on a data block can beadded to the layout. Since we are laying outthe EMPLOYEE data block, it is highlightedautomatically. Move all of the Available Columnsover to the Displayed Items side by clicking onthe double right arrow and click on the Next button.

In this dialog box, the field labels, field sizesand field heights can be altered. Change thefield labels as shown below and click on theNext button:


The following dialog box will appear:


There are two main styles for forms.A Tabular layout arranges the field labelsacross the top of the form with a numberof records below as in a spreadsheet.

A Form layout places the field labelsto the left of the fields. Generallyonly one record is displayed at a time.

For this example, choose a Form layoutand click on the Next button.

In the following dialog box, type a title forthe frame (around the data block) and check theoption to include a scroll bar.

The number of records displayed for a Form layoutshould be 1. The number of records displayed in aTabular layout can be more than 1.


Click on the Next button and the finaldialog box for the Layout wizard will appear.


Click on the Finish button to createthe layout. A new Canvas will be created with the newblock and all of the form items added in the layout wizard.

At this point the Layout Editor should appear andthe new form with the data block will appear.

6.2 The Layout Editor

To view the actual form and its blocks and fields, pull down the Tools menu and select theLayout Editor menu item (if it not alreadydisplayed).

In the Layout Editor, fields and labels can be movedaround by clicking and dragging. Other text, lines andboxes, etc. can be added using the tool palette onthe left side of the window.

Some common operations on the Layout Editor include:

  • Move a field or a field label by clickingand 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 anywhereoutside 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 Formatmenu 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 blackboxes around the field) to re-size the field.

    For exampe, on the employee form, you may wish tomake the FNAME field wider to accomodate longer first names.
  • Use the Zoom In tool to zoom in to theform (e.g., make everything appear larger). Or use the Zoom Out tool to zoom out of the form.

6.3 Displaying Properties

While in the Layout Editor,double clicking on an object will bring upthat object's properties. Optionally, clickingon an object with the right mouse button willbring up a small menu which has the Propertiesmenu item on it. The following figureshows the properties for the FNAMEitem.

Each type of object has a slightly different set of properties.For example, double clicking on theEMPLOYEE data block in the Object Navigator (pull down theTools menu and choose Object Navigator) willdisplay that data block's properties.

Some properties of interest for a data block include:

  • WHERE Clause - specify a WHERE clauseto filter the selection of rows from the base table.
  • ORDER BY Clause - specify an ORDER BYclause to alter the displayed order of the records.
  • Delete Allowed, Insert Allowed, Query Allowedand Update Allowed- toggle these properties to allow or prevent theaction. For example, setting Delete Allowed to FALSE prevents users from deleting recordsfrom the corresponding base table using this form.
  • There are numerous other properties that affect thefonts, colors, size and position of objects in the block.

6.4 Saving, Compiling and Running Forms

Forms can be saved in files in a directory of yourchoosing. The "source code" for a form is storedin a file with a .fmb extension.Compiled forms have a .fmxextension.

Under MS Windows, forms can be saved ina folder (subdirectory) on the local hard disk,on a file server or on a floppy disk.It is desirable tomaintain separate subdirectories (folders) for formsassociated with different projects. One significantitem to be aware of is that Oracle Forms will havedifficulty running any form saved in a folder thathas spaces or punctuation other than the underscorecharacter in the name. For example, it is commonin Windows to just save files in "My Documents".However, since this has a space in the name, theform will not run.

Therefore, if you are developing on a local machine,it is recommended that you at least make a simple directorysuch as C:\forms or A:\forms if using a floppy disk and save all forms files there.

6.4.1 Saving a Form

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

Under MS Windows, this dialog box looks like:

Note that in your company or organization, formsand other files may have to be saved on floppy diskor in a special directory.The above figure is simply an example.

It is critical that you do not save the file with spaces in the file name.In addition, you should save the files ina local directory where the directoryname(s) have no spaces in the them.If you save the file in "My Documents" for example, or on the Windows desktop,you will be unable to run or test the forms.

Fill in a file name in the File Name:field. Be sure it contains no spaces and that it ends with .fmb

Click on the Save button to savethe file.For this example, use the file name employee.fmb

To save this form on a floppy disk in the A: drive,use the file name: A:\employee.fmb

To enhance portability of forms across differentoperating systems, use file names thatbegin with a letter, contain no spaces or punctuation(except for the underscore character), have alllower case letters and are relatively short.

Reminder: Do not use spaces in the namesof the file or the names of any folders (directories).

If you are working on a form and wish to save itunder a different file name, follow the same stepsas above, only select the Save As menu itemin place of the Save menu item. For example, ifyou have finished working on the Employee form and wishto save off a copy before adding some code or programmingsome program units, the Save As menu item can beused to save off a copy before proceeding.

6.4.2 Compiling/Building a Form

Before a form can be executed (run), it must be Compiledand Built. Compiling runs a PL/SQL compiler onall of the procedures and triggers in the form.Building (or compiling the file) createsthe .fmx file that is then used to run the form.

To compile a form, first make sure the form is saved.Pull down the Program menu and select theCompile Module menu item.

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 theyare encountered. A record of the compilationprocess, including error messages, is kept in a file with a .errextension. For example, if the form isnamed employee then the recordof the compilation will be stored in employee.err

Alternatively, Oracle Forms defaults to automaticallycompiling the form each time it is executed (run)from the forms Builder as will be demonstratedin the next step. For more details on the defaultbehavior, pull down the Tools menu andcheck the Preferences menu item.

6.4.3 Running a Form

As discussed in the introduction section, Oraclce9i Formsare intended to urn in a three tier architecture. The databaseacts as the back-end tier. The Oracle9iAS Containers for Java (OC4J)service acts as the middle tier and a web browser (Internet Explorer5.x or above or Netscape Navigator 4.7 or above) takes on therole of the user-interface tier.

Unless this environment has already been established,a local client-side OC4J service may be used in place of the middle tier.

Before running a form, the OC4J service should be started. Use the StartMenu to navigate to:Start -> Programs -> Oracle9i Developer Suite - Home -> Forms Developer -> Start IC4J Instance
A Command prompt will open (in Windows 2000) similar to the following:

OC4J Running

Note that by default, OC4J runs on port 8888. If this conflicts with another service or if you have firewall software that preventsopening up such ports, you will need to change the configuration ofeither OC4J, your firewall software or both.

If you receive an error when running OC4J, it may be becauseanother OC4J instance is already running. If you are uncertain, save yourwork and reboot the PC. Then run a single instance of OC4J.

With OC4J configured and running, and the formdesigned and saved, it canbe executed. To run the form, pull down the Program menuand select the Run Form menu item.This will launch your default web browser which will then connect to OC4J (port 8888) on the local machine. Oracle's JInitiator will loadand the form should appear.

Use the various menus and items to query the form, enter new data and save new and changeddata. Please read the section below on Query By Example to see how to query data from the underlying tables andpopulate the fields in the form.

If OC4J service is not running, an error message similar to thefollowing will be displayed:

If you see error FRM-10142, make sure the OC4J Instance is running. Look in theWindows Start Menu folder where you started Forms Builder. There should be amemu item called: Start OC4J Instance. Run that program (a DOS box will appear)and make sure it looks similar to the example above. If the OC4J Instance giveserrors, it may be because your computer has a personal firewall (such as that included in Windows XP Service Pack 2). Make certain you configure your personal firewall to allowlocal connections to port 8888.


If you have previously not installed the latest JInitiator,your browser will launch with the following message:

Click on the "Get the Plug-in" button and follow the instructionsto install it. Note that the jinit.exe file is actually installedwith Oracle9i Developer Suite.

Reminder: If you saved your form in a folder that has spaces in the name, or if your file name has spaces, the form will not loadin the web browser. In such cases, you may see error: FRM-90928 Positional parameter after key on command line.If this occurs, simply use the "Save As" item on the File menuto save the form under a new name with no spaces. Then tryto run the form again.

Technical Notes and Possible Errors:

  • The height and width settings for the Java appletthat displays the forms are set in the formsweb.cfg file located in \forms90\server directory under the Oracle9iDS installation home.Edit the formsweb.cfg file with a text editor and scrolldown to Section 3) Values for the Forms applet parameters:
    Change the width= and height= parameters accordingly and save the file.You amy need to restart the OC4J server.
  • Error FRM-18125: Could not find the web browserappears if you do not have a web browser installed or your web browseris not found in the PATH. If you are certain you havea web browser installed, set its location in the Preferences sectionin the Forms builder (Edit -> Preferences -> Runtime tab).
  • Error FRM-10142: The HTTP listener is not running on...appears if the OC4J is not running. Start the OC4J Instance.

6.4.4 Query By Example

When a Developer Form is first executed, no records will appear.That is, the blank form will appear with no data in the fields.This is similar to running a word processor where the opening screen is blank. From this blank or empty form, the user can immediately begin to enter new data records by simply typing them in.

However, in many cases, the user would first like tosee if there are any records in the database. Todo this, the user must query the databaseusing the query capabilities that are built intoevery Oracle form. This will bring a copy of therecords from the database into the form.

When a tool such as SQL*Plus is used, the user must formthe SQL query by typing it directly in. In the case ofembedded SQL, the SQL statements are programmed into thehost language (such as "C" or Java) and automatically executed for the user. In the case of Oracle Forms, complex queries can be formulated and submitted to the database using a technique called Query By Exampleor QBE. In QBE, the name of the table or tables is fixedas are the names of the columns that will be returned fromthe tables. The user can supply their own criteria by supplyingvalues for the fields. These values become part of the querythat is submitted to the database on behalf of the user.

Querying a form in Oracle Forms is a twostep process. Clicking on the Query buttonor choosing Enter Query from the Query menu places the form inEnter Query mode. In enter query mode,the form is cleared and the user can navigatein the various fields. Example values can be suppliedas criteria for the query. For example, a user lookingfor all employees in the RESEARCH department mighttype a "5" in the DNO field while in enter query mode.

Clicking on the Query button a second time (or pullingdown the Query menu and choosing Execute Query) executesthe current query using any supplied data ascriteria in a WHERE clause. If no criteria aresupplied, then all records in the table willbe displayed. Continuing the above example,supplying a "5" for the DNO field while inenter query mode would cause the followingSQL query to be submitted to the database:

SELECT fname, lname, ssn, bdate, salary, dno
FROM   employee
WHERE  dno = 5;

Most of the commands on the menus and onthe button bar can also be activated usingspecial key combinations or function keys.For example, within the Java based forms displaypressing the F11 function keyplaces the form in Enter Query mode. Pressingthe CTRL+F11 function key executes aquery. (Note: Please check the Help menu to see ifthe keys in your system are the same).To see a list of keys and their functions,pull down the Help menu andchoose the Keys option.

Once data has been queried, it is retrieved from thedatabase and stored in a buffer in the associated datablock on the form. This means that a local copy of thedata now exists in the client's memory. Appropriatelocks are placed on the data so that the client maintainsa consistent view of the data in the associated tables.

The user can navigate through the data in a block bypressing the up and down arrow keysto scroll between records. To change data ina 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 thelast record and then down once more to moveto a blank record. Data can then be typed into the fieldsand the TAB key can be pressed to move between fieldson the form.

To save both changed and new records on a form, pulldown the Action menu and choose theSave menu item.Any records that have been changed are collected andsubmitted to the database as SQL UPDATE statements.Any new records are collected and submitted to thedatabase as INSERT statements. These statements aresubmitted as a single logical unit of work. Thus ifa failure occurs during any of the statements, theentire transaction will be rolled back.

If the user attempts to clear the block or exit the form and there are some changes that have not yet been applied to the database, they will be prompted to commit the changes before exiting or clearing the form. The block is clearedwhen the Enter Query mode is invoked. A block can also be cleared using the Clear Block and Clear Formmenu items.

To exit from a running form, pull downthe Action menu and selectthe Exit menu item. Warning: Do not simply closethe web browser as this will leave a Java Applet process runningand this will lock your form (.fmx file).

As a review, here are a few basic functions that canbe performed by default in any Form:

Forms function Notes
Querying a form To see all records in the table, simply "Execute Query" (via Query menu or by pressing F8 in Windows).
To filter the record, "enter query" mode, specify the queryparameters in the fields and then "Execute Query".
Inserting new records Scroll to the last record and then scroll once more toget a blank record. Type in data for each field and then committhese changes by pulling down the Action menu and choose theSave menu item.
For master/detail forms (discussed below) first either querya master record or insert and save a master record (in themaster data block), then navigate to the detail block andinsert new records there.
Updating existing records Query the database and scroll to the record of interest.Change the values in the fields and then save the changes.
For master/detail forms, changes to the key in themaster block may be prevented if detail records exist(thus insuring relational integrity).
Deleting records Query the database and scroll to the record ofinterest. Pull down the Records menu and choose Delete record.Then save the changes.
For master/detail forms, attempting to delete a master recordwhen detail records exist typically results in an error.In such cases, all of the detail records should be deletedfirst and then the master record should be able to be deleted.
Exiting the Form Pull down the Action menu and choose theExit menu item. Existing in this way willrelease any Java processes that might lock up yourforms files.

6.5 Exercise: Creating a a Single Block Form

For this exercise, create a simple data entry formfor the DEPARTMENT table with the followingcharacteristics:

  • Select and display all of the columns in the DEPARTMENT table.
  • In the layout wizard, use the Tabular layout and display 5 Recordsin the form. Add a scrollbar.
  • In the Layout Editor, make the DNAME field a bit widerto accommodate longer department names.
  • Change the Properties of the DEPARTMENT block so thatthat data is ORDER BY DNAME.
  • Save the form under the file name: depart.fmb

The resulting form should look like the following:

[Exercise result form]

Use the enter query mode and execute query functionsto query the department table for a specific departmentname and/or number.


7. Creating a Master-Detail Form

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

7.1 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 recordreferences one or more detail records in anothertable. A record in the detail table will relate to exactlyone master record in the master table. Another name for thisrelationship 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 Inventory Items.

Oracle Forms implements the master-detail relationship usingtwo data blocks. The first block corresponds to the master tableand 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 displayonly records that are associated with the current record in the master block.

Note that a Master-Detail form is simply one way of viewingthe data in two related tables. Forms do not affect theschema in terms of creating, dropping or enforcing database levelreferential integrity constraints.

7.2 Steps to Create a Master-Detail Form

In this section, a set of step by step instructions forcreating 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 EMPLOYEEtable gets its values from the DNUMBER column inthe 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.

7.2.1 Create the Master Block

In the Object Navigator, click on the Forms branch at the very top.Create a new form by pulling down theFile menu and choosing theNew menu item. Then chooseForm from the flyout menu.

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

  1. Pull down the Tools menu and choose the Data Block wizard.
  2. Create a data block for a table/view.
  3. Specify the DEPARTMENT table and select all of thecolumns (DNAME, DNUMBER, MGRSSN and MGRSARTDATE).
  4. Create the data block and then go on to theLayout wizard.
  5. Apply the Department data block to a new canvas.
  6. Add all of the columns as Displayed Items.
  7. Change the labels to:
    Dept. Name
    Dept. Number
    Mgr. Ssn
    Mgr. Start Date
  8. Choose a Form layout.
  9. Specify a frame title of "Departments" andselect only 1 record to be displayed.
  10. Save the form as deptemp.fmb and then compileand run it to make sure it is working properly.
  11. Use the QBE features to retrieve only those departmentswith DNUMBER greater than 2. Then, do another QBEquery to retrieve only those departments with theletter H in their name (try %H%).

After this first step, the deptemp formshould look like the following:


7.2.2 Create the Detail Block

Now that we have the master block DEPARTMENT created, we cannow create the detail block EMPLOYEE and associate it with the master block. Perform the following steps:
  1. Return to the Object Navigator (pull down the Tools menu and chooseObject Navigator).
  2. In the Object Navigator, click on the Data Blocks branch of the DEPTEMP form (do not click on the department data block, however).
  3. Pull down the Tools menu and choose the Data Block wizard.
    Note: If the DEPARTMENT data block (or any of its items) is still selected, activating the Data Block wizard will cause theexisting block to be edited instead of creating a new block(which is what is required in this part of the tutorial).
  4. Select the EMPLOYEE table and include the FNAME, LNAME, SSN, BDATE, SALARY and DNO columns.

  5. Because at least one data block already exists inthe form, the next step in the wizard will be to createa relationship between the existing data block (DEPARTMENTin this case) and the new block being created.

    The wizard can construct the relationship based ontable level constraints it learns from the database schema.For example, in the CREATE TABLE and ALTER TABLE statementsgiven at the start of this tutorial, foreign key constraintswere specified between DEPARTMENT and EMPLOYEE, and betweenEMPLOYEE and DEPENDENT. However, such relationships are notalways implemented in table level constraints.
    The developer can also specify the relationship manually.In this case, the relationship will be specified manually.

    De-select the Auto-join data blocks option.
    Click on the Create Relationship buttonto list the available data blocks.
    In the next dialog box Relation Type, choose Based on a join condition and click the OKbutton.

    When the list of blocks appears, choose theDEPARTMENT data block.
    Arrange the Detail Item (DNO) and Master Item (DNUMBER) such as thatthe join condition becomes:EMPLOYEE.DNO = DEPARTMENT.DNUMBER

  6. Name the data block EMPLOYEE.
  7. Create the data block and then call the Layoutwizard.
  8. Be sure to choose the existing canvas (CANVAS4 inthis example) and include all of the itemsexcept the DNO as displayed.
    The DNO column (item) will still be a part ofthe EMPLOYEE data block, however, it will notbe displayed to the user.

  9. Touch up the labels for the fields andchoose the Tabular layout.
  10. Give the Frame Title as "Employees" and select5 Records displayed with 0 distance betweenrecords.
  11. Save the form (it should already have the namedeptemp.fmb) and then compileand run it. Note that after compilation, anyerrors encountered will be displayed.
The following figure shows the master-detail form running:

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

To navigate between the Master and Detail blocks, use:

  • To go to the next block: Press CTRL-PageDownor pull down the Block menu and chooseNext
  • To go to the previous block: Press CTRL-PageUpor pull down the Block menu and choosePrevious

7.3 Relation Properties of a Master-Detail Form

There are a number of properties in a master-detailform that can be changed to suit particularbehavior of the form. In the figure below,the Object Navigator has several new objectson it including Relations.

To view the properties for the DEPARTMENT_EMPLOYEErelation, open up the DEPARTMENT block and thenopen the Relations block by clicking on the+ symbols. Then click on theDEPARTMENT_EMPLOYEE relation with the right mousebutton and select Properties.

There are several interesting properties in therelations property sheet:

  • Name - The name of the Relation. This is typicallymade up of the names of the blocks.
  • Relation Type - The type of the relation: Join or Ref.
    A Join relation uses the typical SQL join (in the Where clause)to bring the two tables (data blocks) together. The Refrelation type is used for abstract data types and object references.
  • Detail Data Block - The name of the detail data blockspecified when the detail data block was created.
  • Join Condition - This is the join conditionin effect for queries to the database. This was specifiedwhen the detail data block was created.
  • Delete Record Behavior - Used to specify how thedeletion of a record in the master blockaffects 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 recordsshould be queried when a master record is queried.
    • Yes: Form does not query the detail recordsuntil the user navigates to the detail block.
    • No: Detail records are fetched immediatelywhen a user queries the master record.
    Deferred is sometimes set to Yes in cases where thereare a lot of detail records for each master record. In such cases, a lot of data must be queried and delivered to theclient each time a new record is displayed in the master block.When Deferred is set to Yes, the user can scroll down to themaster record of interest and then navigate to the detail block(CTRL-PageDown) to query the related detail records.
  • Coordination - Auto-query - Applied to deferred queries only
    • Yes: the query is automatically executed when the user navigates to the detail block.
    • No: the query must be executed manually by the user after they navigate to the detail block.
  • Prevent Masterless operation - Specifies whetherusers are allowed to query or insert records in adetail block when no master record is in place.
    • Yes: Users may not query or insert when nomaster record is in place.
    • No: Users may query or insert when nomaster record is in place.

These settings are used to "tune" the overall performance of a master-detail form. As mentioned above, in cases where a largenumber of detail records are associated with each master record,it is a good idea to set coordination-Deferred to Yes to avoidunnecessary transfers of data between the server and client.This will also speed up the display of master records as theuser can freely scroll through them without a pause to query and deliver the detail records.

7.4 Program Units in a Master-Detail Form

When a Master-detail form is created, three basicProgram Units (PL/SQL Procedures) are created bydefault. These procedures are used to coordinate the query and navigation of the detail records when the masterrecord is changed (e.g., when the user scrolls to a differentmaster record), updated or deleted.The procedures can be seen listed in the Object Navigator:

PL/SQL is Oracle's procedural language extensionsto SQL. To view the PL/SQL code for a ProgramUnit, click on the name of the program unit with theright mouse button and then choose PL/SQL Editorfrom the pop-up menu. The code for the CHECK_PACKAGE_FAILUREprocedure is show here:

The CHECK_PACKAGE_FAILURE procedure checks tosee if a prior form command or SQL statementwas successful or if it failed.

Below is the PL/SQL code for the QUERY_MASTER_DETAILSprocedure. The text after the -- (two minus signs) arecomments.

PROCEDURE Query_Master_Details(rel_id Relation,detail CHAR) ISoldmsg CHAR(2); -- Old Message Level Settingreldef CHAR(5); -- Relation Deferred SettingBEGIN---- 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' THENGo_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;EXCEPTIONWHEN Form_Trigger_Failure THEN:System.Message_Level := oldmsg;RAISE;END Query_Master_Details;

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

Finally, the CLEAR_MASTER_DETAILS procedure is called each timea new master record is retrieved (e.g., when the user scrolls to a different master record) or deleted. In this case,if any of the detail records have been inserted, updated or deleted,the changes must be saved into the database before the detail block can be cleared. If there are any outstanding changes to be saved, the user will be prompted to save those changes before the detailbock is cleared.

7.5 Extending Master-Detail Forms

Master-Detail forms can be extended to includeadditional levels of details. For example,consider a Customer listing with detail onOrders a customer has placed. For each Order,there are then many Items. An extension tothe above example would be to include theDEPENDENTS of the employees as a detailsof the EMPLOYEES block. The stepsoutlined above can be repeated allowing severallevels of detail to be added. Each additional levelof detail will add a new block that is relatedto the level above it.

7.6 Exercise: Creating a Master/Detail Form

For this exercise, create a Master/Detail form usingthe EMPLOYEE and DEPENDENT tables. Employee will be theMaster block and Dependents will be the Detail block.The join condition between the two tables is wherethe SSN column of EMPLOYEE is equal to the ESSNcolumn of DEPENDENT.

The following is an outline of the steps:

  1. Create the Employee data block and include theFNAME, LNAME, SSN, ADDRESS and SEX fields onthe form. Use the Form layout and only display one record at a time.
  2. Go back to the Object Navigator andselect the "Data Blocks" tree. Make surethe EMPLOYEE block is not selected andrun the Data Block Wizard again.
  3. Create the Dependents data block and includeall of the columns.
    For the master/Detail relationship, de-select the "auto" option, use a Join relationship, choose theEMPLOYEE data block as the Master and set DEPENDENT.ESSN = EMPLOYEE.SSN forthe join relationship.
  4. In the Layout Wizard for the Dependents data block,display all of the fields except the ESSN field.The ESSN field will remain part of the data block but itwill not be visible on the form.Choose a Tabular style and display 4 up to records at oncewith 0 space between each record.
  5. Save the form using the name: empdepn.fmb

The completed form should look like the following:

Query the master block and practice navigating between thetwo blocks using the CTRL-PageDown and CTRL-PageUp keys.

While in the Employee block, scroll to the Employee named JENNIFER WALLACE, navigate to the Dependents block and add a new record for a SON named ANDREW born on 20-OCT-1962. Navigate back to the Employee block and attempt to scroll toanother employee record. A prompt should be displayed to savethe new Dependent record.

While in the Master block for Employees, scroll to anemployee who has some dependents. Try to delete theEmployee record and see if an error message appears.

Finally, alter the EMPLOYEE_DEPENDENT relation properties and set the Coordination-Deferred to Yes and then run theform. Notice now that the detail records will not bedisplayed automatically. Navigate to the Dependents blockand hit the Execute query button (or pull down the Querymenu and choose Execute). Notice that only thoseDependent records matching the Employee are displayed.


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

It is cumbersome for a user to have to remembervarious codes and numbers while entering datainto a data entry form. For example, in theEmployee form, it might be difficult to have allof the department numbers memorized when insertingnew employee data.

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

To prepare for this part of the tutorial, closeany forms you currently have open by pulling downthe File menu and selecting the Close menu item.

Once all of the forms are closed, pull down theFile menu and choose the Openmenu item to open up the employee.fmbform that was created previously.

A List of Values is based on a Record Group.In Oracle Forms, a record group is a query that returnssome collection of records. Record groups can be usedto populate blocks or LOVs and they can beused in procedures. When the user navigates to anitem with an LOV attached to it, the LOV key (F9 inMS Windows) can be pressed to call up the LOV.At that time, the query associated with the recordgroup is executed and the results are displayed ina pop up window. Once the user makes a selection fromthe list, the value or values are returned to the form and placed in the appropriate fields.

In the example below, a recordgroup and LOV for the DNO (department number) fieldin the Employee table will be created.

8.1 Creating a List of Values

Creating a List of Values requires 4 basic steps.

8.1.1 Create a new LOV Object

To create an LOV, click on the LOVs node in theObject Navigator. Then pull down the Editmenu and choose the Create menu item.A dialog box will appear asking if you wouldlike to create the LOV manually or if you would liketo use the LOV Wizard.

Choose "Use the LOV Wizard" and click the OK button.The first step of the LOV Wizard will appear as below:

The default is set to create a new Record Group based on a query.Make sure this selection is highlighted and then clickthe Next button.

In most cases, you will not have a record groupcreated previously. However, one can be createdon 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 Next button.

The next step is to specify which columns in the record groupwill be returned to for use by the LOV. In this example, we onlyreturn DNUMBER so select that column as shown below and click Next.

The next step is to specify the display properties of theLOV columns and also to map the LOV item to the field on the form.

To map the LOV column to a field, click on the DNUMBER entry and thenclick on the "Look up return Item" button. Highlight the EMPLOYEE.DNOfield and click on the OK button.

Note that if your LOV contains more than one item, at this timeyou would also map them as well (this is not the case with thisexample, but in the next example we will need to map two LOV columns).Click on the Next button.

Specify a title for LOV window (e.g., "List of Department Numbers").At this point you may alsospecify the size and positioning of the LOV. For this example,leave them as the defaults and click the Next button.

The advanced properties relate to how the records shouldbe fetched for the LOV. If there are many hundreds or thousands ofpossible records returned by the LOV, you may consider changingthese options. Otherwise, for this example, simply click Next.

In this final step, the LOV is returning some columns and they wereassigned to fields on the form in a previous step. At this point,we need to associate the LOV with a particular item on the form.For example, by assigning the LOV to the EMPLOYEE.DNO field, theuser will be able to call up the LOV only when the cursor is positioned in the EMPLOYEE.DNO field. Choose EMPLOYEE.DNO and click the Next button.

Finally, click Finish to create theRecord Group and the LOV.

In the figure below, the LOV has been created. A default name ofLOV6 was given to both the LOV and to its associated record group.Depending on what other parts of this tutorial you have completed,the default name may be slightly different.

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 isavailable for this field.

Under UNIX and under the Java based applet, the key to display the list of values is Control-l(hold down the control key (Ctrl) and press the letter L).To see the appropriate keys to press, pull down theHelp menu and choose the Keys item.

Below is an example of the list of values for the DNO item onthe Employee form.

8.2 Non-Base Table fields

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

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

Adding this item and making it functionalwill take several steps:

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

8.2.1 Adding a New Item to an Existing Data Block

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

  • Display the properties for the new Text Item by doubleclicking on it. Change its name to DEPTNAME. Change the following propertiesfor DEPTNAME:

    Enabled: Yes
    Keyboard Navigable: No
    Database Item: No
    Query Only: No
    Primary Key: No
    Insert Allowed: No
    Query Allowed: No
    Update Allowed: No


  • By setting Keyboard Navigable to No, we prevent the user from tabbing into thisfield. Since this field is not used fordata entry or querying, this prevents thepotential confusion.
  • The Database Item property is set to No which indicates that this filed does not map toan actual column in the EMPLOYEE table.
  • The remaining properties are all set to Noto keep the field from participating in any of these activities: Delete, Insert, Update and Query.

At this point, the new item DEPTNAME has been added tothe EMPLOYEE data block as a non-base table field.

Save the form at this point by pulling down theFile menu and choosing the Save option.

8.2.2 Creating a Trigger

Next, we will create a trigger to populate the DEPTNAMEfield whenever the value of DNO changes. Triggers in Oracle Formshave 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 DEPTNAMEfield with the appropriate department name just after theDNO field changes on the EMPLOYEE block. Thus we will usea POST-CHANGE trigger on the :EMPLOYEE.DNO field.
  • Switch to the Object Navigator view byclicking on the Tools menu and choosingthe Object Navigator menu item.
  • Open up the EMPLOYEE data block and the DNO item.
  • Click on the Triggers property andcreate a new trigger by pulling down theEdit menu and choosing theCreate menu item.
  • A list of possible trigger names appears.Choose the POST-CHANGE trigger andclick on the OK button.

  • When the PL/SQL editor appears, type the followingPL/SQL code:-- Populate the non-base table field :EMPLOYEE.DEPTNAME-- using a value from the DEPARTMENT table.BEGINSELECT DNAMEINTO :EMPLOYEE.DEPTNAMEFROM DEPARTMENTWHERE DEPARTMENT.DNUMBER = :EMPLOYEE.DNO;END;Items on a block are preceded by a full colon todifferentiate them from columns in a table.
  • Compile the trigger by clicking on the Compile button (this icon is in the upper left corner of the PL/SQL editor window).

  • Close the PL/SQL editor by clicking on the Close button.

Save, compile/build and run the form to test the functionality.Notice that when the EMPLOYEE block is queried, the associateddepartment name now appears in the new DEPTNAME field. Each timethe DNO field is changed, the POST-CHANGE trigger will fireand will fetch the appropriate DNAME column from the DEPARTMENTtable and place it in the :DEPTNAME field on the form.

8.2.3 Create a new List of Values

Finally, a new list of values should be created todisplay both the department number and name whenthe user calls up the LOV.

If you completed the previous section on creating the simple LOV, use the Object Navigator to delete this LOV before proceeding withthis section.

  • From the Object Navigator, click on the LOVsfor the EMPLOYEE form. Pull down theEdit menu and choose theCreate menu item. Choose the LOV Wizard.
  • Type the following SQL statement into theQuery Text field:
    SELECT DEPARTMENT.DNUMBER, DEPARTMENT.DNAME FROM DEPARTMENT
    
    Then click on the Next button.

    Include both the DNUMBER and DNAME columns from the Record Groupin the LOV.
    Map the DNUMBER LOV column to the :EMPLOYEE.DNO text item andmap the DNAME LOV column to the :EMPLOYEE.DEPTNAME text item (createdin the previous section).

  • Give a title for your list of values and return both DNUMBER and DNAME from the LOV.
  • Finally, Save, Compile and Run the form. Note thatdisplaying the LOV for the DNO item now causesboth the department number and the name to appear.

8.3 Exercise: Creating an LOV and Non-Base table field

For this exercise, create a new, single data blockform based on all of the columns in the DEPARTMENT table.Then add a non-base table field for the last name of thedepartment manager and create an LOV to populate it:
  • Create a new form called departmg.fmbthat includes all of the columns in the DEPARTMENT table. In the layout wizard, use the Tabular layout and display 5 Recordsin the form. Add a scrollbar.
  • In the Layout Editor, create a new text itemcalled MGR_LAST_NAME in the DEPARTMENT data block. Be sure the DEPARTMENT block is selected when the new item is created. Change the properties ofMGR_LAST_NAME so it is not a Database item.
    Add a text label to this new MGR_LAST_NAME field.

  • Create a list of values called LOV_MGR based upon the query:
    SELECT ssn, lname  FROM  employee
    
    Map the employee.ssn column to the :DEPARTMENT.MGRSSN field.
    Map the employee.lname column to the :DEPARTMENT.MGR_LAST_NAME field.

  • Create a POST-CHANGE trigger on the MGRSSN itemso that when it changes, the followingcode will execute:
    SELECT lname
    INTO   :DEPARTMENT.MGR_LAST_NAME
    FROM   employee
    WHERE  employee.ssn = :DEPARTMENT.MGRSSN;
    
    This will automatically populate the non base table field MGR_LAST_NAMEon the form when the form is queried.

The form should look like the following:

[Example: List of Values]


9. 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 Unitscan be used to add functionality that is not possible throughStructured Query Language (SQL) or through the OracleForms interface. Someexamples of Program Units were given in the discussion onProgram Units in a Master-Detail Form.By storing a Program Unit within a form, many blocksin the form can take advantage of the same procedurewhich reduces code duplication and improves themaintainability of the code.

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

Oracle Forms can make calls to both internal Program Unitsas well as to Stored Program Units. This flexibilityallows application designers extensive control overthe execution of applications and facilitates performancetuning.

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

9.1 Creating Program Units in Oracle Forms

In this section, we will augment the EMPLOYEE formby adding a count of other members of a givenemployee'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 dataand then write a Trigger that will call theProcedure each time a new Employee record isqueried.

9.1.1 Add a new item to the EMPLOYEE Block

Add a new item on the EMPLOYEE block to storethe count of other employees in the department.See the section onAdding a New Item to an Existing Blockfor details.

Call this field OTHER_MEMBERS. Be sure it isa non-database field and that a user willnot be able to navigate there, insert, updateor delete data in the field. Go to theProperties and check each of them:

Enabled: True
Navigable: No
Database Item: No
Query Only: No
Primary Key: No
Insert Allowed: No
Query Allowed: No
Update Allowed: No


Add a label to this field by switching to theLayout Editor, choosing the text tool andclicking next to the OTHER_MEMBERS field.

9.1.2 Create a Procedure to Count Other Department Members

In this step, create a Program Unit (procedure)in Oracle Forms tocount the number of other members in the same department.For this procedure, we will pass in a parameter calledIN_EMPLOYEE_SSN that will contain an employee'ssocial security number. The procedure will return avalue in the parameter OUT_COUNT_OTHER_MEMBERSthat will contain the number of other employeeswho are in the same department.

To create this procedure, switch to the Object Navigatorand scroll down to the Program Units branch. Pull downthe Edit menu and choose the Createmenu item. A dialog box will appear asking you to namethe new procedure. Type in the name OTHER_DEPARTMENT_MEMBERSand click on the OK button.

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

-- 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) ISTEMP_COUNT NUMBER; -- Placeholder for countBEGIN 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.Any lines starting with -- arecomments and are ignored by Oracle forms.

The heading for the procedure definition indicatesthat one parameter, IN_EMPLOYEE_SSN, is a NUMBER datatype and the procedure should expect a value to be passedin. The other parameter, OUT_COUNT_OTHER_MEMBERS, is alsoa NUMBER data type and a value will be passed back to thecalling program through it.

To finish off the procedure header, the TEMP_COUNTvariable is declared for use within thisprocedure only.

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

The result in TEMP_COUNT is checked to see if itis a NULL value. If TEMP_COUNT is not NULL andit is not 0, then OUT_COUNT_OTHER_MEMBERS isset to equal TEMP_COUNT-1. OtherwiseOUT_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 byclicking on the Compile button. Any errorsin the procedure will be highlighted. Some commonproblems include forgetting to use := in an assignment statement (like the Pascal language)and leaving off the END IF to finish upIF ... THEN statements.

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

9.1.3 Create a Trigger to Call the Procedure

Create a trigger to call the OTHER_DEPARTMENT_MEMBERSprocedure. In the Object Navigator, open up theEMPLOYEE block and the SSN item. Highlight theTrigger branch for the SSN item, pull downthe Edit menu and choose the Createmenu item.

A pop up list of trigger names will appear. ChoosePOST-CHANGE and click on the OK button to open thePL/SQL editor for this new trigger. Type in the followingcode for the POST-CHANGE trigger on theSSN item:

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;

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

9.1.4 Save, Compile/Build and Run the Form

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

To save you work, use the Save As menuitem on the File menu and save thisform under the name: emp_memb.fmb

Save, Compile/Build, and Run the form to check itsfunctionality. Note that when scrollingto a new employee's record, the OTHER_MEMBERSfield is automatically populated with valuesindicating the number of other employeesin the same department.

9.2 Creating Stored Procedures in SQL*Plus

In the previous example, we added a procedure(Program Unit) in Oracle Forms to perform a queryon the database. The procedure is stored as partof the form's source code and is compiled when the form is built. Upon examination, each call of thisprocedure produces:
  1. A local call from the POST-CHANGE trigger to the procedure passing an employee's SSN.
  2. A remote access from the procedure to the databasepassing a SQL query.
  3. A remote return from the database containing acount.
  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 bythe network traffic required to go back and forthbetween client and server (steps 2 and 3).The lowest costs areincurred by the local calls between trigger andprocedure and the local processing (steps 1, 4 and 5).

In this example, the SQL Query in steps 2 and 3 doesnot return a significant amount of data. However, there aresituations where, if a large amount of data were to bereturned for each query, such queries would severelyimpact performance.

One solution to this problem is to move the queries andas much processing as possible "closer" to the data inthe database. Thus our example for this section will beto implement the OTHER_DEPARTMENT_MEMBERS procedurein the Oracle database. To achieve this, we willneed to use Oracle SQL*Plus (a command-line tool) to create a stored procedure.

Log in to SQL*Plus and enter the followingCREATE PROCEDURE statement. Alternatively,save this statement in a text file andexecute it using the START commandin SQL*Plus.

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 countBEGIN 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 toRUN the command in the SQL*Plus bufferby typing RUN or by typing the forwardslash /

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 youthe authorization to create stored procedures.Please contact your DBA to obtain thisauthorization.

To see if the procedure compiled correctly, look inview USER_ERRORS as follows:

SQL> SELECT * FROM user_errors;no rows selectedIf the message no rows selected appears,then no errors were found in the trigger.

Another alternative is to use the SQL*Plus commandcalled show errors. After submitting atrigger or a stored procedure, any errors found duringcompilation can be displayed using the show errorscommand.

In some versions of SQL*Plus, the show errorscommand will not work due to a lack of buffer memory on theclient computer. An error message may appear as in thefollowing example:

SQL> CREATE PROCEDURE test AS2 BEGIN3 SELECT SYSDATE INTO :temp FROM dual;4 END;5 /Warning: Procedure created with compilation errors.SQL> show errorsbuffer overflow. Use SET command to reduce ARRAYSIZE or increase MAXDATA.No errors.

If the buffer overflow message appears, set theARRAYSIZE variable to a lower number such as 2 with the followingcommand:

SQL> SET ARRAYSIZE 2

Then use the show errors command as described:

SQL> show errorsErrors for PROCEDURE TEST:LINE/COL ERROR-------- --------------------------------------------3/23 PLS-00049: bad bind variable 'TEMP'

To see what stored procedures you have created,query the USER_SOURCE view in the data dictionary.

The only major difference between the storedprocedure created in the database schema throughSQL*Plus and the procedure created in Oracle Formsis the way the header is formatted. Creatinga 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 inOracle 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 asan object in your schema and will remain thereuntil a DROP PROCEDURE OTHER_DEPARTMENT_MEMBERSstatement is issued. Any Oracle Form, Report or otherprocedure can make calls to OTHER_DEPARTMENT_MEMBERSas if it were a local procedure.

To see the stored procedure in action, open theemp_memb.fmb form and remove (delete) theProgram Unit (procedure) OTHER_DEPARTMENT_MEMBERSfrom it. Save the form, compile, generate and run it.

The POST-CHANGE trigger on :EMPLOYEE.SSN will automaticallymake a call to the stored procedure version ofOTHER_DEPARTMENT_MEMBERS located in the schema.

To view the available Stored Program Units fromwithin Oracle Forms, switch to the Object Navigatorand 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. A listof your stored procedure should then appear.

9.3 Additional Stored Procedures and Triggers

There are countless ways in which stored procedures andtriggers can be used to enforce business rules andto process data in an application. In this section, afew of the most commonly used triggers and proceduresare described.

9.3.1 Generating unique or consecutive identifiers using the table

Many of the keys used in tables are based on a number sequence.For example, in our DEPARTMENT table, the DNUMBER is an integerthat should increase for each new department. To automaticallygenerate a new DNUMBER, create a query to capture the largest current DNUMBER value and add 1 to it. Then assign this newvalue to the DNUMBER item in the DEPARTMENT data block. A triggerto do this could be created at the block level and should executebefore any new record is inserted into the database. A PRE-INSERT trigger on the DEPARTMENT data block would be used for this example:

BEGIN SELECT MAX( dnumber ) + 1 INTO :department.dnumber FROM department;END;A PRE-INSERT trigger executes just before the INSERT statementfor a new record is sent to the database.

9.3.2 Generating unique or consecutive identifiers using an Oracle Sequence

One problem with the above method is that many usersworking on the same form to insert new records willrun into concurrency problems. For each new recordto be inserted, a full table scan must be done to findthe largest identifier.

One solution to this problem is to use an Oracle Sequence.An Oracle Sequence is a schema object that keeps track ofa simple integer and automatically increments this counterwhen it is accessed.

For example, in our DEPARTMENT table, the DNUMBER is an integerthat should increase for each new department. To automaticallygenerate a new DNUMBER, create an Oracle Sequence using theSQL*Plus tool:

CREATE SEQUENCE department_seqINCREMENT BY 1START WITH 1MAXVALUE 99999NOCYCLE;The above department_seq sequence will begincounting from 1 and increment by 1 until it reaches99999. After that, accessing the sequence will producean error. This is reasonable since it is highlyunlikely an organization will ever have more than 99999departments.

The next step is to assign the next value of the sequenceto the DNUMBER item in the DEPARTMENT data block. A triggerto do this would be created at the block level and should executebefore any new record is inserted into the database. A PRE-INSERT trigger on the DEPARTMENT data block would be used for this example:

BEGIN -- Get the next value for DNUMBER from the -- department_seq sequence. SELECT department_seq.nextval INTO :department.dnumber FROM dual;END;The dual table is a dummy table that containsexactly one record (row). We can use it in a SELECT...INTOstatement to consistently return a single value.The nextval accessed in the sequence willreturn the current sequence value and then automaticallyincrement it to the next value.

A PRE-INSERT trigger executes just before the INSERT statementfor a new record is sent to the database.

Oracle Sequences are not applicable for master-detail formssuch as purchase orders where an item number needs tobe assigned in the detail table. For example, purchaseorder items are typically numbered: 1, 2, 3....The next PO will also number the items 1, 2, 3, ....An Oracle Sequence is designed to deliver uniqueidentifiers for primary keys and should not be usedfor such item numbers.

9.3.3 Checking constraints at the client

In any database system, it is always desirable to constrainthe values certain columns can take on. It is possible to add constraints to the database tables themselves in order to enforceconstraints, however, the enforcement only takes place when atransaction reaches the database. In addition, many constraints on the data that involve values of data from other tables may notbe easily implemented as table constraints.

Many types of constraints on data can be checked in the clientusing triggers. For example, in an EMPLOYEE data entry form, we may want to enforce a constraint that no employee can actas their own supervisor. Thus for any record, SSN may not equalSUPERSSN. To implement such a constraint, add the SUPERSSN item tothe EMPLOYEE form (or simply create a new form from scratch using thewizard that includes both SSN and SUPERSSN columns) and then create a WHEN-VALIDATE-ITEMtrigger on the SUPERSSN item in the EMPLOYEE data block:

BEGIN IF (:SUPERSSN = :SSN) THEN MESSAGE('Employees may not supervise themselves!'); RAISE FORM_TRIGGER_FAILURE; END IF;END;The FORM_TRIGGER_FAILURE will cause the execution of theitem validation to be halted and the user will have to changethe SUPERSSN before moving on to another item.

9.3.4 Adding Alerts (Pop-up Messages)

In the previous example, the MESSAGE procedure is used to display theerror along the status bar at the bottom of the Forms window. A better approach may be to force the user's attention to theproblem. This can be accomplished by creating a pop-up dialog boxthat forces the user to read the message and click an OK button (or some other button). In Oracle Forms, this is called an Alert.

To set up an Alert:

  • Use the Object Navigator to display the Alerts area. Pull down the Navigator menu and choose Create. Click on the default name that is given for the new alert (something like ALERT4) and rename it: SUPERVISOR_ALERT
  • Bring up the Property Palette for this Alert and fill in the following properties:
    Title: Supervisor Alert Message
    Message: Employees may not supervise themselves!
    Alert Style: STOP
    Button 1 Label: OK
    Leave the Button 2 Label and the Button 3 Label blank
    Default Alert button: Button 1
    Leave all of the other properties with their defaults and close the Property palette.
  • Change the above WHEN-VALIDATE-ITEMtrigger on the SUPERSSN item in the EMPLOYEE data block to:DECLAREreturn_alert NUMBER; BEGIN IF (:SUPERSSN = :SSN) THEN return_alert := SHOW_ALERT ('SUPERVISOR_ALERT'); RAISE FORM_TRIGGER_FAILURE; END IF;END;The SHOW_ALERT procedure calls up the specified alert and obtains thereturn value (based on the button the user clicks on) to assign to a localvariable called return_alert.
Below is an example of the Alert in action:

In general, Alerts may provide several different buttons for the user to click on. For example, to confirm exiting a form, a POST-FORM triggermight invoke an "Are you sure you want to Exit" Alert with "Yes" and "No"buttons. Or, to confirm deleting a record, a PRE-COMMIT trigger mightinvoke an "Are you sure you want ot delete this Employee?" Alert with "Yes" and "No" buttons. Based on the return_alert value, either a commitor rollback might be issued.

9.3.5 Automatic Forms Query

As discussed earlier in the tutorial, by default, when a form runs, the underlying table data is not automatically queried and displayed.It is up to the user to execute a query, for example, by pulling down thequery menu and choosing Execute.

To automate this process, create a WHEN-NEW-FORM-INSTANCEtrigger that navigates to the master data block and then issuesthe EXECUTE_QUERY built-in procedure. For example, assuming the masterblock is named "EMPLOYEE", the following code will cause aquery to be executed immediately after the form opens:

BEGIN
  GO_BLOCK('EMPLOYEE');
  EXECUTE_QUERY;
END;

9.3.6 Populate items with default values

There are many cases where default values can be suggestedfor certain data items based upon values already supplied in other data items. The method here is to use a POST-ITEMor WHEN-VALIDATE-ITEM trigger to populate another field, onlyif the other field is currently blank. For example, to populatethe SUPERSSN data item of the EMPLOYEE block based upon the MGRSSN of the employee's department, the following WHEN-VALIDATE-ITEMtrigger code might be used on the DNO data item:

BEGIN IF :SUPERSSN IS NULL THEN SELECT department.mgrssn INTO :employee.superssn FROM department WHERE department.dnumber = :employee.dno; END IF;END;Note that the IF statement uses :SUPERSSN IS NULL to check to see if any value has been supplied for SUPERSSN. If nothing was typed into the SUPERSSN data item, then itsvalue is NULL. If the user types anything at all into theSUPERSSN data item, then the IF statement will evaluate to false and no value will be suggested.

9.3.7 Attaching functionality to command buttons

Command buttons can be added on a form to carry out functionssuch as exiting a form, committing/saving changed records and so on. The basic command button object contains properties forthe button Label (the text provided on the button itself). Additional properties include visual attributes including the ability to display a bitmap image in place of the button'snormal appearance.

Once a button has been physically placed on a form, functionalityis added but writing a WHEN-BUTTON-PRESSED trigger.As one might expect, this trigger executes when the user clickson the button. So, for example, to create a button to exit theform when the user clicks on it the WHEN-BUTTON-PRESSED trigger might look like:

DECLARE return_alert NUMBER; BEGIN return_alert := SHOW_ALERT ('EXIT_ALERT'); IF (return_alert = alert_buttin1) THEN EXIT_FORM; END IF;END;The above code assumes there is an Alert called EXIT_ALERT withtwo buttons: "Yes" and "No". If the user clicks on thefirst button ("Yes") then the EXIT_FORM procedure is called andthe form will exit (close). If the user clicks on any other button, then the Alert will close and the form will remain open.

9.3.8 Passing parameters between forms

There are many occasions that require the passing of data from one formto another. One such example is when we query a certain record on oneform and then wish to call up another form with the same record automatically retrieved. In general, data values can be passed betweenforms using GLOBAL variables (treat :GLOBAL as a block available in all forms) or by using Parameter lists. In the example, below, the parameter list features will be used to demonstrate how data can be passed fromone form to another.

For this example, we wil make use of two forms: A basic EMPLOYEE form withall fields and the EMPLOYEE/DEPENDENT master/detail form (created in aprevious exercise - the file name should have been empdepn.fmb). The idea is to place to button on the EMPLOYEE formthat when pressed, will call the EMPLOYEE/DEPENDENT form and automaticallyquery the dependents for the employee being viewed on the EMPLOYEEform. The main steps are:

  • On the EMPLOYEE form, create a button and label it "Query Dependents".
    Create a WHEN-BUTTON-PRESSED trigger with the following code (comments areenclosed in the code):DECLARE -- Declare pl_id as the identifier for the parameter list pl_id ParamList;BEGIN -- See if that parameter list already exists. If so, destroy it pl_id := Get_Parameter_List('tmpdata'); IF NOT Id_Null(pl_id) THEN Destroy_Parameter_List( pl_id ); END IF; -- Create a fresh parameter list */ pl_id := Create_Parameter_List('tmpdata'); -- Add a new parameter to this list called EMPLOYEESSN -- Assign this parameter the current value of the :SSN field Add_Parameter(pl_id, 'EMPLOYEESSN', TEXT_PARAMETER, :SSN); -- Now run a new form called 'empdepn' (must be in the same -- directory as the employee form we are now editing.) -- Pass along the identifier of our parameter list (pl_id) Run_Product(FORMS, 'empdepn', SYNCHRONOUS, RUNTIME, FILESYSTEM, pl_id, NULL); -- Note: In newer version of Forms, you may need to call -- formsid := FIND_FORM_OBJECT('empdepn'); -- view_forms := RUN_FORM_OBJECT(formsid, pl_id); -- or if running a Report from a form you may need to call: -- reportid := FIND_REPORT_OBJECT('myreport'); -- view_report := RUN_REPORT_OBJECT(reportid, pl_id); -- Note that 'myreport' must be created under the Reports entry in the -- Object Navigator in FormsEND;
    Note: In 9iDS, you may need to use either RUN_REPORT_OBJECTor RUN_FORM_OBJECT.
    Important note: You may also have to include the full path ofto the file name of the form you are launching with theRun_Product function call. For example:
     Run_Product(FORMS, 'd:\myfiles\empdepn', SYNCHRONOUS, RUNTIME,
         FILESYSTEM, pl_id, NULL);
    
    If you receive error FRM-40010 Cannot read form empdepn.fmxthen you will need to include the full directory path to whereyour forms are located.

  • Compile and save this form.
  • Now open up the empdepn (EMPLOYEE/DEPENDENT) form.
  • Using the Object Navigator, create a new PARAMETERcalled EMPLOYEESSN. This parameter will automaticallybe assigned a value based on the parameter list passedto the form by the above RUN_PRODUCT procedure call.
  • Create a new trigger called WHEN-NEW-FORM-INSTANCE. Thistrigger will execute whenever the form is first run.DECLARE-- Declare a forms block identifer blk_id Block;BEGIN -- Obtain the block ID of the EMPLOYEE block. This is the -- Master block in the empdepn master/detail form. blk_id := Find_Block('EMPLOYEE'); IF NOT Id_Null(blk_id) THEN -- Check to make sure our parameter has a value. If this form -- were executed by itself, then the parameter will be null. -- If this form is called from EMPLOYEE then the parameter will -- be passed along and assigned to :PARAMETER.employeessn IF (:PARAMETER.employeessn is not null) THEN -- Since we have a parameter, use it to alter the WHERE Clause -- property so that it becomes WHERE ssn=:PARAMETER.employeessn SET_BLOCK_PROPERTY(blk_id,DEFAULT_WHERE,'ssn=' || :PARAMETER.employeessn); -- Navigate to the EMPLOYEE block and execute a query automatically GO_BLOCK('EMPLOYEE'); EXECUTE_QUERY; END IF; END IF;END;
  • Compile and save the empdepn form.
  • Now run the EMPLOYEE form, query an employee record andclick on the "Query Dependents" button. The empdepn form shouldappear with the dependents automaticaly queried.

The above examples are just a few of the great many things one can dowith Oracle Forms. The on-line help that ships with Forms containsmany example forms and example pieces of code (see the Forms PL/SQL Reference) that go far beyond the simple techniques demonstratedabove.

9.4 Exercise: Procedures and Triggers

The following are some suggested exercises using various combinations oftriggers and procedures:
  • Use a simple Department data entry form and create an Oracle Sequence (using SQL*Plus) that will generatenew Department numbers for the DEPARTMENT table. Add therequisite PRE-INSERT trigger code to insert a unique DNUMBER each time a new department record is created.
  • Add a WHEN-NEW-FORM-INSTANCE trigger to the aboveDepartment form that will automatically query the DEPARTMENTtable when this form is opened.
  • Use a simple Employee data entry form and create an alert that displays a warning if an employee'ssalary is larger than their supervisor's. In a WHEN-VALIDATE-ITEMtrigger, run a query to check if the employee's salary is greaterthan their supervisor's and if so, show the alert.
  • Use the Employee/Dependent Master/Detail form you created inexercise 7.6 and put in some validation code that will check to make sure if aDEPENDENT is a SON or DAUGHTER, then the DEPENDENT's BDATE mustbe less than the EMPLOYEE's BDATE. If this is not the case,show an alert.
  • After you learn how to create Reports (see the next section) createa simple DEPARTMENT data entry form with a button that, when pressed,will run a report for that department (show all employees for example)by passing along the DNUMBER as a parameter.
    Note that you will need to use some code in your button similarto the following:
    DECLARE
       reportid       REPORT_OBJECT;
       view_report    VARCHAR2(100); 
       report_result  VARCHAR(30);
       rep_status     VARCHAR2(20);
       -- Declare pl_id as the identifier for the parameter list
       pl_id   ParamList;
    
    BEGIN   
      -- NOTE: In order to launch a report from Forms, you must have the Reports server 
      -- running on your local machine.
      -- At a DOS prompt type:  rwserver -install localhost autostart=yes
      -- This will install a reports server for your machine (localhost) and start it up.
      
      -- Next, create a Reports Object in Forms Object Navigator.
      -- This is named "MYREPORT" for this example.  This reports
      -- object will have properties including the path to the actual
      -- report file (.rdf file) you want to run.
    
      pl_id := Get_Parameter_List('tmpdata');
      IF NOT Id_Null(pl_id) THEN
        Destroy_Parameter_List( pl_id );
      END IF;
      -- Create a fresh parameter list 
      pl_id := Create_Parameter_List('tmpdata');
    
      -- Add a new parameter to this list called MYDNUMBER
      -- Note that in the report being called (empreport.rdf) the
      -- report query should use :MYDNUMBER in the WHERE clause
      --  as in    SELECT * FROM employee WHERE dno = :MYDNUMBER
      -- Assign this parameter the current value of the :DNUMBER field
      Add_Parameter(pl_id, 'MYDNUMBER', TEXT_PARAMETER, :DNUMBER);
    
      -- Now execute the report object.  This object must be created
      -- in the Object Navigator under REPORTS (just below "Record Groups")
      
      report_result := RUN_REPORT_OBJECT('MYREPORT', pl_id);
    
      -- Check on the report status and loop until it is done
      rep_status := REPORT_OBJECT_STATUS(report_result);
      WHILE rep_status in ('RUNNING','OPENING_REPORT','ENQUEUED') LOOP 
         rep_status := report_object_status(report_result); 
      END LOOP; 
      -- When the report is done, open up a new window and display it
      IF rep_status = 'FINISHED' THEN 
         WEB.SHOW_DOCUMENT('http://localhost:8888/reports/rwservlet/getjobid'|| substr(report_result,instr(report_result,'_',-1)+1)||'?'||'server=localhost','_blank'); 
      END IF;  
    
    END;
    
    


10. Oracle Reports Basics

We now turn our attention to another Developertool called Oracle Reports. The Oracle Reports Builderallows the developer to create sophisticated reports in avariety of layouts and contains many customization features.In this section, the basic steps for creating a simple reportand a Master-Detail report will be given.

10.1 Starting Developer tools under Windows 95/98/NT/2000/XP

A typical installation of Developer creates severalfolders under the Start Programs menu.

To run Oracle9i Reports Builder under Windows, click on theStart -> Programs -> Oracle9i Developer Suite - Home -> Reports Developer -> Reports Buildermenu item.

Under UNIX, run the rwbuilder.sh script.

An initial screen will appear:

Click on the Cancel button to proceed directly to theObject Navigator.

10.2 The Oracle Reports Object Navigator

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

The main sections of the Object Navigator are:

  1. Reports which include:
    • Data Model - Contains information about queries used for a report.
    • Web Source - A web page or web service that returns a data set.
    • Paper Layout - Contains information about how a paper or screen a report is formatted including headers, footers, margins, fonts, etc.
    • Paper Parameter Form - Contains information about the initial screen that is displayed when a form first runs.
    • Report Triggers - PL/SQL code that can be executed before, during or after a report has been executed.
    • Program Units
    • Attached Libraries
  2. Templates - Existing templates that can be used to create new reports.
  3. PL/SQL Libraries - Libraries of PL/SQL code.
  4. Debug Actions - Actions the debugger is scheduled to track.
  5. Stack - The current state of execution of a procedure.
  6. Built-in Packages - The built-in packages already in the database.
  7. Database Objects - Tables, sequences, views, etc.

As with Oracle Forms, the first step in using Oracle Reportsis to Connect to an Oracle server.This is accomplished by pulling down the File menu and selectingthe 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 inthe service name for your Oracle database.For example, if you are using Personal Oracle Lite, theDatabase: would be ODBC:POLITE. Contactyour system administrator to learn the service name for your Oracledatabase and provide that information in the Database: field.

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

10.3 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.

Oracle Reports version 3.0, 6i and 9iDS all support a wizard that takes thedeveloper through all of these steps. This approach willbe demonstrated next.

10.4 Creating a Single-Table Report

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

10.4.1 Specify the Data Model and Layout

The first step is to specify the data model andlayout of the report. We will use the Reportwizard to accomplish this. From theObject Navigator, pull down the Toolsmenu and choose Report wizard...

The first screen for the reports wizard will appear as below:

The first option is to decide the type of report to be created.Oracle Reports can be created to display on a web page (inside ofa web browser), or by using the more traditional Oracle Reports runtime. The latter is called the "Paper Layout". For this example,both types of layouts will be created. Make certain the "Create both Web and Paper Layout"option is selected and click the Next button.

The next step in the wizard prompts for the style of the report and for the Report Title.

The eight layout choices include:

  1. Tabular - Simple table with column headings at the top anddata records in consecutive rows below.
  2. Form - Column headings on the left hand side withdata values next to them on the right.
  3. Mailing Label - No column headings and records groupedinto repeating sections sized to print directly toa sheet of mailing labels.
  4. Form Letter - Arbitrary placement of data items withina text body.
  5. Group-Left - A Master/Detail/Detail style where the masterrecords are grouped on the left hand side with details tothe right.
  6. Group-Above - A Master/Detail/Detail style where themaster records appear above the detail records.
  7. Matrix - Column labels on both the left and the topwith data values in the middle. Similar to a spreadsheet.
  8. Matrix with Group - A combination of Master/Detail(Group-Above) and Matrix.

For this report, type in "Employee Report" as the title,choose the Tabular Layout andclick on the Next button.

The next step is to specify the Type of query thereport will be based on.

The five choices are:

  1. Express Server Query - a query based on a stored Oracle Express Server (OLAP)
  2. JDBC Query - A Query posed to a JDBC (Java DataBase Connectivity) Source
  3. SQL Query - A traditional SQL query to an Oracle database
  4. Text Query - A query posed against a text data source
  5. XML Query - A query posed against an XML data source (the Document Type Definition (DTD) of the XML data source is also required to use this type of query).

For this example, choose the "SQL Query" andclick the next button.

The next step is to specify the query that will formthe basis of the report. In this case, type the followingquery in the SQL Query Statement: text box:

SELECT FNAME, LNAME, SSN, SALARY, DNO
FROM EMPLOYEE ;

For future reference, one could also invoke the graphical Query Builderat this step, or choose to import a SQL query from a text file.

Once the SQL SELECT statement is entered, click on the Next button to move to the next step. Note: If you did not Connect to the database, you will be prompted for the Username, Password and Database.

In the next wizard dialog box, specify which columnsfrom the SQL Query will be displayed in the reportoutput. To specify all columns, click on the double rightarrow to bring all of the Available Fields to theDisplayed Fields side.

Click on the Next button to move to the next dialog box.

In the next step, aggregate functions can be applied to thefields on the report. For this example, we will displaythe average salary as well as a count of employees.Highlight the SALARY field and click on the Averagebutton. Then highlight the SSN field and click on theCount button.

The next dialog box allows you to change thefield labels and display widths for variousfields in the report.

As a final step, a design template can be applied to thereport. Design templates include specifications forfonts, colors and layout of the report. For this example,choose the "Blue" predefined template and then click on the Finish button.

After a short delay, the Reports Live Previewer will appearshowing the report.

It is possible to make changes to the layout of thereport (fonts, colors, positions of headings and titles, etc.)by using the tool palettes and tool bars. When you have finished touching up the report,close the Reports Live Previewer and the Object Navigatorwill reappear. To edit the report again, pull down theTools menu and choose Report Editor.

10.4.2 Saving, Generating and Running the Report

To save a report, pull down the File menu andchoose the Save option. This is similar tosaving an Oracle Form. The source code forOracle Reports are saved in files with an .rdffile name extension. Compiled and generated reportsare saved with a .rep extension.

For this example, save this report as employee.rdf

Note that report files can also be saved as static HTML or XMLfiles as well as Java Server Pages (.jsp) files.

Once the report is saved, it can be run by pullingdown the Program menu and choosing theRun Web Layout or Run Paper Layout menu item. The Paper Layout option will display the current reportdirectly within a window inside of Reports Builderas was seen at the end of the report wizard.The Web Layout will take the current report with a snapshot of thedata as it is now in the database and save it to an HTML file. The default web browser will then be launched to display thefile. This is shown in the figure below:

As the report is running, an Activity screen will appeargiving an indication of the processing that is currentlyunderway.

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

10.5 Exercise: Creating a Report

For this exercise, create a report to display allof the records in the DEPARTMENT table includinga join with the EMPLOYEE table to display the lastname of the department manager.
  • Start up the Report wizard. Use "Departments"as the title and enter the following SQL Statement asthe query:
    SELECT dnumber, dname, mgrstartdate, mgrssn, lname, salary
    FROM   department, employee
    WHERE  department.mgrssn = employee.ssn;
    
  • Summarize the report with an average of the Salary column.
  • Save the new report as departmg.rdf
The finished report should look something like the following:

10.6 Creating a Master-Detail Report

In this section, the steps for creatinga report that views the contents of two tables ina Master-Detail (one to many) relationship are given. To avoid any confusion, Save and Close any existingreports before proceeding.

10.6.1 Specify the Master-Detail Data Model and Layout

As in the single-table report, we begin byspecifying the data model and layout for the Master-Detailreport by using the Report Wizard.

Invoke the wizard by pulling down the Tools menuand choosing Report wizard. Following these stepsto complete the wizard:

  1. Choose both Web and Page Layouts
  2. For the Report Title use: Departments and Employees
  3. For the Report Style, choose Group Above.
  4. Choose a SQL Statement and the SQL Query:
    SELECT DNAME, FNAME, LNAME, SALARY
    FROM DEPARTMENT, EMPLOYEE
    WHERE DEPARTMENT.DNUMBER = EMPLOYEE.DNO
    
  5. Designate the DNAME field as a Group field(Level 1). Click on the DNAME field and then onthe right arrow button.
  6. Display all fields in the report.
  7. Choose Average Salary as a Totals field.Click on the SALARY field and then click on theAverage button.
  8. Touch up the fields and labels as follows:
    Fields and Totals   Labels
    DNAME               Department Name
    FNAME               First Name
    LNAME               Last Name
    SALARY              Salary
    AvgSALARYperDNAME   Average:
    AvgSALARYperRep     Average For all Employees:
    
  9. Use the Blue pre-defined template.
From this point, click on Finish to display theLive Report Preview:

10.6.2 Saving, Generating and Running the Master-Detail Report

At this point, the report can be savedand Execute (Run).

For this example, save the Master-Detailreport as dept_emp.rdf

10.7 Exercise: Creating a Master-Detail Report

In this exercise, a master-detail report will becreated that displays each EMPLOYEE record, with a group of DEPENDENT below.

  • Create a new Report using the wizard. Give atitle: "Employees and Their Dependents"
  • Select the Group Above report type anduse the following SQL query:
    SELECT  employee.fname, employee.lname, 
    employee.bdate AS EmpBdate, 
    employee.sex AS EmpSex, dependent.dependent_name,
    dependent.bdate AS DepBdate,  
    dependent.sex AS DepSex, dependent.relationship
    FROM    employee, dependent
    WHERE   employee.ssn = dependent.essn;
    
    Note the use of column aliases (AS) to differentiate thebdate and sex columns.
  • Select the lname, fname, empsex, empbdate fields as the "Group 1" fields. These shouldall be from the EMPLOYEE table.
  • Select all fields to display in the report and donot select any fields for the calculate totals step.
  • Touch up the report in the live previewer to fit onone screen.
  • Save the report as file: empdepen.rdf

The report should look similar to the following whencompleted:

10.8 Parameter forms in Oracle Reports

For each of the Oracle Reports examples thus far,all of the records in the respective tables werereturned as part of the report. However, there maybe situations where only a subset of the databaserecords are required. In these cases, we would like to prompt the user to enter some criteriathat can be used to filter the report. InOracle Reports, a Parameter Form fulfillsthis role.

Parameter forms are simple Oracle forms that promptthe user for some information before a report isexecuted. This information can become part of theWHERE clause in the report query and is used to filterthe records returned in the report. Parameters in Oracle Reportscan either be filled in by the user on a parameter form orcan be filled in automatically by passing the parameters in from another Oracle Forms form (see the example ofparameter passing in the previous section on Oracle Forms).

In the following example, a simple parameter form isautomatically created by Oracle Reports to restrict aquery on the EMPLOYEE table by showing only employeesin a given department number (DNO) supplied by theuser.

  1. Create a new report using the report wizard.Specify "Employees" as the report title and usea Tabular report layout.
  2. Use the following SQL query exactly as written.Note the use of the full colon in the WHERE clause.
    SELECT *
    FROM   employee
    WHERE  employee.dno = :DNO_PARAMETER;
    
    In the above query, :DNO_PARAMETER is the name of arun time parameter. When the report is executed, the user will supply a value for this parameterthus completing the WHERE clause for the query.
    Clicking on the Next button will produce thefollowing alert:

  3. Choose all of the columns (fields) for display inthe report. Do not choose any fields for summaryinformation.
  4. When the report wizard is finished, the report andparameter form (based upon the parameters given in the SQL query) will be created. As the live previeweris running, the new parameter form will be displayed:

    For this example, choose department 5 (type the number 5 in the field) and press the enter key.Notice that only employees in department 5 are displayedin the live previewer.

10.8.1 Building a Custom Parameter Form

At this point, whenever the report is run, a defaultparameter form showing the DNO_PARAMETER will be displayed.However, it is possible to create a custom parameter formas shown in the following steps.

  1. Switch to the Object Navigator, pull down the Tools menu and choose the Parameter Form Builder. This is shown below:

    A number of built-in system parameters are displayedand can be highlighted by clicking on them with the right mousebutton. Note that at the bottom of this list is the DNO_PARAMETER and it should already be selected.

  2. For this example, in addition to the DNO_PARAMETER also select the DESTYPE, DESNAME and COPIES parameters.Then click the OK button. A new parameter formwill be automatically created.

  3. Save the report as empparam.rdf. When the form is executed (pull down the Program menuand choose Run Report), the new parameter form willbe displayed as shown below:

    In the above example, 1 copy of the report will be sent to theprinter named "HP LaserJet 4000 Series". The report will only showemployees in department 4.

    Other Destination Type options include Screen, File and E-mail.

Parameter forms can be highly customized to include list of valuesuser prompts and other user-friendly features. The techniques usedfor Oracle Forms can be easily applied to parameter forms in Oracle Reports.


11. Creating Menus in Oracle Forms

Please note that as of May, 2004, I have only done a basicupdate of this section to reflect the new features of 9iDS.Therefore, the following sectionhas no figures, has not been tested and may not work at all.

Note: The properties for Menus indifferent versions of Forms seem to change with every minor revision. Please keep in mind, the version of Forms you areworking with may differ slightly from what is shown inthis tutorial.

Thus far, we have covered the basics for creatingforms and reports using Oracle Developer. Someadvanced features such as using stored procedures(Program Units) have also been introduced.

In a complete Database System, the applicationsconsist of many forms and reports that might beused by a number of users. Access to forms andreports is typically accomplished throughmenus. In Oracle Developer, menus canbe created to guide users into forms and reportsof interest. Menus are designed as part of theOracle Forms Builder.

In Oracle Forms, every form runs withone of the following:

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

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

The default menu is part of the form module. However, custom menumodules are separate from form modules. So, when a single-form applicationruns 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 sharethe same menu, or each form can invoke a different menu.

To explicitly specify the default menu in Forms:
  1. Select the name of the form/module in the Object Navigator.Click with the right mouse button and choose Propertiesfrom the pop-up menu.
  2. In the Menu Module property, type DEFAULT&SMARTBARto get the default menu and a button bar below it.

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

To explicitly specify the default menu in older versions of Developer:
  1. Select the name of the form/module in the Object Navigator.Click with the right mouse button and choose Propertiesfrom the pop-up menu.
  2. In the Menu Module property, type DEFAULT.

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

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

11.1 The Oracle Forms Menu Hierarchy

The Oracle Forms menu hierarchy contains three object categories:

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

11.2 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 (Compiling) the menu module (creates the .mmx file)
  5. Attaching a menu module to a form

11.2.1 Creating a Menu Module

The first step in designing a custom menu is tocreate a menu module. There are several ways tocreate a new menu module:
  1. Pull down the File menu, highlight theNew menu item and then select theMenu menu item.
  2. Navigate down to the Menus subtree in theObject Navigator, pull down the Editmenu and choose the Create menu item.
A new menu module will be created and a default name will beassigned. Click on this default menu name and assign it a moremeaningful name such as mymenu.

11.2.2 Creating menus and menu items

Creating menus and menu items can be accomplished usingthe Menu Editor. To activate the menu editor,first highlight the name of your menu, thenpull down the Tools menu and click onthe Menu Editor item.

The main menu items go across the top of the screen.To add a new menu across the top, click on theBuild 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 theBuild Down icon on the toolbar:

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

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

Once this is completed, the basic menu structure willbe in place. The next step is to add functionality toeach menu item.

11.2.3 Assigning Commands to Menu Items

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

To assign a command to a menu item:

  1. Select the desired menu item in the Menu Editor. Click with theright mouse button and choose Propertiesto bring up the properties of that menu item.
    Note: The pop-up menu may show Property Palette
  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.
    • Menu Item Code (or Command Text): The text of the actual command to run.

    To run a form from the menu item, choose Command TypePL/SQL. Double click on the Menu Item Code (or Command Text) propertyand type in the PL/SQL editor:

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

    RUN_PRODUCT is a PL/SQL built-in function thatruns another Oracle Developer product.The parameters to RUN_PRODUCT are:

    • Product: FORMS or REPORTS
    • Document/Module: name of the file in single quotes (Note that you may also provide an explicit path to the .fmx file here)
    • 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. Note: Starting with Forms 5.0, (which uses PL/SQL version 2.3) you may not use NULL in the call to RUN_PRODUCT here. You must use two single quotes (indicating a zero length character string).

    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 theapplication. Call the menu: Exit Applicationand 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:

11.2.4 Save and Generate the Menu Module

At this point, all of the menu structure has beencreated and commands for each menu item havebeen specified. The menu module must now besaved to a file (with a .mmb extension)and Generated or Compiled.

To save the menu module, make sure the name of themenu is highlighted, pull down the File menuand choose the Save menu item.Specify a file name with a .mmbextension. This is the "source code"for the menu. For this example, use thefile name mymenu.mmb.

Pre-Oracle9iDS Oracle 9iDS
To generate or compile the menu module, pull down theFile menu, click on the Administrationmenu item and choose Generate from theflyout menu. In some versions of the Forms Builder, themenu item might be called Compile File. To generate or compile the menu module, pull down theProgram menu and choose Generate Module.

Generating or compiling a menu module results ina file with a .mmx extension.In this example, the generated menu module becomes:mymenu.mmx

11.2.5 Attaching a Menu Module to a Form

A custom menu must be attached to a form in orderto be displayed.Typically, a form with no data entry on itis used as the main screen to host thecustom menu.

The following steps are different depending on theversion of Forms being used.

For Oracle Forms version 4.5 For Oracle Forms version 6i (and later)
  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 the form as file blankform.fmb and generate this form. (Note you should save the this form in the same drive and directory as your menu's .mmx file).
  5. Run the form.
  1. Create a new form with no blocks or items on it. Pull down the File menu, select New and then select Form.
    Click on the name of the form in the object navigator and rename it as blankform.
  2. Open up the property palette for the form and set the following properties:
    Menu Source: File - The menu information will come from a file.
    Menu Module: mymenu.mmx - The file name of the compiled menu module. You may have to give the explicit path to this .mmx file. For example, if this file is on your A: drive then type a:\mymenu.mmx

  3. Save the form as file blankform.fmb and generate this form. (Note you should save the this form in the same drive and directory as your menu's .mmx file).
  4. Run the form.

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

The blank form that is used to host the menu need not be blank. You may wish to use the Layout Editorto place some graphics and a title of the applicationon the canvas.

Another use for this space would be for some simpleinstructions the user can read to get started withthe database application.



Please feel free to use these tutorials for your own personaluse, education, advancement, training, etc. However, I kindlyask that you respect the time and effort I have put intothis work by not distributing copies, either in whole or in part,for your personal gain. You may not under any circumstancesdownload, cache or host copies of these tutorials on your ownservers. Use of these tutorials for commercial trainingrequires a special arrangement with the author or authors.



All material Copyright 1997-2004R. Holowczak


转载:http://www.holowczak.com/oracle/dev9i/

分享到:
评论

相关推荐

Global site tag (gtag.js) - Google Analytics