Data Management

Get Oracle stored procedures to work in ADO.NET

Though the new managed provider for Oracle uses the same basic structure as that for SQL Server, there are minor differences that can trip you up, especially when you're using stored procedures.

With the recent release of the .NET Managed Provider For Oracle, .NET developers finally have a high-performance way to access Oracle data using ADO.NET. In a previous article, I discussed how to connect to and execute SQL statements against Oracle databases using the Oracle managed provider. Now, let’s look at how to get the new provider to work with Oracle stored procedures.

Might not be what you're used to
If you’re accustomed to SQL Server development, you’ll find the Oracle world to be much different. When working with SQL Server, the process of accessing the results of a SQL query is as simple as executing the query and assigning the results to a DataReader, DataSet, or equivalent. The process works in much the same way for stored procedures, because SQL Server returns the results when a command is executed. For reference, the code in Listing A runs a stored procedure named sp_persons and loads the results into a DataSet.

Oracle doesn't follow the SQL Server pattern of returning data. The results of an Oracle query are returned as a ref cursor, which is set as an output parameter in the Oracle stored procedure. So you’ll need to specify a parameter for the ref cursor when accessing Oracle from a .NET application.

Creating parameters with OracleParameter
You’ll use the System.Data.OracleClient.OracleParameter class, which can handle both input and output parameters, to create and configure the parameter. The OracleCommand class contains a Parameters property to which initialized Parameter objects can be added.

Compare the code in Listing A with the example in Listing B, where I establish an Oracle connection and use a stored procedure as the command. Notice that the command type must be set to StoredProcedure; the default is Text, which is useful for executing SQL. Once the command and connection strings are set, I add an output parameter to receive the cursor returned from the procedure. The process of adding the OracleParameter object to the Parameters property of the OracleCommand object requires a type (e.g., Cursor) and a name that corresponds to the name assigned in the actual Oracle stored procedure. I must also set the direction of the parameter using the Direction property. Possible settings are Input, Output,InputOutput, or ReturnValue. In this example, the cursor accepts the output of the procedure, so I set its Direction to Output. Finally, I open an OracleDataReader for the results of the command.

Passing values to a stored procedure
Stored procedures often accept one or more input values that are used to control the results of the procedure. For example, you may not need to return every person record from a database; you instead need to return only one record that matches a supplied value.

The OracleParameter object provides this functionality as well. Listing C illustrates the creation of an input parameter using an OracleParameter object. Here I set the Direction property of the parameter to Input and set the Value property with a value appropriate for the procedure I’m calling. The OracleType enum defines all available Oracle data types, such as char,byte, DateTime, Float, Number, and so on.

Subtle differences can cause headaches
Most developers are accustomed to working with database systems, but moving from one system to another does introduces differences that you must understand in order to develop functional code. The major difference between working with SQL Server and Oracle is the expected return values from stored procedures. Working with the Oracle cursor is easy with the use of the OracleParameter object.

Editor's Picks