40 Building a Report that Includes PL/SQL

In this chapter, you will learn about reports that include PL/SQL. By following the steps in this chapter, you can generate the report output shown in Figure 40-1.

Figure 40-1 PL/SQL report output

Description of "Figure 40-1 PL/SQL report output"

Concepts

• There are a variety of ways to incorporate PL/SQL into your reports. You have already created formula columns that used simple PL/SQL expressions to compute their values, and format triggers that used PL/SQL to conditionally determine the formatting of mailing labels. Here, you will create external libraries and local functions and procedures.

• External PL/SQL libraries are modules that contain named PL/SQL functions and procedures. They may be stored either in the database or in a file, and can be referenced from not only any report, but from other Oracle products. External libraries eliminate the need to re-enter commonly-used PL/SQL constructs, whether in reports, forms, or graphs. This, in turn, eliminates the problem of maintaining several versions of the same PL/SQL code.

• Local PL/SQL consists of named PL/SQL functions and procedures that are saved in a report definition. Local PL/SQL may be referenced only by objects within the report (for example, group filters, formula columns, format triggers, and so on). However, the usefulness of storing PL/SQL in a single location still applies.

Data Relationships

This report uses one query. You will add a function stored in an external library, a report-level function, two formula columns, and a parameter governing the number of records to display before inserting a space.

Layout

This report uses the tabular layout style, with minor modifications.

Example Scenario

In this example, you will use an external PL/SQL library and PL/SQL within a report to modify formatting to add spacing between records, and calculate the total compensation for each employee. You will manually create a query in the Data Model view, then modify the layout of the report in the Paper Layout view. You will create formula columns, a summary column, and a format trigger that uses a summary column and a user parameter.

As you build this example report, you will:

• Create a New PL/SQL Library that you will use in this report.

• Create the Report Definition:

  • Create a query.

  • Create a formula column that calculates bonuses.

  • Create a report-level function that calculates total compensation.

  • Create a second formula column for total compensation.

• Create the Report Layout Using the Report Block Wizard.

• Add Vertical Space Between Records:

  • Create a user parameter.

  • Create a summary column that counts the number of records.

  • Modify the layout.

  • Create a format trigger.

• Run Your Report to Paper.

To see a sample PL/SQL report, open the examples folder named plsql, then open the Oracle Reports example named plsql.rdf. For details on how to open it, see "Accessing the Example Reports" in the Preface.