Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Links Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
Database Tools
SQL Scripts & Samples
Links
» Database Forum
» Sitemap
Free Newsletters:
DatabaseDaily  
News Via RSS Feed


follow us on Twitter
Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

Oracle

Posted Jun 30, 2004

Using Cursor Variables As Function Arguments on Oracle 9i

By Jim Czuprynski

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



Oracle Archives

Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 




Latest Forum Threads
Oracle Forum
Topic By Replies Updated
Oracle Data Mining: Classification jan.hasller 0 July 5th, 07:19 AM
Find duplicates - Unique IDs Lava 5 July 2nd, 08:30 AM
no matching unique or primary key rcanter 1 April 25th, 12:32 PM
Update values of one table based on condition of values in other table using Trigger Gladiator 3 February 29th, 06:01 PM