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.

---

I have never tried this kind of reporting before. Mail from a friend prompted me that I should start creating this report and also publish an article which shows a step-by-step process of creating one.

 

So here is the list that we are going to achieve:

 

1. Create a DTD file
2. Create an XML file
3. Create a report based on the two files created.

 

First step is to create a DTD file.

 

Our XML file contains details of all database environments in use by a team.

 

The following is our DTD file. Save the following contents as c:\database.dtd:

 

<?xml version="1.0" encoding="UTF-8"?>

<!ELEMENT DatabaseInventory (DatabaseName+)>

<!ELEMENT DatabaseName (   GlobalDatabaseName

                         , OracleSID

                         , DatabaseDomain

                         , Administrator+

                         , DatabaseAttributes

                         , Comments)

> 

<!ELEMENT GlobalDatabaseName (#PCDATA)>

<!ELEMENT OracleSID          (#PCDATA)>

<!ELEMENT DatabaseDomain     (#PCDATA)>

<!ELEMENT Administrator      (#PCDATA)>

<!ELEMENT DatabaseAttributes EMPTY>

<!ELEMENT Comments           (#PCDATA)>

 

<!ATTLIST Administrator       EmailAlias CDATA #REQUIRED>

<!ATTLIST Administrator       Extension  CDATA #IMPLIED>

<!ATTLIST DatabaseAttributes  Type       (Production|Development|Testing) #REQUIRED>

<!ATTLIST DatabaseAttributes  Version    (7|8|8i|9i) "9i">

 

<!ENTITY AUTHOR "Anantha Narayanan">

<!ENTITY WEB    "http://askanantha.blogspot.com">

 

The contents of the structure is as follows:

1. DatabaseInventory (Row of type DatabaseName)
2. DatabaseName is a type of fields (GlobalDatabaseName, OracleSID, DatabaseDomain, Administrator+, DatabaseAttributes, Comments)
3. GlobalDatabaseName is a field of type PCDATA
4. OracleSID is a field of type PCDATA
5. DatabaseDomain is a field of type PCDATA
6. Administrator is a field of type PCDATA
7. DatabaseAttributes is EMPTY by default
8. Comments is a field of type PCDATA

 

In the ATTLIST tag, we are defining the properties of the fields of type PCDATA.

 

Now the second step is to create XML file as below. Save the following contents as database.xml:

<?xml version="1.0"?>

<!DOCTYPE DatabaseInventory SYSTEM "db.dtd">

 

<DatabaseInventory>

 

  <DatabaseName>

    <GlobalDatabaseName>mydb.myspace.com</GlobalDatabaseName>

    <OracleSID>production</OracleSID>

    <DatabaseDomain>myspace.com</DatabaseDomain>

    <Administrator EmailAlias="tlananthu" Extension="2354">Anantha</Administrator>

    <DatabaseAttributes Type="Production" Version="10g"/>

    <Comments>

      This is production database.

    </Comments>

  </DatabaseName>

 

  <DatabaseName>

    <GlobalDatabaseName>prod.myspace.com</GlobalDatabaseName>

    <OracleSID>development</OracleSID>

    <DatabaseDomain>myspace.com</DatabaseDomain>

    <Administrator EmailAlias="tlananthu" Extension="2354">Anantha</Administrator>

    <DatabaseAttributes Type="Production" Version="10g"/>

    <Comments>

      This is development database.

    </Comments>

  </DatabaseName>

 

  <DatabaseName>

    <GlobalDatabaseName>test.myspace.com</GlobalDatabaseName>

    <OracleSID>testing</OracleSID>

    <DatabaseDomain>myspace.com</DatabaseDomain>

    <Administrator EmailAlias="tlananthu" Extension="2354">Anantha</Administrator>

    <DatabaseAttributes Type="Production" Version="10g"/>

    <Comments>

      This is testing database.

    </Comments>

  </DatabaseName>

 

</DatabaseInventory>

 

Now start Report Builder.

 

1. Create a New report using the wizard
2. Click next on Welcome wizard
3. Select Create both Web and Paper Layout option and click Next
4. Choose a Tabular report
5. Choose XML Query
6. In the Query Source definition choose Query Definition button
7. In the Define XML Query choose the database.dtd and choose select columns button
1. Choose all columns
8. In the Data source field choose the database.xml file and click OK
9. Click next
10. Choose all fields and click next
11. Click next
12. Edit the labels if necessary.
13. Click next
14. Choose a template if necessary
15. Click Finish.

 

 

Congratulations, you have created a report based on an XML query successfully.

 

PL SQL ERROR 201: SRW.MESSAGE must be declared

If you are unable to compile your reports or plls in Oracle 10g Version, due to the error like

PL SQL ERROR 201: SRW.___ must be declared, then check the existence of the following file and its privilege:

rwrun.jar in /Oracle10gAS/reports/jlib folder.

Where Oracle10gAS is the folder where 10g Application server is installed.

You will normally get this error only in UNIX/Linux flavors, because this is related to privilege rights. If you are getting this error in Windows also, then it is quietly possible that the file is missing.

The file must have the privilege 755 or -rwx-r-x-r-x.