Oracle Reports Building Reports 10g (9.0.4) Part Number B10602-01 |
|
Reports Builder enables you to easily manage your queries by use of ref cursors. By using a ref cursor, which is a PL/SQL cursor datatype, you can reference a cursor from within a PL/SQL query. For example, if you already have numerous queries built and you want to reuse those queries in your reports, you can simply use a ref cursor in your report data model to access those queries.
In this chapter, you will learn how to use Reports Builder's features for using ref cursors. To build this paper report, you will use the Data Model view to create a multiquery 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.
A ref cursor is a PL/SQL datatype that you can use in a query 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.
In this example, you will create a detailed report showing information about employees and the job position they hold in each department.
To build the examples in this manual, you must have the example files we've provided. If you haven't already done so, you can download the files you'll need to complete this example from the Oracle Technology network and install them on your machine.
http://otn.oracle.com/product/reports/
).
d:\temp
).
d:\orawin90\examples
).
This zip file contains the following files:
If you don't know if you have access to the sample Human Resources schema provided with the Oracle9i database, contact your database administrator.
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.
concl_cv
in the Name field.
PACKAGE concl_cv IS type conclass_rec is RECORD (EMPLOYEE_ID NUMBER(6), FIRST_NAME VARCHAR2(20), LAST_NAME VARCHAR2(25), e-mail VARCHAR2(25), PHONE_NUMBER VARCHAR2(20), HIRE_DATE DATE, JOB_ID VARCHAR2(10), SALARY NUMBER(8,2), DEPARTMENT_ID NUMBER(4)); type conclass_refcur is REF CURSOR return conclass_rec; END;
This package spec does two things:
PACKAGE cont_cv IS type container_rec is RECORD (EMPLOYEE_ID NUMBER(6), START_DATE DATE, END_DATE DATE, JOB_ID VARCHAR2(10), DEPARTMENT_ID NUMBER(4)); type container_refcur is REF CURSOR return container_rec; END;
ref61_<your initials>.rdf
.
After creating package specs that define the ref cursors, you are ready to define the queries, as described in this section.
RefCurDS return
port_cv.portdesc_refcur istemp_portdesc port_cv.portdesc_refcur;begin
open temp_portdesc for select department_id, department_name from departments; return temp_portdesc;end;
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;
The Data Model should look similar to the following:
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.
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:
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.
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.
Now that your queries are complete and linked, the steps in this section will help you to create columns to summarize the data.
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.
Table 38-3 Summary Characteristics
Create in Group | Name | Function | Source | Reset At |
---|---|---|---|---|
G_conlabel |
CS_conlabel_classcount |
Sum |
CS_classcount |
G_conlabel |
G_department_id |
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:
Now that you have a working data model, the steps in this section will help you to create a layout.
My Employees
in the Title field, select Group Above.
Table 38-4 Field description of Labels page
Fields | Labels | Width |
---|---|---|
DEPARTMENT_NAME |
|
|
EMPLOYEE_ID1 |
|
|
START_DATE |
|
|
END_DATE |
|
|
JOB_ID |
|
|
CS_port_count |
|
|
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.
cont_cv
as in the Name field.
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;
END;
statement:
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.
ref_67_<your initials>.rdf
.
Optional Exercise:
Repeat the above steps for the other two queries in the report.
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:
DEPT_CONTAINER
as the Library.
.PLL
file extension. After you have found and selected DEPT_CONTAINER, click Open.
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.
Congratulations! You have finished the Ref Cursor Query sample report. You now know how to:
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:
http://otn.oracle.com/products/reports/
), click Documentation and navigate to the Reports Builder Online Help for the most recent, hosted online help.
|
![]() Copyright © 2002, 2003 Oracle Corporation. All Rights Reserved. |
|