41.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 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 provided file 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.