Dapper & Oracle Clob Type

Question

I am using dapper to access oracle. I have a scenario where I should have an output parameter of type OracleDbType.Clob. Since I am using dapper and thus using the basic DbType enum, I am using the DbType.Object enumeration as suggested here http://docs.oracle.com/html/B14164_01/featOraCommand.htm to get up for OracleDbType.Clob.

However, this sets the command parameter (deep in dapper) for the DbType object and the oracle Blob type (since the DbConnection providers are a specific OracleParameter). The problem is that this Oracle process only works if this parameter is of type Clob not Blob.

Pure ADO code works like a charm (using OracleParameter and OracleConnection, etc.), but there seems to be no way to set a specific type or intercept this DbParameter creation process in dapper to change this OracleType type on the returned CommandParameter?

It 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,

John

Answer 1

I know that you asked this a long time ago. However, I ran into the same problem with a different type of database.

Basically, you ran into one of the problems with Dapper. This is a microorganism that has somewhat doubted how everything should work. It seems that this was written primarily with MS SQL Server, although it claims to work with any type of database. Which is mostly true, however, when you start getting more esoteric data types, such as Clob's, Blob, Geospatial, etc., begin to decay, as you saw.

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

Go to this line:

 sealed partial class DbString : Dapper.SqlMapper.ICustomQueryParameter 

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

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

Answer 2

I found this vijaysg / OracleDynamicParameters.cs

Creates an OracleDynamicParameters class OracleDynamicParameters implements the IDynamicParameters interface.

Here how to use it

Example:

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

and how to call him 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 type Clob, simply specify OracleDbType.Clob when adding the parameter.