3

Given this oracle stored proc package:

CREATE OR REPLACE 
PACKAGE TESTPKG AS 
  FUNCTION TestReturnRefCursor RETURN SYS_REFCURSOR;
  PROCEDURE TestingRefCursorsAsOutParam (outp OUT SYS_REFCURSOR);
END TESTPKG

And this package body:

CREATE OR REPLACE
PACKAGE BODY TESTPKG AS

  FUNCTION TestReturnRefCursor RETURN SYS_REFCURSOR AS
    outp SYS_REFCURSOR;
  BEGIN
    OPEN outp FOR SELECT * FROM TABLENAME;
    RETURN outp; 
  END TestReturnRefCursor;

  PROCEDURE TestingRefCursorsAsOutParam (outp OUT SYS_REFCURSOR) AS 
  BEGIN
    OPEN outp FOR SELECT * FROM TABLENAME;
  END TestingRefCursorsAsOutParam;

END TESTPKG;

I am trying to use Dapper to call either function. I have created the OracleDynamicParameters class that implements Dapper.SqlMapper.IDynamicParameters that I found at this post after searching StackOverflow: https://gist.github.com/vijaysg/3096151

Calling the TestReturnRefCursor stored function works fine with this code:

        var conn = new OracleConnection(ConfigurationManager.ConnectionStrings["DataConnection"].ConnectionString);
        conn.Open();
        var p = new OracleDynamicParameters();
        p.Add("retSet", dbType: OracleDbType.RefCursor, direction: ParameterDirection.ReturnValue);
        conn.Execute("TESTPKG.TestReturnRefCursor ", param: p, commandType: CommandType.StoredProcedure);
        var refcur = p.Get<OracleRefCursor>("retSet");
        // do something with refcur
        conn.Close();

However, when I try to call the TestingRefCursorsAsOutParam stored procedure with this code:

var conn = new OracleConnection(ConfigurationManager.ConnectionStrings["DataConnection"].ConnectionString);
conn.Open();
var p = new OracleDynamicParameters();
p.Add("retSet", dbType: OracleDbType.RefCursor, direction: ParameterDirection.Output);
conn.Execute("TESTPKG.TestingRefCursorsAsOutParam ", param: p, commandType: CommandType.StoredProcedure);
var refcur = p.Get<OracleRefCursor>("retSet");
// do something with refcur
conn.Close();

I get an OracleException from the Oracle.ManagedDataAccess.dll with the detail:

"ORA-06550: line 1, column 7:\nPLS-00306: wrong number or types of arguments in call to 'TESTINGREFCURSORSASOUTPARAM'\nORA-06550: line 1, column 7:\nPL/SQL: Statement ignored"

I found after searching another stackoverflow post where sometimes parameters aren't bound if they are null unless you specifically set their value to be DBNull.Value, so I tried changing the add parameter line to be:

p.Add("retSet", value: DBNull.Value, dbType: OracleDbType.RefCursor, direction: ParameterDirection.Output);

but this did not help.

I can call this stored procedure just fine using regular c# oracle code:

var cmd = conn.CreateCommand();
cmd.CommandText = "TESTPKG.TestingRefCursorsAsOutParam";
cmd.CommandType = CommandType.StoredProcedure;

var outparam = new OracleParameter();
outparam.ParameterName = "retSet";
outparam.Direction = ParameterDirection.Output;
outparam.OracleDbType = OracleDbType.RefCursor;

cmd.Parameters.Add(outparam);

OracleDataAdapter da = new OracleDataAdapter(cmd);
cmd.ExecuteNonQuery();

var refcur = (OracleRefCursor)cmd.Parameters[0].Value;

Is there something I am missing here, or is Dapper not functioning correctly?

casperOne
  • 73,706
  • 19
  • 184
  • 253
J.T. Dorion
  • 33
  • 1
  • 5
  • I don't have oracle handy to test, but I *suspect* the issue is that your parameter bag isn't implementing `IParameterCallbacks` to check the updated parameter values. Is there any reason that you can't just use the pre-rolled `DynamicParameters` class? – Marc Gravell Dec 09 '15 at 09:13
  • Thanks for you reply, Marc! When I went to use Dapper to call a function/procedure that is using a refcursor, I did not find Dbtype.Refcursor in the list of options. I then did a quick stackoverflow search and found this: http://stackoverflow.com/questions/7390015/using-dapper-with-oracle-stored-procedures-which-return-cursors Which led me to believe the OracleDynamicParameters class was required. I am using the default strong (1.4) version of Dapper that you get using NuGet. – J.T. Dorion Dec 09 '15 at 14:00
  • If I change the OracleDynamicParameters to DynamicParameters and the add param call to this (omitting the dbtype declaration): `code`p.Add("retSet", direction: ParameterDirection.Output);`code` I still get the exact same error message as above - "wrong number or types of arguments". – J.T. Dorion Dec 09 '15 at 14:00
  • It is very hard for me to debug this, as I don't have Oracle; but the key thing here is line 171 on the gist; **is that executed?** It is the `AddParameters` method that is in change of adding the parameters. – Marc Gravell Dec 09 '15 at 15:02
  • btw; there's probably a cleaner way of implementing this, by writing a type handler for `OracleRefCursor` - [a bit like this](https://github.com/StackExchange/dapper-dot-net/blob/master/Dapper.EntityFramework/DbGeographyHandler.cs), but simpler (in that code there are two different representations - the `SqlGeography` that the DB provider uses, and the `DbGeography` that EF uses; all you really need to configure is the `OracleDbType`) – Marc Gravell Dec 09 '15 at 15:15
  • I looked at the source for Dynamic Parameters and implemented both IParameterCallbacks and and IParameterLookup, essentially copy and pasting the code there. I also tried creating the type handler for OracleRefCursor. I looked through the tests to figure out how and registered the type handler, but I think part of the issue may be that you can't instantiate an OracleRefCursor on your own to attach it to the parameter- it's created by the OracleCommand. – J.T. Dorion Dec 18 '15 at 16:10

1 Answers1

2

If you do not want to use a strong type for your result using dapper (i.e: Query<YourStrongType> ), you can use Query<dynamic>, the code below should work:

 IEnumerable<dynamic> results = null;
 using (var conn = new OracleConnection(ConfigurationManager.ConnectionStrings["DataConnection"].ConnectionString))
{
    var p = new OracleDynamicParameters();
    p.Add("outp ", dbType: OracleDbType.RefCursor, direction: ParameterDirection.Output);
    results = conn.Query<dynamic>("TESTPKG.TestingRefCursorsAsOutParam", p, commandType: CommandType.StoredProcedure);

    foreach (var row in results)
    {
         var fields = row as IDictionary<string, object>;
    }
}
omaraguirre
  • 154
  • 5