Using Dapper with Oracle stored procedures which return cursors

Thanks for the solution here. I achieved the same thing with a little less code using a simple DynamicParameter decorator:

public class OracleDynamicParameters : SqlMapper.IDynamicParameters{    private readonly DynamicParameters dynamicParameters = new DynamicParameters();    private readonly List<OracleParameter> oracleParameters = new List<OracleParameter>();    public void Add(string name, object value = null, DbType? dbType = null, ParameterDirection? direction = null, int? size = null)    {        dynamicParameters.Add(name, value, dbType, direction, size);    }    public void Add(string name, OracleDbType oracleDbType, ParameterDirection direction)    {        var oracleParameter = new OracleParameter(name, oracleDbType, direction);        oracleParameters.Add(oracleParameter);    }    public void AddParameters(IDbCommand command, SqlMapper.Identity identity)    {        ((SqlMapper.IDynamicParameters)dynamicParameters).AddParameters(command, identity);        var oracleCommand = command as OracleCommand;        if (oracleCommand != null)        {            oracleCommand.Parameters.AddRange(oracleParameters.ToArray());        }    }}

You would have to implement:

 public interface IDynamicParameters {    void AddParameters(IDbCommand command, Identity identity); }

Then in the AddParameters callback you would cast the IDbCommand to an OracleCommand and add the DB specific params.

Add this class to your project

and your code should like below :-

        var p = new OracleDynamicParameters();        p.Add("param1", pAuditType);        p.Add("param2", pCommnId);        p.Add("outCursor", dbType: OracleDbType.RefCursor, direction: ParameterDirection.Output);        using (var multi = cnn.QueryMultiple("procedure_name", param: p, commandType: CommandType.StoredProcedure))        {            var data = multi.Read();            return data;        }