A Basic Tutorial on Oracle9i Forms and Reports

Covering Developer version 9i for Windows NT/2000/XP By Richard Holowczak

This tutorial introduces the Oracle9i Developer Suite Release 2 (August, 2002) that includes Oracle Forms 9.0 and Oracle Reports 9.0. The main objectives are to demonstrate and provide hands-on instructions on creating and modifying data entry and query forms in various configurations, reports and graphics.

Caveats: Please note that Oracle tends to change things like menu items, prompts and other small things between each major (certainly) and often minor release. Depending on the exact release of Oracle9i Developer Suite you have, you may find some small discrepencies between what is shown in this tutorial and what you see on your screen.

Prerequisites
Before following this tutorial, a student must have a valid user account in an Oracle server or a local installation of an Oracle database. Contact your DBA or systems administrator to learn the details of how Oracle server is set up in your organization.

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

One more word of caution. Oracle has designed Oracle9i as a three tier architecture. This means that the database (Oracle9i server) should run on one server, the application Server (oracle9i Application Server or Oracle9iAS Containers for Java (OC4J) server as used here) runs on a second server and the client application (written in Oracle Forms and Reports for example) runs on a third machine.

Contents

• 1. Introduction
• 2. Prerequisites
• 3. Developer Tools
  • 3.1 Starting Developer tools under Windows
• 4. The Oracle Forms Builder Main Screen
  • 4.1 Oracle Forms Object Navigator
  • 4.2 Connecting to the Database
• 5. Forms Design
• 6. Creating a Form with a Single Block
  • 6.1 Creating a New Block
  • 6.2 The Layout Editor
  • 6.3 Displaying Properties
  • 6.4 Saving, Generating and Running Forms
    • 6.4.1 Saving a Form
    • 6.4.2 Compiling/Building a Form
    • 6.4.3 Running a Form
    • 6.4.4 Query By Example
  • 6.5 Exercise: Creating a Single Block Form
• 7. Creating a Master-Detail Form
  • 7.1 The Master-Detail Relationship
  • 7.2 Steps to Create a Master-Detail Form
    • 7.2.1 Create the Master Block
    • 7.2.2 Create the Detail Block
  • 7.3 Relation Properties of a Master-Detail Form
  • 7.4 Program Units in a Master-Detail Form
  • 7.5 Extending Master-Detail Forms
  • 7.6 Exercise: Creating a Master/Detail Form
• 8. List of Values (LOVs) and Non-Base Table Fields
  • 8.1 Creating a List of Values
    • 8.1.1 Create a new LOV Object
    • 8.1.2 Specify the LOV Query Text
    • 8.1.3 Specify the Column Mapping Properties
    • 8.1.4 Attach the LOV to the item
    • 8.1.5 Test the LOV
  • 8.2 Non-Base Table fields
    • 8.2.1 Adding a New Item to an Existing Data Block
    • 8.2.2 Creating a Trigger
    • 8.2.3 Create a new List of Values
  • 8.3 Exercise: Creating an LOV and Non-Base Table field
• 9. Oracle Forms Program Units and Stored Program Units
  • 9.1 Creating Procedures in Oracle Forms
    • 9.1.1 Add a new item to the EMPLOYEE Block
    • 9.1.2 Create a Procedure to Count Other Department Members
    • 9.1.3 Create a Trigger to Call the Procedure
    • 9.1.4 Save, Compile and Run the Form
  • 9.2 Creating Stored Program Units (Procedures) in SQL*Plus
  • 9.3 Additional Stored Procedures and Triggers
    • 9.3.1 Generating unique or consecutive identifiers using the table
    • 9.3.2 Generating unique or consecutive identifiers using an Oracle Sequence
    • 9.3.3 Checking constraints at the client
    • 9.3.4 Adding Alerts (Pop-up Messages)
    • 9.3.5 Automatic Forms Query
    • 9.3.6 Populate items with default values
    • 9.3.7 Attaching functionality to command buttons
    • 9.3.8 Passing parameters between forms
  • 9.4 Exercise: Procedures and Triggers
• 10. Oracle Reports Basics
  • 10.1 Starting Developer tools under Windows
  • 10.2 The Oracle Reports Object Navigator
  • 10.3 Creating Reports in Oracle Reports
  • 10.4 Creating a Single-Table Report
    • 10.4.1 Specify the Data Model and Layout
    • 10.4.2 Saving, Generating and Running the Report
  • 10.5 Exercise: Creating a Report
  • 10.6 Creating a Master-Detail Report
    • 10.6.1 Specify the Master-Detail Data Model and Layout
    • 10.6.2 Saving and Running the Master-Detail Report
  • 10.7 Exercise: Creating a Master-Detail Report
  • 10.8 Parameter forms in Oracle Reports
    • 10.8.1 Building a Custom Parameter Form
• 11 Creating Menus in Oracle Forms
  • 11.1 The Oracle Forms Menu Hierarchy
  • 11.2 Creating a Custom Menu

---

About the Author: Richard Holowczak is an Associate Professor of Computer Information Systems at the Zicklin School of Business in Baruch College, City University of New York.

---