Get value of Oracle OUT parameter from a stored procedure call using Dapper.NET

Question

Edit: Using the Execute method instead of the Query/QueryMultiple methods, my OUT_SUCCESS parameter now has an AttachedParam with with an OracleParameter that has the returned value. So this would work if, for instance, I only needed to retrieve non-cursors parameters. Then I could use Execute for procedures with all non-cursor output parameters and Query/QueryMultiple for procedures with only cursor output parameters. But what if I need to call a stored procedure that has both cursor and non-cursor output parameters, as is often the case?

Using Dapper.NET and the OracleDynamicParameters class I have successfully returned and mapped multiple IN OUT REF CURSORs, but I cannot get the value a single OUT parameter.

For instance, I am trying to call a stored procedure with the following spec:

PROCEDURE DO_SOMETHING (
    OUT_SUCCESS    OUT VARCHAR2
)

for which I have created a corresponding C# class to model it, which contains methods to get the OracleDynamicParameters, the CommandText, and so forth, and also includes an auto-implemented property for each parameter

public class DO_SOMETHING {
    ... //code to return parameters, etc
    public string OUT_SUCCESS { get; set; }
    ...
}

and I have tried all of the following syntax:

using (var gridReader = Connection.QueryMultiple(nModel.CommandText(), param: nModel.DynamicParameters(), commandType: nModel.CommandType()))
{
     OUT_SUCCESS = ((OracleDynamicParameters)Model.DynamicParameters()).Get<string>("OUT_SUCCESS"); // 1
     OUT_SUCCESS = gridReader.Read<string>().Single(); //2
     OUT_SUCCESS = gridReader.Read<DO_SOMETHING>().Single().OUT_SUCCESS; //3
}

but none of them work:

1. The AttachedParam is null for the parameter with name "OUT_SUCCESS" (although I can see the parameter exists)
2. The gridReader reports that the "sequence contains no elements", probably because it has no idea how to read a string out of the response.
3. This one seems the most promising - InvalidArgumentException: the gridReader advises me that "When using the multi-mapping APIs ensure you set the splitOn param if you have keys other than Id", but I'm not really sure how this is pertinent to my problem.

By the way, I do know that the procedure is successfully executing because ODP.NET does not produce an exception and I see the resulting rows persisted in the database.

I don't know how to proceed, but I'd really like to use Dapper, as this is the last remaining hurdle to climb. Any help is always appreciated.

Answer 1

I know this is extremely late and may be common knowledge to most everyone but me, but there is a comment in the original message from not too long ago, so I'll describe how I got around the issue of having a cursor out parameter as well as non-cursor out parameters.

Since my example only has one Oracle cursor, I can user the Query method. The results of the other out parameters are in the parameters themselves and can be retrieved with the Get<> method in the OracleDynamicParameters class.

The other important part for me was to add a size to my out parameters, otherwise they were coming back as null or empty strings.

Below is a sample of the code I'm using.

using (IDbConnection db = new OracleConnection(connectionString)) {
    var p = new OracleDynamicParameters();
    p.Add("p_first_parameter", someParameter, OracleDbType.Varchar2, ParameterDirection.Input);
    p.Add("o_cursr", dbType: OracleDbType.RefCursor, direction: ParameterDirection.Output);
    p.Add("o_sqlerrm", dbType: OracleDbType.Varchar2, direction: ParameterDirection.Output, size: 200);
    p.Add("o_sqlcode", dbType: OracleDbType.Varchar2, direction: ParameterDirection.Output, size: 200);

    dynamic csr = db.Query("myStoredProcedure", p, commandType: CommandType.StoredProcedure).ToList().First();
    string code = p.Get<OracleString>("o_sqlcode").ToString();
    if (code != "0") {
        string errm = p.Get<OracleString>("o_sqlerrm").ToString();
        throw new Exception($"{code} - {errm}");
    }
}