7
Building a Paper Report with Ref Cursors

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

• 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 7-3 Data model with three queries

Text description of the illustration orbrref_62.gif

7.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 a few inches 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 7-4 Query with group

Text description of the illustration orbrref_gp.gif

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

Figure 7-5 Data model with group

Text description of the illustration orbrref_63.gif

7.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 7-6 Data model with links

Text description of the illustration orbrref_64.gif

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

   G_conlabel	CS_conlabel_classcount	Sum	CS_classcount	G_conlabel
   G_department	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 7-7 Data model with summary columns

Text description of the illustration orbrref_65.gif

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

   • 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:

   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 Cyan Grid Landscape, then click Finish to display your report output in the Paper Design view. It should look something like this:

Figure 7-8 Paper Design view for the ref cursor report

Text description of the illustration orbrref_66.gif

7.8 Move 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.

To move the SELECT statement 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 as 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:

open tempcv_container for

select employee_id,
start_date,
end_date,
ob_id,
department_id
from   job_history
where  :department_id=department_id;

return tempcv_container;

function query_container (p_department_id number) return container_refcur;

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.

Optional Exercise:

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

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

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. 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 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. In the Attach Library dialog box, click File System.

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

13. Click Attach.

14. Choose Program > Compile > All.

15. Click OK to close the Compile window.

16. Click the Paper Design button in the toolbar to run the report and view it in the Paper Design view.

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

17. Save the report as ref_68_<your initials>.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.

7.10 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 on any of the wizards, views, or properties used in this example, refer to the Reports Builder online help, which you can access in two ways:

• From the Oracle Technology Network (http://otn.oracle.com/products/reports/), click Documentation and navigate to the Oracle9i Reports Builder Online Help for the most recent, hosted online help.

• From Reports Builder, choose Help > Help Contents.

  Note: You can replace your Reports Builder online help with the most recent update by downloading the latest online help set available on the Oracle Technology Network (http://otn.oracle.com/docs/products/reports/). Instructions for replacing your help file are included in the readme.txt in the download file.