Dapper & Oracle Clob type

Question

I am using dapper to do some oracle access. I have a scenario where I have to have an output parameter with a type of OracleDbType.Clob. As I am using dapper and thus using the base DbType enumeration I am using the DbType.Object enum as suggested here http://docs.oracle.com/html/B14164_01/featOraCommand.htm to stand in for OracleDbType.Clob.

However, this sets the command parameter (deep down in dapper) to be of DbType object and oracle type Blob (as the DbConnection providers a concrete OracleParameter). The problem being this Oracle proc only works if this parameter is of type Clob not Blob.

The pure ADO code works like a charm (Using OracleParameter and OracleConnection etc) but there appears to be no way to set the concrete type or hook into this DbParameter creation process in dapper to change this OracleType on the returned CommandParameter?

This works:

using (OracleConnection conn = new OracleConnection("some connection string"))
{
      conn.Open();
      var cmd = new OracleCommand("ProcName", conn);
      cmd.CommandType = CommandType.StoredProcedure;
      var paramOne = new OracleParameter("ReturnValue", OracleDbType.Clob, int.MaxValue, null, ParameterDirection.Output);
      cmd.Parameters.Add(paramOne);
      cmd.ExecuteNonQuery();
      var value1 = paramOne.Value;
 }

This fails:

DynamicParameters dyanmicParameters = new DynamicParameters();
dyanmicParameters.Add("ReturnValue", null, DbType.Object, ParameterDirection.Output);
connection.Execute("ProcName", dyanmicParameters, commandType: CommandType.StoredProcedure);
var val = dynamicParameters.Get<object>("ReturnValue");

Any Ideas??

Thanks,

Jon

Featherstitch

Answer 1

I know you asked this a long time ago. However I've encountered the same issue with a different database type.

Basically your running into one of the issues with Dapper. It's a micro-orm, thats somewhat opinionated about how things should work. It seems to have been written primarily with MS SQL Server in mind, even though it claims it works with any database type. Which for the most part is true, however when you start to get to more esoteric data types such as Clob's, Blob's, Geospatial, etc things start to break down as you have seen.

The only way around this would be to create a custom Query parameter. You can look at the ICustomQueryParameter source for an example here: https://github.com/SamSaffron/dapper-dot-net/blob/master/Dapper%20NET40/SqlMapper.cs

Go down to this line:

sealed partial class DbString : Dapper.SqlMapper.ICustomQueryParameter

You would basically write your own that uses OracleDbType.Clob and then use it like this:

Query<Thing>("select * from Thing where Name = @Name", new { Name = new OracleClob { Value = "abcde" } });

Converted

Answer 2

I found this vijaysg / OracleDynamicParameters.cs

It creates OracleDynamicParameters class implements IDynamicParameters interface.

Here is how to use it

Sample:

PROCEDURE GetUserDetailsForPIDM (i_id    IN   NUMBER,
                o_user           OUT SYS_REFCURSOR,
                o_roles          OUT SYS_REFCURSOR);

and how to call it with dapper

public static User GetUserDetailsByID( int ID ) {
    User u = null;
    using ( OracleConnection cnn = new OracleConnection( ConnectionString ) ) {
        cnn.Open( );
        var p = new OracleDynamicParameters( );
        p.Add( "i_id", ID );
        p.Add( "o_user", dbType:OracleDbType.RefCursor, direction: ParameterDirection.Output );
        p.Add( "o_roles", dbType: OracleDbType.RefCursor, direction: ParameterDirection.Output );

        using ( var multi = cnn.QueryMultiple( "PKG_USERS.GetUserDetailsForID", param: p, commandType: CommandType.StoredProcedure ) ) {
            u = multi.Read<User>( ).Single( );
            u.Roles = multi.Read<UserRole>.ToList( );
        }
    }
    return u;
}

For the type Clob, just specify OracleDbType.Clob when adding parameter.

Justly

@MarcGravell when would one choose to create/use a custom IDynamicParameters implementation over ICustomQueryParameter? – Claudetteclaudia