DbProviderFactory DbType To OracleDbType Mapping in Return Parameter

DbProviderFactory DbType To OracleDbType Mapping in Return Parameter

Postby kris » 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?f=1&t=18346&p=58291&hilit=clob+clob+dbtype#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
kris
 
Posts: 2
Joined: Fri 24 Jul 2015 09:27

Re: DbProviderFactory DbType To OracleDbType Mapping in Return Parameter

Postby Shalex » Fri 24 Jul 2015 12:28

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);
Shalex
Devart Team
 
Posts: 7460
Joined: Thu 14 Aug 2008 12:44

Re: DbProviderFactory DbType To OracleDbType Mapping in Return Parameter

Postby kris » Tue 28 Jul 2015 10:11

Already tried:
Code: Select all
p.Size = int.MaxValue;

Today tried 16000 and 32000. All failed.
kris
 
Posts: 2
Joined: Fri 24 Jul 2015 09:27

Re: DbProviderFactory DbType To OracleDbType Mapping in Return Parameter

Postby Shalex » Wed 29 Jul 2015 12:29

kris wrote: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
Shalex
Devart Team
 
Posts: 7460
Joined: Thu 14 Aug 2008 12:44


Return to dotConnect for Oracle