Oracle® Fusion Middleware Oracle Reports User's Guide to Building Reports 11g Release 1 (11.1.1) Part Number B32122-02 |
|
|
PDF · Mobi · ePub |
After creating package specs that define the REF CURSOR
s, you are ready to define the queries, as described in this section.
To create a REF CURSOR query:
In the Object Navigator, double-click the view icon next to the Data Model node to display the Data Model view.
In the Data Model view, click the Ref Cursor Query tool in the tool palette.
Click in an open area of the Data Model view to display the PL/SQL Editor for the new REF CURSOR
query.
In the PL/SQL Editor, use the template to enter the following PL/SQL code:
function q_portdescRefCurDS return port_cv.portdesc_refcur is temp_portdesc port_cv.portdesc_refcur; begin open temp_portdesc for select department_id, department_name from departments; return temp_portdesc; end;
Note:
You can open the provided filerefcursor_code.txt
to copy and paste the code into the PL/SQL Editor.Click Compile.
If any compilation errors occur, check the code for syntax errors and recompile as needed.
Click Close.
In the Data Model view, double-click the new REF CURSOR
query object (QR_1) to display the Property Inspector.
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.Repeat the steps above to create two more queries:
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 provided filerefcursor_code.txt
to copy and paste the code into Reports Builder.The Data Model should look similar to the following:
Figure 41-3 Data model with three queries
Save the report as ref_62_
your_initials
.rdf
.
Note:
You can open the provided fileref_emp62.rdf
and display the Data Model to compare your results.