A REF CURSOR
query uses PL/SQL to fetch data. Each REF CURSOR
query is associated
with a PL/SQL function that returns a cursor value from a cursor variable. The
function must ensure that the REF CURSOR
is opened and associated with a SELECT
statement that has a SELECT list that matches the type of the REF CURSOR
.
Oracle Reports supports both static and dynamic REF CURSOR
s. For
example:
Static REF CURSOR
:
open l_rc for SELECT * FROM emp WHERE ename = 'KING';
Dynamic REF CURSOR
:
l_query := SELECT empno, ename, sal, hiredate FROM emp WHERE 1-1';
open l_rc for l_query;
Note: The SELECT
statement must be explicitly
set for dynamic REF CURSOR
s.
For a detailed example, see the chapter titled "Building a Paper Report
with REF CURSORs " in the Oracle Reports Building Reports manual,
available on the Oracle
Technology Network Oracle Reports Documentation page (http://www.oracle.com/technology/documentation/reports.html
).
REF CURSOR
s. For
example:type c1 is REF CURSOR RETURN emp%ROWTYPE;
REF CURSOR
query the child in a data link,
the link can only be a group to group link. It cannot be a column to column
link.
If you use a stored program unit to implement REF CURSOR
s,
you receive the added benefits that go along with storing your program units
in the Oracle database.
You base a query on a REF CURSOR
when you want to:
more easily administer SQL
avoid the use of lexical parameters in your reports
share data sources with other applications, such as Form Builder
increase control and security
encapsulate logic within a subprogram
Furthermore, if you use a stored program unit to implement REF CURSOR
s,
you receive the added benefits that go along with storing your program
units in the Oracle database.
For more information about REF CURSOR
s and stored subprograms, refer
to the PL/SQL User's Guide and Reference.
REF CURSOR
example
/* This package spec defines a REF CURSOR
** type that could be referenced from a
** REF CURSOR query function.
** If creating this spec as a stored
** procedure in a tool such as SQL*Plus,
** you would need to use the CREATE
** PACKAGE command.
*/
PACKAGE cv IS
type comp_rec is RECORD
(deptno number,
ename varchar(10),
compensation number);
type comp_cv is REF CURSOR return comp_rec;
END;
REF CURSOR
and function
/* This package spec and body define a ref
** cursor type as well as a function that
** uses the REF CURSOR to return data.
** The function could be referenced from
** the REF CURSOR query, which would
** greatly simplify the PL/SQL in the
** query itself. If creating this spec
** and body as a stored procedure in a
** tool such as SQL*Plus, you would need
** to use the CREATE PACKAGE and CREATE
** PACKAGE BODY commands.
*/
PACKAGE cv IS
type comp_rec is RECORD
(deptno number,
ename varchar(10),
compensation number);
type comp_cv is REF CURSOR return comp_rec;
function emprefc(deptno1 number) return comp_cv;
END;
PACKAGE BODY cv IS
function emprefc(deptno1 number) return comp_cv is
temp_cv cv.comp_cv;
begin
if deptno1 > 20 then
open temp_cv for select deptno, ename,
1.25*(sal+nvl(comm,0)) compensation
from emp where deptno = deptno1;
else
open temp_cv for select deptno, ename,
1.15*(sal+nvl(comm,0)) compensation
from emp where deptno = deptno1;
end if;
return temp_cv;
end;
END;
REF CURSOR
query
/* This REF CURSOR query function would be coded
** in the query itself. It uses the cv.comp_cv
** REF CURSOR from the cv package to return
** data for the query.
*/
function DS_3RefCurDS return cv.comp_cv is
temp_cv cv.comp_cv;
begin
if :deptno > 20 then
open temp_cv for select deptno, ename,
1.25*(sal+nvl(comm,0)) compensation
from emp where deptno = :deptno;
else
open temp_cv for select deptno, ename,
1.15*(sal+nvl(comm,0)) compensation
from emp where deptno = :deptno;
end if;
return temp_cv;
end;
REF CURSOR
query calling function
/* This REF CURSOR query function would be coded
** in the query itself. It uses the cv.comp_cv
** REF CURSOR and the cv.emprefc function from
** the cv package to return data for the query.
** Because it uses the function from the cv
** package, the logic for the query resides
** mainly within the package. Query
** administration/maintenance can be
** done at the package level (for example,
** modifying SELECT clauses could be done
** by updating the package). You could also
** easily move the package to the database.
** Note this example assumes you have defined
** a user parameter named deptno.
*/
function DS_3RefCurDS return cv.comp_cv is
temp_cv cv.comp_cv;
begin
temp_cv := cv.emprefc(:deptno);
return temp_cv;
end;
Creating a local query: REF CURSOR Query tool
Copyright © 1984, 2005, Oracle. All rights reserved.