Using Cursor Variables As Function Arguments on Oracle 9i

Synopsis. Oracle 9i offers the ability to pass
data to a PL/SQL function in the form of a cursor variable. When this feature
is paired with the concept of the table function, the resulting PL/SQL code
increases significantly in flexibility and scalability. This article builds
upon the author’s previous article on table functions and provides a brief
technical explanation of how best to implement this feature.

In my ongoing attempts to clean up the use of global
temporary tables in our production Oracle database, I have been refining a set
of best practices for creating PL/SQL table functions. (Please see my last
article, CASTing
About For A Solution: Using CAST and Table Functions in PL/SQL
for a
detailed description of the business problem I am solving.)

One of the more interesting features of Oracle 9i is the
ability to pass arguments to procedures and functions in the form of a cursor
variable
. As its name implies, I can define the input argument as reference
cursor, populate it and pass the populated rows to the function or procedure.

Strongly-Typed Reference Cursors

A strongly-typed reference cursor is one whose result
set is a predefined list of variables defined by the %ROWTYPE designation. A strongly-typed
cursor is usually declared within the bounds of a package specification, and
this definition is then referenced within the function’s signature to define
the incoming reference cursor as a variable.

Weakly-Typed Reference Cursors and SYS_REFCURSOR

A weakly-typed reference cursor, on the other hand,
may be defined either in a package specification by leaving out the %ROWTYPE specifier,
or “on the fly” by signing it the special SYS_REFCURSOR directive. The
advantage of a weakly-defined reference cursor is that I don’t need a package
specification to define it ahead of time. However, I do need to insure that the
reference cursors that I pass to a table function match what that function
expects to process.

See Listing
1.1
for an example of declaring both types of reference cursors.

Reference Cursors As Arguments

Strongly-typed or weakly-typed reference cursors can be
passed directly as parameters to a packaged function, procedure, stored
procedure, or stored function. In addition, multiple reference cursors can be
passed as arguments. Here is a sample declaration for a new table function:


CREATE OR REPLACE FUNCTION sf_get_max_division_salaries(
arc_divisions IN hr.pkg_ref_cursors.divisions_rc
,arc_salaries IN SYS_REFCURSOR
)
RETURN division_salary_t PIPELINED
IS
BEGIN
. . .
{function body}
. . .
RETURN;
END;
/

See Listing
1.2
for an example of a new table function, sf_get_max_division_salary, for a detailed
example of declaring both a strongly-typed and a weakly-typed reference cursor
as input arguments to a table function.

Passing Reference Cursors Using CURSOR

Passing reference cursors as arguments to functions is
simple and yet flexible. To pass a SELECT statement into a reference cursor,
simply wrap it in the CURSOR directive. Here is an example for the function
above:


SELECT *
FROM
TABLE(
CAST(
sf_get_max_division_salaries(
CURSOR(SELECT * FROM hr.divisions),
CURSOR(SELECT D.division_id, MAX(E.salary)
FROM hr.employees E, hr.departments D
WHERE E.department_id = D.department_id
GROUP BY D.division_id))
AS division_salary_t
)
);

Listing
1.3
shows a ready-to-execute example of how the sf_get_max_division_salary function would be
called to return a list of the maximum salary within each division of the
company.

A Practical Example: Code Table Generator

The prior example illustrates how to provide multiple
reference cursor arguments to one table function. One of my other recent
deliverables involved providing a flexible, extendable code table generator for
my development team.

Our Powerbuilder client-server applications and web-based
Microsoft .NET applications use drop-down list boxes extensively to customize a
user’s environment. A user from Company A that accesses our web-based
application needs to have a different look and feel for his web page than does
a user from Company B. Moreover, what makes this more complex is that one of
our standard product offerings may be called two different products by either
Company A or Company B.

A code table generator would make my developers’
coding efforts much simpler: All their code would have to do is provide the
appropriate SELECT statement against the proper table(s) using the correct
syntax, pass that SELECT statement into a table function, and receive a
properly-formatted, standard result set.

Listing
1.4
provides the prototype code for a standard code table function, and
Listing
1.5
shows three examples of how the function can be called with
different SELECT statements to return one standard set of code table entries.
(I have utilized the DIVISION, DEPARTMENT and EMPLOYEE entities of the HR
schema in the demo database to provide a simple example.)

Conclusion

Cursor variables provide increased flexibility and
scalability, especially for PL/SQL table functions, in Oracle 9i. My PL/SQL
development “tool belt” is stronger with the addition of this slick set of
features, and I encourage all Oracle developers to experiment with them.

References and Additional Reading

While there is no substitute for direct experience, reading
the manual is not a bad idea, either. I have drawn upon the following Oracle 9i
Release 2 documentation for the deeper technical details of this article:

A96624-01 Oracle 9i PL/SQL
User’s Guide and Reference,
Chapter 8

»


See All Articles by Columnist
Jim Czuprynski

Jim Czuprynski
Jim Czuprynski has accumulated over 30 years of experience during his information technology career. He has filled diverse roles at several Fortune 1000 companies in those three decades - mainframe programmer, applications developer, business analyst, and project manager - before becoming an Oracle database administrator in 2001. He currently holds OCP certification for Oracle 9i, 10g and 11g. Jim teaches the core Oracle University database administration courses on behalf of Oracle and its Education Partners throughout the United States and Canada, instructing several hundred Oracle DBAs since 2005. He was selected as Oracle Education Partner Instructor of the Year in 2009. Jim resides in Bartlett, Illinois, USA with his wife Ruth, whose career as a project manager and software quality assurance manager for a multinational insurance company makes for interesting marital discussions. He enjoys cross-country skiing, biking, bird watching, and writing about his life experiences in the field of information technology.

Latest Articles