setting the OracleDbType based on the size of the string

setting the OracleDbType based on the size of the string

Postby slaxman » Fri 16 Jul 2010 22:07

The OracleDbType is not extracted correctly based on the value when the length of the string exceeds '4000' chars. In this case it should set to 'NCLOB' or 'CLOB'. But it remains as 'VARCHAR'. I don't see any downside to automatically setting it. your thoughts?
slaxman
 
Posts: 47
Joined: Wed 16 Sep 2009 20:09
Location: United States

Postby Shalex » Mon 19 Jul 2010 11:08

As I understood from your post, you mean the problem with the OracleParameterCollection.Add(String,Object) overload. I have reproduced the following issue when the 5000 bytes string is truncated to 4000 bytes, and this truncated value is written to the database:
script:
Code: Select all
create table clob_table
(
  clob_column clob
);

C# code:
Code: Select all
using (OracleConnection conn = new OracleConnection("server=ora1110;uid=scott;pwd=tiger;")) {
  conn.Open();
  OracleCommand command = conn.CreateCommand();
  command.CommandText = "insert into clob_table(clob_column) values (:p1)";

  string str = new string('a', 5000);
  command.Parameters.Add("p1", str); // p1 is VarChar though VarChar's max value is 4000 bytes
  command.ExecuteNonQuery();
}

To fix the issue, please use
command.Parameters.Add("p1", str).OracleDbType = OracleDbType.Clob;
instead of
command.Parameters.Add("p1", str);

We will investigate this behaviour.
Last edited by Shalex on Tue 23 Nov 2010 11:04, edited 1 time in total.
Shalex
Devart Team
 
Posts: 7338
Joined: Thu 14 Aug 2008 12:44

just an idea.

Postby slaxman » Mon 19 Jul 2010 14:26

in my scenario, I use a method that takes Parameter name and value to create Oracle parameter. It does not use the type and relies on the automatic setting by the provider based on the .NET type of the 'value' object passed.

My suggestion would be to check the size of 'str' before adding to parameters in the example you are showing here . If it's more than 4000, the type can be set to 'clob' (or nclob if there is a way to know it) first before setting the value.
slaxman
 
Posts: 47
Joined: Wed 16 Sep 2009 20:09
Location: United States

Postby Shalex » Wed 11 Aug 2010 16:12

We have changed the behaviour: OracleParameter.OracleDbType will be set by default to OracleDbType.Clob for the values which are longer than 4000 symbols if OracleDbType was not set explicitly.

I will post here when the corresponding build is available for download.
Shalex
Devart Team
 
Posts: 7338
Joined: Thu 14 Aug 2008 12:44

nice feature

Postby slaxman » Wed 11 Aug 2010 16:19

will come handy. thanks
slaxman
 
Posts: 47
Joined: Wed 16 Sep 2009 20:09
Location: United States

Postby Shalex » Fri 24 Sep 2010 14:40

New build of dotConnect for Oracle 5.70.170 is available for download now!
It can be downloaded from http://www.devart.com/dotconnect/oracle/download.html (trial version) or from Registered Users' Area (for users with valid subscription only).
For more information, please refer to http://www.devart.com/forums/viewtopic.php?t=19068 .
Shalex
Devart Team
 
Posts: 7338
Joined: Thu 14 Aug 2008 12:44


Return to dotConnect for Oracle