DbProviderFactory DbType To OracleDbType Mapping in Return Parameter

[kris]

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:

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());

Thanks

[Shalex]

Please set the size of the ReturnValue parameter explicitly to the maximum expected value, for example:

Code: Select all

var p = factory.CreateParameter(); 
p.Direction = ParameterDirection.ReturnValue;
p.ParameterName = "ReturnValue";
p.Size = 16000;

paramList.Insert(0, p);

[kris]

Already tried:

Code: Select all

p.Size = int.MaxValue;

Today tried 16000 and 32000. All failed.

[Shalex]

Already tried:

Code: Select all

p.Size = int.MaxValue;

Today tried 16000 and 32000. All failed.

We cannot reproduce the problem. Please give us the following information:
1) send us a small test project with the corresponding DDL/DML script
2) the exact text of the current error and its full call stack with all inner exceptions