6
Building a Report with Ref Cursor Queries

The report described in this chapter is designed to help you learn more about Report Builder features for using ref cursors. To build this report, you will use the Data Model view to create a multi-query 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. In this example, you will create a detailed report showing information about available shipping containers at various ports.

About ref cursor queries

A ref cursor query uses PL/SQL 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 datasources with other applications, such as Form Builder

• increase control and security

• encapsulate logic within a subprogram

Furthermore, 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.

The following figure 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 package from the database allowing you to reuse the package in many reports.

Table 6-1, "Features demonstrated in this Ref Cursor Query sample report", describes the steps you will take to create this report.

The ref.rdf file contains the report you will create after finishing the tasks in this chapter. You may want to refer to this file while you are working. In addition, the port_container.pll is the library file that is associated with ref.rdf. These files are located in your ORACLE_HOME\TOOLS\DOC60\US\RBBR60 directory.

Table 6-1 Features demonstrated in this Ref Cursor Query sample report

Feature	Location
Create package specs that define ref cursors.	Section 6.1, "Defining a ref cursor type"
Create ref cursor queries that will use the ref cursors.	Section 6.2, "Creating a ref cursor query"
Rename objects in the data model so that they have more meaningful names.	Section 6.3, "Refining the data model"
Create group-to-group data links between ref cursor queries to create relationships between them.	Section 6.4, "Creating links between ref cursor queries"
Create summaries that better describe the data.	Section 6.5, "Adding summary columns"
Use the Report Wizard to create a report layout.	Section 6.6, "Creating a layout"
Move the SELECT statements used by the ref cursor queries from the report and into packages that define the ref cursor types.	Section 6.7, "Moving the SELECT statement into a package"
Move the packages into a PL/SQL library so that other reports can share the code.	Section 6.8, "Moving the packages into a library"

To get started, open Report Builder. If the Welcome dialog box appears, click Build a new report manually and click OK. If not, choose File->New->Report. Click Build a new report manually, and click OK.

At some point before you generate the report, you will need to log into the database. Choose File->Connect to connect to the database. Enter the appropriate log on information. See Section 1.3, "Obtaining database access before you start" for details.

6.1 Defining 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 cursors.

For online help on this task, choose Help->Report Builder Help Topics On the Index page, type... query, creating Then click Display to view help topic... Creating a local query: Ref Cursor Query tool

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

2. Click to add a program unit.

3. In the New Program Unit dialog box, type concl_cv as the name of the program unit.

4. Click Package Spec, and click OK.

5. Type the following package spec definition in the editor. The new code that you need to add is in bold:

   PACKAGE concl_cv IS
         type conclass_rec is RECORD 
          (ccap number,
           classid number,
           gwl number,
   

twl number,

        htf number,
        hti number,
        notes varchar(50),
        teu number);
    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.

6. Click Compile.

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

8. Click Close.

9. Repeat steps 2 through 8 to create two more package specs with the following characteristics. New code is displayed in bold:
   • Package Spec Name: cont_cv

     PACKAGE cont_cv IS
         type container_rec is RECORD 
            (title varchar(40),
             dockloc varchar(10),
             portid number,
             repno varchar(10),
             status number,
             key varchar(10),
             key2 varchar(10),
             classid2 number);
         type container_refcur is REF CURSOR return container_rec;
     END;
     

   • Package Spec Name: port_cv

     PACKAGE port_cv IS
       type portdesc_rec is RECORD 
            (portid number,
             locname varchar(10));
       type portdesc_refcur is REF CURSOR return portdesc_rec;
     END;
     
     

