Calling Oracle Stored Procedures with REF CURSOR’s using the MS Enterprise Library DAAB


Calling a stored procedure in Oracle which returns a REF CURSOR requires that parameters have been added to the DBCommandWrapper, the parameter types must be set to OracleDbType.RefCursor (if using the Oracle ODP provider), or OracleType.Cursor (if using the MS Oracle provider).

To return a result-set in SQL Server doesn’t require (or support) cursors, and unfortunatley there is no equivilent DbType enumeration, meaning that if you’re trying to write provider neutral code you’re a little stuck. The solution means either that your client development code must branch based on the type of the XXXDatabase object and add the parameters using typed or casted accessors, or, you extend the DAAB and add appropriate abstract functions to the XXXCommandWrapper objects to allow adding of REF CURSOR parameters. The later solution has the benefit that DAAB client code doesn’t have to branch or do some suspicous casting, on the other hand it means that for SQL Server providers the method call does nothing, which could be confusing.

But then 1 of the points of the DAAB is to achieve provider independence in your client code, so you could say it’s a moot point.

The later is the approach (extending the DAAB) I’ve taken, and from what I’ve seen, the approach many other people have also taken.  To this end I added abstract functions to the XXXCommandWrapper classs and implemented them in provider overrides: –

public override void AddCursorOutParameter(string CursorName)
public override void AddCursorInParameter(string CursorName)
public override void AddCursorInOutParameter(string CursorName)

These functions are overrided in concrete implementations for Oracle and SQL Server: –

SQL Server

public override void AddCursorOutParameter(string CursorName)
{    /** NOOP */    return;    }

public override void AddCursorInParameter(string CursorName)
{    /** NOOP */    return;    }

public
override void AddCursorInOutParameter(string CursorName)
{    /** NOOP */    return;    }

Oracle (Microsoft provider)

public override void AddCursorOutParameter(string CursorName)
{
    this.AddParameter(CursorName, OracleType.Cursor , 0, ParameterDirection.Output, true, 0, 0, String.Empty, DataRowVersion.Default, Convert.DBNull);
}

public override void AddCursorInParameter(string CursorName)
{
    this.AddParameter(CursorName, OracleType.Cursor , 0, ParameterDirection.Input, true, 0, 0, String.Empty, DataRowVersion.Default, Convert.DBNull);
}

public override void AddCursorInOutParameter(string CursorName)
{
    this.AddParameter(CursorName, OracleType.Cursor , 0, ParameterDirection.InputOutput, true, 0, 0, String.Empty, DataRowVersion.Default, Convert.DBNull);
}

Oracle ODP (Oracle provider)

public override void AddCursorOutParameter(string CursorName)
{
    this.AddParameter(CursorName, OracleDbType.RefCursor , 0, ParameterDirection.Output, true, 0, 0, String.Empty, DataRowVersion.Default,
                                   Convert.DBNull);
}

public override void AddCursorInParameter(string CursorName)
{
    this.AddParameter(CursorName, OracleDbType.RefCursor , 0, ParameterDirection.Input, true, 0, 0, String.Empty, DataRowVersion.Default,
                                  Convert.DBNull);
}

public override void AddCursorInOutParameter(string CursorName)
{
    this.AddParameter(CursorName, OracleDbType.RefCursor , 0, ParameterDirection.InputOutput, true, 0, 0, String.Empty, DataRowVersion.Default,
                                 Convert.DBNull);
}

Using these overrides, stored procedures like those shown below can be called in either SQL Server or Oracle;

SQL Server

CREATE PROCEDURE U2_SPGETCLASSES
      @PCLASSID INT = 0
AS
 SET NOCOUNT ON
 SELECT * FROM CLASSES_TABLE
      WHERE CLASSID > @PCLASSID
      ORDER BY 1
 RETURN

Oracle

CREATE OR REPLACE PACKAGE “U2″.”U2PKG” AS

  TYPE CUR_CLASSES IS REF CURSOR RETURN CLASSES_TABLE%ROWTYPE;
END;

CREATE OR REPLACE PROCEDURE “U2″.”U2_SPGETCLASSES”
(
      PCLASSID IN INTEGER DEFAULT 0,
      PC_CLASSES OUT U2PKG.CUR_CLASSES
) AS
BEGIN
  OPEN PC_CLASSES FOR
        SELECT * FROM CLASSES_TABLE
              WHERE CLASSID > PCLASSID
              ORDER BY 1;
END;

Incidentley, in Oracle your stored procedure output parameters can either be type-less (parameter is typed as a REF CURSOR), or typed (parameter is typed as  U2PKG.CUR_CLASSES in the above example).

DAAB Client Code

The DAAB client code shown below can be used to call the stored procedure for either of the providers :-

…..
System.Int32 forClassID = 0;
SomeTypedDataset ds = null;

Database db = DatabaseFactory.CreateDatabase();
DBCommandWrapper cmd = db.GetStoredProcCommandWrapper(“U2_SPGETCLASSES”);
cmd.AddInParameter(“PCLASSID”, DbType.Int32, forClassID);
cmd.AddCursorOutParameter(“PC_CLASSES”);

ds = new SomeTypedDataset();
db.LoadDataSet(cmd, ds, “CLASSES_TABLE”);
….
Or…

…..
System.Int32 forClassID = 0;
SomeTypedDataset ds = null;

Database db = DatabaseFactory.CreateDatabase();
DBCommandWrapper cmd = db.GetStoredProcCommandWrapper(“U2_SPGETCLASSES”);
cmd.AddInParameter(“PCLASSID”, DbType.Int32, forClassID);
cmd.AddCursorOutParameter(“PC_CLASSES”);

using( IDbConnection conn = db.GetConnection() )
using( DbDataAdapter da = db.GetDataAdapter() )
{
    cmd.Command.Connection = conn;
    ((IDbDataAdapter)da).SelectCommand = cmd.Command;

    ds = new SomeTypedDataset();
    da.Fill(ds, “CLASSES_TABLE”);
}

….

Or…

…..
System.Int32 forClassID = 0;
SomeTypedDataset ds = null;

Database db = DatabaseFactory.CreateDatabase();
DBCommandWrapper cmd = db.GetStoredProcCommandWrapper(“U2_SPGETCLASSES”);
cmd.AddInParameter(“PCLASSID”, DbType.Int32, forClassID);
cmd.AddCursorOutParameter(“PC_CLASSES”);

DataSet uds = db.ExecuteDataSet(cmd);
ds = new SomeTypedDataset();
uds.Tables[0].TableName = ds.CLASSES_TABLE.TableName;
ds.Merge(uds);
….

Published by

Phil Harding

SharePoint Consultant, Developer, Father, Husband and Climber.

2 thoughts on “Calling Oracle Stored Procedures with REF CURSOR’s using the MS Enterprise Library DAAB

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s