41 Building a Paper Report with REF CURSORs

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

Figure 41-1 REF CURSOR report output

Description of "Figure 41-1 REF CURSOR report output"

Concepts

• A REF CURSOR is a PL/SQL datatype that you can use in a query to fetch data. Each REF CURSOR query is associated with a PL/SQL function that returns a strongly typed REF CURSOR. The PL/SQL function must ensure that the REF CURSOR is opened and associated with a SELECT statement that has a SELECT list that matches the REF CURSOR type. You base a query on a REF CURSOR when you want to:

  • more easily administer SQL.

  • avoid the use of lexical parameters in your reports.

  • share data sources with other applications.

  • increase control and security.

  • encapsulate logic within a subprogram.

• If you use a stored program unit to implement REF CURSORs, you receive the added benefits that go along with storing program units in the Oracle database.

• Reports Builder enables you to easily manage your queries by use of using REF CURSORs. For example, if you already have numerous queries built and you want to reuse those queries in your reports, you can simply use a REF CURSOR in your report data model to access those queries.

• Figure 41-2 shows that you create a report with the SELECT statement in the REF CURSOR query of the report. It also shows that you can store the SELECT statement in a package in the database. Then, from the report, you can call the database package allowing you to reuse the package in many reports.

  Figure 41-2 Overview of the REF CURSOR example

  
  Description of "Figure 41-2 Overview of the REF CURSOR example"
  
  

Example Scenario

In this example, you will learn how to use REF CURSORs in Reports Builder to create a detailed paper report showing information about employees and the job position they hold in each department. You will use the Data Model view to create a multiquery data model, and then use the Report Wizard to create the report layout. You will make fairly extensive manual refinements in the Data Model view.

As you build this example report, you will:

• Define a REF CURSOR Type by creating package specs that define REF CURSORs.

• Create a REF CURSOR Query that will use the REF CURSORs.

• Refine the Data Model by renaming objects so that they have more meaningful names.

• Create Links Between REF CURSOR Queries to create relationships between them.

• Add Summary Columns that better describe the data.

• Create a Layout using the Report Wizard.

• Move the SELECT Statements Into Packages that define the REF CURSOR types.

• Move the Packages Into a Library so that other reports can share the code.

To see a sample report with REF CURSORs, open the examples folder named refcursor, then open ref_emp68.rdf. For details on how to open it, see "Accessing the Example Reports" in the Preface. The example files used in this chapter are listed and described in Table 41-1.

Table 41-1 Example report files

File	Description
refcursor\ref_emp*.rdf	The different stages of the RDF. You can refer to these files as you complete each section of this chapter. The file ref_emp68.rdf is the final report.
refcursor\refcursor_code.txt	The PL/SQL code you will write in this chapter. You can use this file as a reference point to make sure your code is accurate, or you can simply copy and paste from this file into Reports Builder.