Oracle Reports Building Reports
Release 6i

A73172-01

Contents

Index

Prev Next

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:

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.

 

  1. For online help on this task, choose Help->Report Builder Help Topics

  2. On the Index page, type...
    query, creating

  3. 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:

  1. Click Compile.

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

  3. Click Close.

  4. 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;
        
        
    • 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;
    1. Click Compile.

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

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

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

    5. 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.

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

    7. Close the Property Palette.

    8. 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;
          

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

      2. 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:

         

        1. For online help on this task, choose Help->Report Builder Help Topics

        2. On the Index page, type...
          break group, creating

        3. 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:

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

       

      1. For online help on this task, choose Help->Report Builder Help Topics

      2. On the Index page, type...
        data link, creating

      3. 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.

      1. Click .

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

      3. Double-click q_conclass.

      4. 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.

      5. 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.

    9. Click Compile.

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

    11. Click Close.

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

    13. 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.

       

      1. For online help on this task, choose Help->Report Builder Help Topics

      2. On the Index page, type...
        summary column, creating

      3. 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 

        CCAP 

        Cu capy 

        GWL 

        Gross wt 

        TWL 

        Tare wt 

        HTF 

        Htf 

        HTI 

        Hti 

        NOTES 

        Notes 

        15 

        CS_conlabel_classcount 

        Total available: 

        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.

      1. Click to add a program unit.

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

      3. Click Package Body, and click OK.

      4. 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;
        
      1. Click Compile.

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

      3. Click Close.

      4. 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.

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

        function query_container (p_portid number) return container_refcur;
        
        
      6. Click Close.

      7. Choose Program->Compile->All.

      8. Click OK when done.

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

      10. 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.

      1. Click Compile.

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

      3. Click Close.

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

      5. 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.

      1. Click to add a new library.

      2. Choose File->Save As.

      3. Type PORT_CONTAINER as the Library.

      4. Click File System.

      5. Click OK.

      6. 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)

      7. Save PORT_CONTAINER.

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

      9. 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.

      10. Click the Attached Libraries node for your report.

      1. Click to add a new attached library.

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

      3. 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.

      4. Click Attach.

      5. Choose Program->Compile->All.

      6. Click OK to close the Compile window.

      7. Double-click to view the report.

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

      8. 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:

       

      1. For online help on this topic, choose Help->Report Builder Help Topics

      2. On the Index page, type...
        ref cursor, about

      3. Then click Display to view help topic...
        About ref cursor queries

       


Prev Next
Oracle
Copyright © 1999 Oracle Corporation.

All Rights Reserved.

Contents

Index