Using Cursor Variables As Function Arguments on Oracle 9i

June 30, 2004

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








The Network for Technology Professionals

Search:

About Internet.com

Legal Notices, Licensing, Permissions, Privacy Policy.
Advertise | Newsletters | E-mail Offers