Calling an Oracle Stored Proc from ASP.Net

Here is a basic example of calling an Oracle Stored Proc from ASP.Net using a ref cursor to return the result. Basically, I installed the Oracle Data Provider for .Net downloaded from Oracle (just google "oracle .net"). I used the 11g provider even though I'm on a 9 database (it works fine so far). After setting up a connection to my Oracle database, I dropped a List View onto a page and bound it to a table in the schema just to display some data. Asp.Net does it's magic behind the scenes to compile the necessary subclasses and wire up connections and what not for everything to work. But at this point, the code behind class is basically empty. It turns out you can dig out the connection property that was generated by Asp.Net. Here's a little helper method that reuses the "MyConnection" property to serve up fresh OracleConnections:

protected OClient.OracleConnection getOracleConnection() {
  return new OClient.OracleConnection(MyConnection.ConnectionString);
}

So using this, here's the code to retrieve a value from a ref cursor filled in by a stored proc:

using System;
using System.Data;
using Oracle.DataAccess.Client;
using Oracle.DataAccess.Types;
using OClient = Oracle.DataAccess.Client;                                    // (1)

public partial class Class1 : System.Web.UI.Page {

protected void Page_Load(object sender, EventArgs e) {
    using (OClient.OracleConnection oConn = getOracleConnection()) {         // (2)      
        using (OClient.OracleCommand oCmd = new OClient.OracleCommand()) {

            oCmd.Connection = oConn;
            oCmd.CommandText = "mypackage.sayhello";                         // (3)          
            oCmd.CommandType = System.Data.CommandType.StoredProcedure;      // (4)          

            // Use name binding (instead of positional).  Makes pl/sql parameter           
            // names part of the public api (can't change them without changing           
            // calling code), but moreclear and less error prone than positional.          
            oCmd.BindByName = true;                                          // (5)          

            OClient.OracleParameter inSessionToken = new OClient.OracleParameter(
                    "in_session_token", OClient.OracleDbType.Long, ParameterDirection.Input);

            OClient.OracleParameter outCursor_text = new OClient.OracleParameter(
                    "out_text", OClient.OracleDbType.RefCursor, ParameterDirection.Output);

            // Should be first parameter added (see below)
            oCmd.Parameters.Add(outCursor_text);                             // (6)
            oCmd.Parameters.Add(inSessionToken);

            inSessionToken.Value = 462; // just to show data getting passed in

            oConn.Open();

            Label1.Text = String.Format("MYPACKAGE.SAYHELLO: {0} at {1}",
                    oCmd.ExecuteScalar(), System.DateTime.Now);              // (7)
        }
    }
}
}

Annotations:

1. 1) There are various class name collisions between the Oracle.DataAccess.Client package and the System.Data.OracleClient package, and you need to reference both. Here the "using" alias helps make the code more readable while disambiguaging to the correct classes. Otherwise Oracle.DataAccess.Client would be sprinked throughout the code. It's really just syntactic sugar, but it helps.


2. 2) Here the other (more important) application of "using" is managing the database resources. Note the nested calls for the connection and the command. This just saves boilerplate resource management code. Note that "using" calls IDispose on the underlying object. In the case of ADO, this should either return the connection to the pool (if pooling) or close the connection if not-\-assuming the provider was correctly written.


3. 3) This is where the package.procedure is specified. Of course, you may just be using a procedure directly without a package (just use the procedure name by itself).


4. 4) This is required to let the driver know you are calling a stored proc


5. 5) I'm using named parameters, otherwise you could use positional parameters like :1, :2 , etc.


6. 6) As I explain in the comments, it's safest to just add the ref cursor parameter first for the ExecuteScalar to work properly.


7. 7) Note, that it doesn't make a lot of sense to spin up a ref cursor just to get a single "cell" of data out of it--you'd probably just want to return a varchar2 from a function instead. However, it's a quick way to show that it works. When a single value is all that's needed, ExecuteScalar can be used.
   

Here's what the stored proc looks like:

PACKAGE BODY "MYPACKAGE" IS

PROCEDURE "SAYHELLO" (
in_session_token IN PLS_INTEGER,
out_text OUT sys_refcursor) IS

BEGIN

open out_text for select 'Oracle says "Hello" for session token ' ||
in_session_token from dual;

END "SAYHELLO";

END "MYPACKAGE";

Of course, you'd want a package declaration (not shown, but it basically is just declares the procedures signature).

You can also use the OracleRefCursor class in conjunction with the
OracleDataAdapter class to populate DataTables and DataSets. Also, note that
the ExecuteScalar method for a refcursor must be returned from the function or the must be the first out bind parameter.
Oracle article on using ref cursors with .Net

The ref cursor is then accessed as either an OracleDataReader object or a
DataSet, by use of the OracleDataAdapter class.
Oracle article on using the ways you can handle a ref cursor

If you only need read-only data, use the OracleDataReader to populate a DataSet. If you need offline or updateability, use OracleDataAdapater.