Skip Headers

Oracle® Reports Building Reports 10g Release 2 (10.1.2) B13895-01 |
| Library | Product | Contents | Index

Previous | Next |

# 40 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 40-1.

Figure 40-1 REF CURSOR report output

Description of "Figure 40-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 CURSOR`s, 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 CURSOR`s. 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 40-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 40-2 Overview of the REF CURSOR example

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

Example Scenario

In this example, you will learn how to use `REF CURSOR`s 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 CURSOR`s.

- Create a REF CURSOR query that will use the `REF CURSOR`s.

- 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 CURSOR`s, open the examples folder named `RefCursor`, then open `result\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 40-1.

Table 40-1 Example report files

File | Description
--- | ---
`Examples``\RefCursor\result\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.
`Examples``\RefCursor\scripts\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.

## 40.1 Prerequisites for this example

To build the example in this chapter, you must have the example files provided, as well as access to the Human Resources sample schema (HR) provided with the Oracle Database. If you do not know if you have access to this sample schema, contact your database administrator.

## 40.2 Define a REF CURSOR type

To create a `REF CURSOR` query, you first create a package spec that defines the `REF CURSOR`. Then you create a query that uses the `REF CURSOR`. The steps in this section will help you create package specs that define `REF CURSOR`s.

To define a REF CURSOR type:

1. Launch Reports Builder (or, if already open, choose File > New > Report).

2. In the Welcome or New Report dialog box, select Build a new report manually, then click OK.

3. In the Object Navigator, click the Program Units node under the new report node (MODULEx).

4. Click the Create button in the toolbar to display the New Program Unit dialog box.

5. In the New Program Unit dialog box, type `concl_cv` in the Name field.

6. Select Package Spec, then click OK to display the PL/SQL Editor.

7. In the PL/SQL Editor, use the template to enter the following PL/SQL code:

       PACKAGE concl_cv IS
           type conclass_rec is RECORD
              (EMPLOYEE_ID NUMBER(6),
            FIRST_NAME VARCHAR2(20),
            LAST_NAME VARCHAR2(25),
            EMAIL VARCHAR2(25),
            PHONE_NUMBER VARCHAR2(20),
            HIRE_DATE DATE,
            JOB_ID VARCHAR2(10),
            SALARY NUMBER(8,2),
            DEPARTMENT_ID NUMBER(4));
           type conclass_refcur is REF CURSOR return conclass_rec;
       END;

   This package spec does two things:

   - defines a record (conclass_rec) that describes the data you want to select from the database.

   - defines a `REF CURSOR` that returns the data in the format described by the record.

     Note: You can open the file `Examples``/RefCursor/scripts/refcursor_code.txt` to copy and paste the code into the PL/SQL Editor.

8. Click Compile.

9. If any compilation errors occur, check the code for syntax errors and recompile as needed.

10. Click Close.

11. Repeat steps 3 through 8 to create two more package specs:

    - Package Spec Name: cont_cv

          PACKAGE cont_cv IS
              type container_rec is RECORD
                 (EMPLOYEE_ID NUMBER(6),
                  START_DATE DATE,
                  END_DATE DATE,
                  JOB_ID VARCHAR2(10),
                  DEPARTMENT_ID NUMBER(4));
              type container_refcur is REF CURSOR return container_rec;
          END;

    - Package Spec Name: port_cv

          PACKAGE port_cv IS
            type portdesc_rec is RECORD
                 (DEPARTMENT_ID NUMBER(4),
                  DEPARTMENT_NAME VARCHAR2(30));
            type portdesc_refcur is REF CURSOR return portdesc_rec;
          END;

      Note: You can open the file `Examples``/RefCursor/scripts/refcursor_code.txt` to copy and paste the code into the PL/SQL Editor.

12. Choose File > Save As. Save the report in the directory of your choice, and name the report `ref61_``your_initials``.rdf`.

    Note: It is good practice when you are designing your report to save it frequently under a different file name. If you generate an error or if you do not like some of the changes you made, you easily can go back to the previously saved file and make revisions from that point.

## 40.3 Create a REF CURSOR query

After creating package specs that define the `REF CURSOR`s, you are ready to define the queries, as described in this section.

To create a REF CURSOR query:

1. In the Object Navigator, double-click the view icon next to the Data Model node to display the Data Model view.

2. In the Data Model view, click the Ref Cursor Query tool in the tool palette.

3. Click in an open area of the Data Model view to display the PL/SQL Editor for the new `REF CURSOR` query.

4. In the PL/SQL Editor, use the template to enter the following PL/SQL code:

       function q_portdescRefCurDS return port_cv.portdesc_refcur is temp_portdesc port_cv.portdesc_refcur;
       begin
       open temp_portdesc for select department_id, department_name from
       departments;
         return temp_portdesc;
       end;

   Note: You can open the file `Examples``/RefCursor/scripts/refcursor_code.txt` to copy and paste the code into the PL/SQL Editor.

5. Click Compile.

6. If any compilation errors occur, check the code for syntax errors and recompile as needed.

7. Click Close.

8. In the Data Model view, double-click the new `REF CURSOR` query object (QR_1) to display the Property Inspector.

   - Under General Information, set the Name property to q_portdesc.

     Tip: It is usually a good idea to give objects meaningful names, particularly when building a report with many objects. Later when building the layout, it is helpful to have queries and groups with meaningful names.

9. Repeat the steps above to create two more queries:

   - Query name: q_container

         function q_containerRefCurDS return cont_cv.container_refcur is
         temp_container cont_cv.container_refcur;
         begin
             open temp_container for
              select employee_id,
                  start_date,
                  end_date,
                  job_id,
                  department_id
               from job_history;
             return temp_container;
         		end;

   - Query name: q_conclass

         function q_conclassRefCurDS return concl_cv.conclass_refcur is
         temp_concl concl_cv.conclass_refcur;
         begin
          open temp_concl for
            select employee_id,
                 first_name,
                 last_name,
                 email,
                 phone_number,
                 hire_date,
                 job_id,
                 salary,
                 department_id
            from employees;
           return temp_concl;
         end;

     Note: You can open the file Examples/RefCursor/scripts/refcursor_code.txt to copy and paste the code into Reports Builder.

   The Data Model should look similar to the following:

   Figure 40-3 Data model with three queries

   Description of "Figure 40-3 Data model with three queries"

10. Save the report as `ref_62_``your_initials``.rdf`.

    Note: You can open the provided file `Examples``/RefCursor/result/ref_emp62.rdf` and display the Data Model to compare your results.

## 40.4 Refine the data model

In this section, you will rename some of the objects in the data model so that they have more meaningful names. You will also create a break group.

To refine the data model:

1. In the Data Model view, drag the title bar of the group G_EMPLOYEE_ID down about 4 inches (10 centimeters) to move the entire group.

2. Click and drag the column named JOB_ID out of and above G_EMPLOYEE_ID to create a new break group, as shown in the following figure:

   Figure 40-4 Query with group

   Description of "Figure 40-4 Query with group"

3. Double-click the title bar of the new group that contains JOB_ID to display the Property Inspector, and set the following properties:

   - Under the General Information node, set the Name property to G_conlabel.

4. In the Data Model view, your data model should look similar to the following:

   Figure 40-5 Data model with group

   Description of "Figure 40-5 Data model with group"

   Note: You can open the provided file `Examples``/RefCursor/result/ref_emp63.rdf` and display the Data Model to compare your results.

5. Save the report as `ref_63_``yourinitials``.rdf`.

## 40.5 Create links between REF CURSOR queries

Currently, the queries that you have created are unrelated. To create relationships between them, you need to create group-to-group data links. The steps in this section will help you create the links.

To create links between `REF CURSOR` queries:

1. In the Data Model view, click the Data Link tool in the tool palette.

2. Click the title bar of G_DEPARTMENT_ID, and drag a link to the title bar of G_EMPLOYEE_ID.

3. Double-click q_container to display the PL/SQL Editor.

4. In the PL/SQL Editor, append code to the `WHERE` clause of the `SELECT` statement to specify which columns are being used as primary and foreign keys:

   After `from job_history`, add the following code:

       where :department_id = department_id;

   Be sure that the semicolon (`;`) now follows the `WHERE` clause.

   Note that `:department_id` is a bind variable referring to the `DEPARTMENT_ID` in `G_DEPARTMENT_ID`.

5. Click Compile.

6. If any compilation errors occur, check the code for syntax errors and recompile as needed.

7. Click Close.

8. In the Data Model view, click the Data Link tool in the tool palette.

9. Click the title bar of G_EMPLOYEE_ID and drag a link to the title bar of G_EMPLOYEE_ID1.

10. Double-click q_conclass to display the PL/SQL Editor.

11. In the PL/SQL Editor, add a `WHERE` clause to the `SELECT` statement:

    Insert your cursor between `FROM EMPLOYEES` and the semicolon (`;`), and press ENTER or RETURN to create a new line, then add the following code:

        where :employee_id = employee_id;

    Be sure that the semicolon (`;`) now follows the `WHERE` clause.

    Note that `:employee_id` is a bind variable referring to the `EMPLOYEE_ID` column in `G_EMPLOYEE_ID`.

12. Click Compile.

13. If any compilation errors occur, check the code for syntax errors and recompile as needed.

14. Click Close.

15. Your data model should look similar to the following:

    Figure 40-6 Data model with links

    Description of "Figure 40-6 Data model with links"

    Note: You can open the provided file `Examples``/RefCursor/result/ref_emp64.rdf` and display the Data Model to compare your results.

16. Save the report as `ref_64_``your_initials``.rdf`.

## 40.6 Add summary columns

Now that your queries are complete and linked, the steps in this section will help you to create columns to summarize the data.

To add summary columns:

1. In the Data Model view, click the Summary Column tool in the tool palette.

2. Click inside the G_EMPLOYEE_ID group to create a summary column.

3. Double-click the new summary column object (CS_1) to display the Property Inspector, and set the following properties:

   - Under General Information, set the Name property to CS_classcount.

   - Under Summary, set the Function property to Count, set the Source property to employee_id, and set the Reset At property to G_department_id.

   You have now created a summary that counts up the number of employees. You will not use the summary in this report's layout, but you will use it as the source for other, more interesting summaries later.

4. Repeat the steps above to create summaries with the following characteristics:

   Table 40-2 Summary Characteristics

   Create in Group | Name | Function | Source | Reset At
   --- | --- | --- | --- | ---
   G_conlabel | CS_conlabel_classcount | Sum | CS_classcount | G_conlabel
   G_department_id | CS_port_count | Sum | CS_conlabel_classcount | G_DEPARTMENT_ID

   You may not understand these summaries now. Their purpose will become clearer when you create the report layout and preview the live data.

   Your data model should look similar to the following:

   Figure 40-7 Data model with summary columns

   Description of "Figure 40-7 Data model with summary columns"

   Note: You can also compare your results to the file we have provided, called `ref_emp65.rdf`.

5. Save the report as `ref_65_``your_initials``.rdf`.

## 40.7 Create a layout

Now that you have a working data model, the steps in this section will help you to create a layout.

To create a paper layout:

1. In the Object Navigator, right-click the report name and choose Report Wizard.

2. In the Report Wizard, on the Report Type page, select Create Paper Layout Only.

3. On the Style page, type `My Employees` in the Title field, select Group Above.

4. On the Groups page, click the following fields in the Available Fields list and click Down to specify the Print Direction and move them to the Group Fields list:

   - G_conlabel

   - G_DEPARTMENT_ID

   - G_EMPLOYEE_ID

5. On the Fields page, click the following fields and click the right arrow (>) to move them to the Displayed Fields list:

   - DEPARTMENT_NAME

   - EMPLOYEE_ID

   - START_DATE

   - END_DATE

   - JOB_ID

   - CS_port_count

6. On the Labels page, change the labels and field widths as follows:

   Table 40-3 Field description of Labels page

   Fields | Labels | Width
   --- | --- | ---
   DEPARTMENT_NAME | `Department` | `30`
   EMPLOYEE_ID | `Employee ID` | `8`
   START_DATE | `Start Date` | `9`
   END_DATE | `End Date` | `9`
   JOB_ID | `Job ID` | `10`
   CS_port_count | `Total:` | `12`

7. On the Template page, choose Predefined template and click Beige, then click Finish to display your report output in the Paper Design view. It should look something like this:

   Figure 40-8 Paper Design view for the REF CURSOR report

   Description of "Figure 40-8 Paper Design view for the REF CURSOR report"

   Note: You can open the provided file `Examples``/RefCursor/result/ref_emp66.rdf` and display the Paper Design view to compare your results.

8. Save the report as `ref_66_``your_initials``.rdf`.

## 40.8 Move the SELECT statements into packages

In your current report configuration, the `SELECT` statements used by the `REF CURSOR` queries reside within the report itself. In many cases, it is advantageous to have `SELECT` statements reside in the packages that define the `REF CURSOR` types. Then, you can simply reference the packages, rather than typing the same `SELECT` statement directly into every report that uses it. If you need to change the `SELECT` statement (for example, to modify or add clauses), you simply update it once in the package, rather than in every report that uses it.

The steps in this section will help you to move the `SELECT` statements to the packages that define the `REF CURSOR` types.

To move SELECT statements into a package:

1. In the Object Navigator, click the Program Units node for your report.

2. Click the Create button in the toolbar to display the New Program Unit dialog box.

3. In the New Program Unit dialog box, type `cont_cv` in the Name field.

4. Select Package Body, and click OK to display the PL/SQL Editor for the new program unit.

5. In the PL/SQL Editor, use the template to enter the following PL/SQL code:

       PACKAGE BODY cont_cv IS
            function query_container (p_department_id number)
            return container_refcur is tempcv_container cont_cv.container_
            refcur;
       begin
         open tempcv_container for
         select employee_id,
         start_date,
         end_date,
         job_id,
         department_id
         from job_history
         where  :department_id=department_id;
         return tempcv_container;
       end;
       END;

   Note: You can open the provided file `Examples``/RefCursor/scripts/refcursor_code.txt` to copy and paste the code into Reports Builder.

6. Click Compile.

7. If any compilation errors occur, check the code for syntax errors and recompile as needed.

8. Click Close.

9. Now that the function is defined, you must add it to the package spec so that it can be referenced. Other program units will know about the function in the package body only if it is described in the package spec.

10. In the Object Navigator, double-click the CONT_CV (Package Spec) object to display the PL/SQL Editor.

11. In the PL/SQL Editor, type the following line above the `END;` statement:

        function query_container (p_department_id number) return container_refcur;

12. Click Close.

13. Choose Program > Compile > All.

14. Click OK when done.

15. In the Object Navigator, under the Program Units node, double-click Q_CONTAINERREFCURDS to display the PL/SQL Editor.

16. In the PL/SQL Editor, edit the code to look as follows:

        function Q_containerRefCurDS return cont_cv.container_refcur is
            temp_container cont_cv.container_refcur;
        begin
             temp_container:=cont_cv.query_container (:department_id);
               return temp_container;
        end;

    When you are done, all of the query's logic will reside in the function named `query_container`. From now on, when you change `query_container`, you will change this and any other queries that reference it.

    Note: You can open the file Examples`/RefCursor/scripts/refcursor_code.txt` to copy and paste the code into Reports Builder.

17. Click Compile.

18. If any compilation errors occur, check the code for syntax errors and recompile as needed.

19. Click Close.

20. Click the Paper Design button in the toolbar to view the report in the Paper Design view.

21. Save the report as `ref_67_``your_initials``.rdf`.

Optional Exercise:

Repeat the above steps for the other two queries in the report.

## 40.9 Move the packages into a library

If you have many reports that use these same `REF CURSOR` types and `SELECT` statements, you can move the program units that you created into a PL/SQL library stored in a file, so that other reports can easily share the code. The steps in this section will help you to move the program units to a PL/SQL library.

To move the packages into a library:

1. In the Object Navigator, click the PL/SQL Libraries node, then click the Create button in the toolbar to add a new library.

2. Choose File > Save As.

3. Type `DEPT_CONTAINER` as the Library.

4. Ensure that File System is selected.

5. Click OK.

6. Drag and drop the following program units from your report to the Program Units node under the newly created DEPT_CONTAINER library:

   - CONCL_CV (Package Spec)

   - CONT_CV (Package Spec)

   - CONT_CV (Package Body)

   - PORT_CV (Package Spec)

7. Save `DEPT_CONTAINER`.

8. If the Paper Design view is open, close it.

9. In the Object Navigator, under the Program Units node for your report, delete CONCL_CV (Package Spec), CONT_CV (Package Spec), CONT_CV (Package Body), and PORT_CV (Package Spec).

   Note: If the Paper Design view is open when you delete the packages from the report, you may get some errors.

10. Click the Attached Libraries node for your report, then click the Create button in the toolbar to add a new attached library.

11. Click Browse to find the DEPT_CONTAINER library. It will have a `.PLL` file extension. After you have found and selected DEPT_CONTAINER, click Open.

12. Click Attach.

13. Choose Program > Compile > All.

14. Click OK to close the Compile window.

15. Click the Run Paper Layout button in the toolbar to run the report and view it in the Paper Design view.

16. Save the report as `ref_68_``your_initials``.rdf`.

## 40.10 Summary

Congratulations! You have finished the `REF CURSOR` query sample report. You now know how to:

- create package specs that define `REF CURSOR`s.

- create `REF CURSOR` queries.

- create data links between `REF CURSOR` queries.

- create summaries to describe data.

- create a report layout.

- move `SELECT` statements into packages.

- move packages into a PL/SQL library.

For more information on any of the wizards, views, or properties used in this example, refer to the Oracle Reports online Help, which is available in Reports Builder or hosted on the Oracle Technology Network (OTN), as described in Section 3.1.1, "Using the Oracle Reports online Help".