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.
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.
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.
This report uses the tabular layout style, with minor modifications.
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.
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.