As Published In
Oracle Magazine
January/February 2006

DEVELOPER: ODP.NET


Cursor In, Cursor Out

By Mark A. Williams Oracle ACE

The easy way to send a ref cursor to PL/SQL with ODP.NET and Oracle Database 10g Release 2

A ref cursor is a reference to a result set that resides in server memory. When a ref cursor is opened, no data is initially returned to the client. Instead, the address where the data resides is passed to the client and the client can choose how and when to process the data represented by the ref cursor.

With previous releases of ODP.NET, you could retrieve data from a ref cursor but you could not pass a ref cursor as an input parameter to a PL/SQL stored procedure or function. But with ODP.NET 10g Release 2, you are now able to easily pass a ref cursor as an input parameter in Oracle Database 10g Release 2.

This ODP.NET column focuses on how to send a ref cursor to a PL/SQL stored procedure in a .NET application that accesses Oracle Database 10g Release 2.

Sample Application Requirements

The sample application code that accompanies this column simulates a user selecting a subset of employees from an employees table. The subset of employees selected is represented by a ref cursor—the actual rows are never passed from the database to the client application, just the row addresses. After the employees have been selected, the ref cursor is passed to a stored procedure for processing. In this case, the processing simply inserts a message into a table.

This sample application uses the HR sample schema.

Implementing the Database Objects

Before you create the .NET code to pass a ref cursor to a stored procedure, you create the underlying database objects to support this activity. Although you can create the .NET code first, it is easiest to first create the database objects the .NET code will use. The three database objects to create are 

  • A table
  • A PL/SQL package
  • A PL/SQL package body


The table holds the results of the processing that occurs in the PL/SQL package body. The PL/SQL package defines the ref cursor and contains the definition of the stored procedure that processes it. The stored procedure logic is located in the PL/SQL package body and performs the bulk of the work, which, in this case, is simply to loop through the rows in the ref cursor and insert a simple message into the table. The code in database.sql, available with the sample application download, creates the database objects.

Creating the .NET Code

With the database objects created, you now create a new .NET console application and add a reference to the ODP.NET assembly to your project, by choosing Project->Add Reference from the Microsoft Visual Studio menu bar and then choosing Oracle.DataAccess.dll from the Add Reference dialog box.

As well as adding a reference to the ODP.NET assembly, I always add a standard set of namespaces to each of my projects, to reduce the amount of application code, by including the following code at the beginning of my class file: 

using System;
using System.Data;
using Oracle.DataAccess.Client;
using Oracle.DataAccess.Types;


The Oracle.DataAccess.Types namespaces contain Oracle-specific datatypes available in .NET, such as the ref cursor.

Code Listing 1: The .NET main method—creating and passing a ref cursor

 

static void Main(string[] args)
{
  string constr = "User Id=hr; Password=hr;
    Data Source=oramag; Pooling=false";

  OracleConnection con = new OracleConnection(constr);
  con.Open();

  OracleCommand cmd = con.CreateCommand();

  cmd.CommandText = "begin open :1 for
    select * from employees
    where manager_id=101; end;";

  OracleParameter p_rc = cmd.Parameters.Add(
    "p_rc",
    OracleDbType.RefCursor,
    DBNull.Value,
    ParameterDirection.Output);

  cmd.ExecuteNonQuery();

  cmd.Parameters.Clear();

  cmd.CommandText = "cursor_in_out.process_cursor";
  
  cmd.CommandType = CommandType.StoredProcedure;

  OracleParameter p_input = cmd.Parameters.Add(
    "p_input",
    OracleDbType.RefCursor,
    p_rc.Value,
    ParameterDirection.Input);

  cmd.ExecuteNonQuery();

  p_input.Dispose();
  p_rc.Dispose();
  cmd.Dispose();
  con.Dispose();
}


The code in Listing 1 contains the main method to replace the main method that Microsoft Visual Studio creates when you build a new console application. This new code simulates a user selecting a subset of rows from a table.

The following are key points in the code in Listing 1: 

cmd.CommandText = "begin open :1 for
  select * from employees
  where manager_id=101; end;";


—sets the CommandText property of the OracleCommand object to an anonymous PL/SQL block that creates a ref cursor. The anonymous block is delineated by the begin and end keywords (but no name), and it uses a bind variable ( :1 ) for the ref cursor parameter. The ref cursor is created by the open keyword, and the contents of the cursor are determined by the simple SQL statement that retrieves all the employees whose manager_id is 101 . I am using the literal value 101 in the code for this column; you can use a bind variable in your applications.

Next Steps


READ more about ODP.NET
Oracle Data Provider for .NET Developer's Guide
"Optimize Result Set Retrieval Using ODP.NET and Ref Cursors"
Pro .NET Oracle Programming

DOWNLOAD
sample application for this column
ODP.NET 10g

 

OracleParameter p_rc = 
  cmd.Parameters.Add(
  "p_rc",
  OracleDbType.RefCursor,
  DBNull.Value,
  ParameterDirection.Output);


—creates an OracleParameter object, p_rc, that represents the ref cursor in the .NET code. You need to declare the parameter to be of type OracleDbType.RefCursor . (See the Oracle Data Provider for .NET Developer's Guide for more details about the ref cursor ODP.NET datatype.) The initial value of the parameter is set to null ( DBNull.Value ), and the direction is set to output. 

cmd.CommandText = 
"cursor_in_out.process_cursor";
cmd.CommandType = 
CommandType.StoredProcedure;


—assigns values to the CommandText and CommandType properties of the OracleCommand object, to invoke the PL/SQL stored procedure that processes the ref cursor. The CommandText property is set to the name of the stored procedure (including the package name as a prefix), and the CommandType property is set to indicate that a stored procedure, rather than a standalone SQL statement, is being used. Note that if you were to call a stored function rather than a procedure, you would still declare the command to be a StoredProcedure type. 

OracleParameter p_input = 
  cmd.Parameters.Add(
  "p_input",
  OracleDbType.RefCursor,
  p_rc.Value,
  ParameterDirection.Input);


—creates an OracleParameter object, p_input , that passes the ref cursor into the PL/SQL stored procedure. Note that the parameter is an input parameter ( ParameterDirection.Input ).

Seeing the Code in Action

To see the results of the code, choose Build->Build Solution from the Microsoft Visual Studio .NET main menu and run the project by clicking on the Start button. The application produces no visible output, but you can easily see the results by using a query tool such as the SQL Query Window in Oracle Developer Tools for Visual Studio .NET or SQL*Plus. Whichever tool you use, you should see a result similar to the following

SQL> select * from processing_result;
STATUS
----------------------------------------
Processed employee #108: Nancy Greenberg
Processed employee #200: Jennifer Whalen
Processed employee #203: Susan Mavris
Processed employee #204: Hermann Baer
Processed employee #205: Shelley Higgins

5 rows selected.


Summary

This column showed you how to create both the database objects and the .NET code to produce an output ref cursor and how to pass that ref cursor as an input parameter to a PL/SQL stored procedure. ODP.NET makes working with input ref cursors just as easy as working with any other ODP.NET and Oracle type. I encourage you to expand this simple example to leverage the power of ODP.NET, PL/SQL stored procedures, ref cursors, and Oracle Database 10g Release 2 in your applications today.


Mark A. Williams (mawilliams@cheshamdbs.com) is an Oracle ACE, Oracle Certified Professional DBA, and the author of Pro .NET Oracle Programming (Apress, 2004).


Send us your comments