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
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.
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
. . .
. . .
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
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))
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
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.
1.4 provides the prototype code for a standard code table function, and
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.)
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