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: –

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

SQL Server

Oracle (Microsoft provider)

Oracle ODP (Oracle provider)

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 :-