10. Choose File->Save As. Save the report in the directory of your choice, and name the report ref_61.rdf.

    Tip: 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 don't like some of the changes you made, you easily can go back to the previously saved file and make revisions from that point.

    6.2 Creating a ref cursor query

    After creating package specs that define the ref cursors, you are ready to define the queries, as described in this section.

    1. In the Object Navigator, double-click to go to the Data Model view.

    2. Click .

    3. Click in the main area (canvas region) of the Data Model view.

    4. In the Program Unit Editor, type in the bold code that follows to define the function. New code is displayed in bold:

    function q_portdescRefCurDS return port_cv.portdesc_refcur istemp_portdesc port_cv.portdesc_refcur;begin open temp_portdesc for select portid, locname from portdesc; return temp_portdesc;end;
    5. Click Compile.

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

    7. Click Close. The data objects display in the Data Model view.

    8. In the Data Model view, click the ref cursor query object (QR_1), then choose Tools->Property Palette.

    9. Under the General Information node, change 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.

    10. Press ENTER or RETURN, or click any other field in the Property Palette to accept the change.

    11. Close the Property Palette.

    12. Repeat steps 2 through 10 to create two more queries with the following characteristics. Be sure to rename the queries using the Property Palette after creating them. New code is displayed in bold:
        • 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 cl.title, c.DOCKLOC, 
                                           c.PORTID, c.REPNO, 
                                           c.STATUS, c.key, 
                                           cl.key key2, c.classid classid2
                                from CONTAINERS c, conlabel cl
                                where cl.key=c.key 
                                order by c.REPNO;
            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 CCAP, CLASSID, GWL, TWL, HTF, HTI, NOTES, 
          TEU
                            from CONCLASS;
                            return temp_concl;
          end;
          

        13. The Data Model should look similar to the following figure:
            

        14. Save the report as ref_62.rdf.

        6.3 Refining 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.

        1. In the Data Model view, drag the title bar of the group G_TITLE down a few inches to move the entire group.

        2. With G_TITLE still selected, choose Tools->Property Palette.

        3. Under the General Information node, change the Name property to
           G_container.

        4. Press ENTER or RETURN, or click any other field in the Property Palette to accept the change.

        5. Close the Property Palette.

        6. Click and drag the column named TITLE out of and above G_container to create a new break group, as shown in the following figure:

           For online help on this task, choose Help->Report Builder Help Topics On the Index page, type... break group, creating Then click Display to view help topic... Creating a break group

           

        7. Click the title bar of the new group (probably named G_TITLE) that contains TITLE, and choose Tools->Property Palette.

        8. Under the General Information node, change the Name property to G_conlabel.

        9. Press ENTER or RETURN, or click any other field in the Property Palette to accept the change.

        10. Close the Property Palette.

        11. Drag and drop KEY2 from G_container to G_conlabel. KEY2 displays in G_conlabel and is removed from G_container.

        12. Click the title bar of G_PORTID and choose Tools->Property Palette.

        13. Change the Name property under the General Information node to G_portdesc.

        14. Press ENTER or RETURN, or click any other field in the Property Palette to accept the change.

        15. Close the Property Palette.

        16. Click the title bar of G_CCAP and choose Tools->Property Palette.

        17. Under the General Information node, change the Name property to
            G_conclass.

        18. Press ENTER or RETURN, or click any other field in the Property Palette to accept the change.

        19. Close the Property Palette.

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

        

        21. Save the report as ref_63.rdf.

        6.4 Creating 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.

        For online help on this task, choose Help->Report Builder Help Topics On the Index page, type... data link, creating Then click Display to view help topic... Creating a data link (Report Builder Help)

        1. In the Data Model view, click .

        2. Click the title bar of G_portdesc, and drag to the title bar of G_container.

        3. Double-click q_container. The Program Unit Editor displays.

        4. Now you will append code to the WHERE clause of the SELECT statement to specify which columns are being used as primary and foreign keys.

           After where cl.key=c.key, add the following code:

           and :portid=c.PORTID
           
           

           Note that :portid is a bind variable referring to the PORTID column in G_portdesc.

        5. Click Compile.

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

        7. Click Close.

        8. Click .

        9. Click the title bar of G_container and drag to the title bar of G_conclass.

        10. Double-click q_conclass.

        11. Now you will add a WHERE clause to the SELECT statement. Insert your cursor between FROM CONCLASS and the semicolon (;), and press ENTER or RETURN to create a new line.

        12. Add the following code:

            where :classid2=conclass.classid
            
            
            

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

        Note that :classid2 is a bind variable referring to the CLASSID2 column in G_container.

    13. Click Compile.

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

    15. Click Close.

    16. Your data model should look similar to the following figure:
        

    17. Save the report as ref_64.rdf.

        6.5 Adding 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.

        For online help on this task, choose Help->Report Builder Help Topics On the Index page, type... summary column, creating Then click Display to view help topic... Creating a summary column

        1. In the Data Model view, click .

        2. Click inside the G_container group. This creates a new column, CS_1.

        3. Double-click the newly created column to open the Property palette.

        4. Under the General Information node, change the Name property to
           CS_classcount.

        5. Under the Summary node, change the following settings:

           Table 6-2 Property settings for the Summary node

           Property	Setting
           Function	Count
           Source	KEY
           Reset At	G_container

        6. Click any other field in the Property Palette to accept the changes.

        7. Close the Property Palette.

           You have now created a summary that counts up the number of containers. 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.

        8. Repeat steps 1 through 5 to create summaries with the following characteristics:

           Table 6-3 Summary properties

           Create in Group	Name	Function	Source	Reset At
           G_conlabel	CS_conlabel_classcount	Sum	CS_classcount	G_conlabel
           G_portdesc	CS_port_count	Sum	CS_conlabel_classcount	G_portdesc

           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:
           

        9. Save the report as ref_65.rdf.

        6.6 Creating a layout

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

        1. Click to bring up the Report Wizard.

        2. On the Style page, type Pacific Intermodal Leasing as the Title.

        3. Click Group Above as the report style.

        4. Click Next.

        5. On the Groups page, click G_conclass and click Down.

        6. Repeat step 5 for:
           • G_container

           • G_conlabel

           • G_portdesc

        7. Click Next.

        8. On the Fields page, click LOCNAME, and click .

        9. Repeat step 8 for:
           • TITLE

           • DOCKLOC

           • REPNO

           • CLASSID2

           • CCAP

           • GWL

           • TWL

           • HTF

           • HTI

           • NOTES

           • CS_conlabel_classcount

           • CS_port_count

        10. Click Next.

        11. On the Labels page, type in the labels and widths as shown in the following table:

            Table 6-4 Labels and widths

            Column	Label	Width
            LOCNAME	Port of:	15
            TITLE	Containers	20
            DOCKLOC	Location	10
            REPNO	ID	10
            CLASSID2	Class	9
            CCAP	Cu capy	9
            GWL	Gross wt	9
            TWL	Tare wt	9
            HTF	Htf	9
            HTI	Hti	9
            NOTES	Notes	15
            CS_conlabel_classcount	Total available:	8
            CS_port_count	Total available:	15

        12. Click Next.

        13. On the Template page, click Predefined template, and choose Cyan Grid Landscape.

        14. Click Finish. The report automatically displays in the Live Previewer:
            

        15. Save the report as ref_66.rdf.

        6.7 Moving the SELECT statement into a package

        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.

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

        2. Click to add a program unit.

        3. In the New Program Unit dialog box, type cont_cv as the name of the program unit.

        4. Click Package Body, and click OK.

        5. Type the following code in the editor. New code is displayed in bold:

           PACKAGE BODY cont_cv IS
           function query_container (p_portid number) return container_refcur is
                 tempcv_container cont_cv.container_refcur;
           begin
           open tempcv_container for select cl.title, c.DOCKLOC, 
                                            c.PORTID, c.REPNO, 
                                            c.STATUS, c.key, 
                                            cl.key key2, c.classid classid2
                                 from CONTAINERS c, conlabel cl
                                 where cl.key=c.key 
                                 and p_portid=c.PORTID
                                 order by c.REPNO;
           return tempcv_container;
           end;
           END;
           

        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.

           In the Object Navigator, double-click the CONT_CV(Package Spec) object.

        10. In the Program Unit editor, type the following line above the END; statement:

            function query_container (p_portid number) return container_refcur;
            
            

        11. Click Close.

        12. Choose Program->Compile->All.

        13. Click OK when done.

        14. In the Object Navigator, double-click the Q_CONTAINERREFCURDS object under the Program Units object.

        15. 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 (:portid);
              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.

        16. Click Compile.

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

        18. Click Close.

        19. Double-click to view the report in the Live Previewer.

        20. Save the report as ref_67.rdf.

        Optional Exercise:

        Repeat steps 1 through 19 for the other two queries in the report.

        6.8 Moving 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 or the database, 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.

        1. In the Object Navigator, click the PL/SQL Libraries object.

        2. Click to add a new library.

        3. Choose File->Save As.

        4. Type PORT_CONTAINER as the Library.

        5. Click File System.

        6. Click OK.

        7. Drag and drop the following program units from your report to the Program Units node under the newly created PORT_CONTAINER library:
           • CONCL_CV(Package Spec)

           • CONT_CV(Package Spec)

           • CONT_CV(Package Body)

           • PORT_CV(Package Spec)

        8. Save PORT_CONTAINER.

        9. If the Live Previewer is open, close it.

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

            Tip: If the Live Previewer is open when you delete the packages from the report, you may get some errors.

        11. Click the Attached Libraries node for your report.

        12. Click to add a new attached library.

        13. In the Attach Library dialog box, click File System.

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

        15. Click Attach.

        16. Choose Program->Compile->All.

        17. Click OK to close the Compile window.

        18. Double-click to view the report.

            Tip: If you get an error when you attempt to view the report, repeat steps 16 through 18.

        19. Save the report as ref_68.rdf.

        Optional Exercise:

        Store the PL/SQL library in the database rather than in a file. Note that you will need "create" privileges on the database to complete this optional exercise.

        6.9 Summary

        Congratulations! You have finished the Ref Cursor Query sample report. You now know how to:

        • Create package specs that define ref cursors.

        • 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 about using ref cursors, see the online help:

        For online help on this topic, choose Help->Report Builder Help Topics On the Index page, type... ref cursor, about Then click Display to view help topic... About ref cursor queries