DbProviderFactory DbType To OracleDbType Mapping in Return Parameter
Posted: Fri 24 Jul 2015 10:07
Hi,
I have a question about the devart related datatype mapping.
We have a project where we need to execute Database Function which returns some xml data (as clob). We created helper extension method that uses DbProviderFactory to create all related objects (parameters...). Everything works fine when returning clob value is less than 4000 symbols. When function returns more than 4000 chars I get ORA-06502: PL/SQL buffer too small exception. The problem is that by default parameter type DbType.Sring maps to OracleDbType.VarChar which is max 4000 length.
In similar topic http://forums.devart.com/viewtopic.php? ... ype#p58291 I read that since version 5.70.170 OracleParameter.OracleDbType is set by default to OracleDbType.Clob for the values which are longer than 4000 symbols, but in this case, it is ReturnValue parameter (Direction.ReturnValue) so I have no value to set (and indicate Clob usage).
I would like to ask if there is a way to make this work without using strong types OracleDbParameter and OracleDbType, because it would require provider specific code instead of generic.
As for now, I use ugly workaround with reflection engaged:
My code is something like that:
Thanks
I have a question about the devart related datatype mapping.
We have a project where we need to execute Database Function which returns some xml data (as clob). We created helper extension method that uses DbProviderFactory to create all related objects (parameters...). Everything works fine when returning clob value is less than 4000 symbols. When function returns more than 4000 chars I get ORA-06502: PL/SQL buffer too small exception. The problem is that by default parameter type DbType.Sring maps to OracleDbType.VarChar which is max 4000 length.
In similar topic http://forums.devart.com/viewtopic.php? ... ype#p58291 I read that since version 5.70.170 OracleParameter.OracleDbType is set by default to OracleDbType.Clob for the values which are longer than 4000 symbols, but in this case, it is ReturnValue parameter (Direction.ReturnValue) so I have no value to set (and indicate Clob usage).
I would like to ask if there is a way to make this work without using strong types OracleDbParameter and OracleDbType, because it would require provider specific code instead of generic.
As for now, I use ugly workaround with reflection engaged:
Code: Select all
var prop = p.GetType().GetProperty("OracleDbType");
if (prop != null)
{
prop.SetValue(p, 6, null); // OracleDbType.Clob
}My code is something like that:
Code: Select all
var factory = DbProviderFactories.GetFactory("Devart.Data.Oracle");
...
var p = factory.CreateParameter();
p.Direction = ParameterDirection.ReturnValue;
p.ParameterName = "ReturnValue";
paramList.Insert(0, p);
...
var sql = "BEGIN :RETURNVALUE := FUNCTION_RETURNING_CLOB(:P0); END;";
context.Database.ExecuteSqlCommand(sql, paramList.Cast<object>().ToArray());