Oracle® Fusion Middleware Oracle Reports User's Guide to Building Reports 11g Release 1 (11.1.1) B32122-01 |
|
![]() Previous |
![]() Next |
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:
In the Object Navigator, click the Program Units node for your report.
Click the Create button in the toolbar to display the New Program Unit dialog box.
In the New Program Unit dialog box, type cont_cv
in the Name field.
Select Package Body, and click OK to display the PL/SQL Editor for the new program unit.
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 filerefcursor_code.txt to copy and paste the code into Reports Builder. |
Click Compile.
If any compilation errors occur, check the code for syntax errors and recompile as needed.
Click Close.
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 to display the PL/SQL Editor.
In the PL/SQL Editor, type the following line above the END;
statement:
function query_container (p_department_id number) return container_refcur;
Click Close.
Choose Program > Compile > All.
Click OK when done.
In the Object Navigator, under the Program Units node, double-click Q_CONTAINERREFCURDS to display the PL/SQL Editor.
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 filerefcursor_code.txt to copy and paste the code into Reports Builder. |
Click Compile.
If any compilation errors occur, check the code for syntax errors and recompile as needed.
Click Close.
Click the Paper Design button in the toolbar to view the report in the Paper Design view.
Save the report as ref_67_
your_initials
.rdf
.
Optional Exercise:
Repeat the above steps for the other two queries in the report.