DbProviderFactory DbType To OracleDbType Mapping in Return Parameter

Discussion of open issues, suggestions and bugs regarding ADO.NET provider for Oracle
Post Reply
kris
Posts: 2
Joined: Fri 24 Jul 2015 09:27

DbProviderFactory DbType To OracleDbType Mapping in Return Parameter

Post by 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? ... 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
Site Admin
Posts: 8246
Joined: Thu 14 Aug 2008 12:44

Re: DbProviderFactory DbType To OracleDbType Mapping in Return Parameter

Post by 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);

kris
Posts: 2
Joined: Fri 24 Jul 2015 09:27

Re: DbProviderFactory DbType To OracleDbType Mapping in Return Parameter

Post by kris » Tue 28 Jul 2015 10:11

Already tried:

Code: Select all

p.Size = int.MaxValue;
Today tried 16000 and 32000. All failed.

Shalex
Site Admin
Posts: 8246
Joined: Thu 14 Aug 2008 12:44

Re: DbProviderFactory DbType To OracleDbType Mapping in Return Parameter

Post by 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

Post Reply