Page 1 of 1
setting the OracleDbType based on the size of the string
Posted: Fri 16 Jul 2010 22:07
by slaxman
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?
Posted: Mon 19 Jul 2010 11:08
by Shalex
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.
just an idea.
Posted: Mon 19 Jul 2010 14:26
by slaxman
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.
Posted: Wed 11 Aug 2010 16:12
by Shalex
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.
nice feature
Posted: Wed 11 Aug 2010 16:19
by slaxman
will come handy. thanks
Posted: Fri 24 Sep 2010 14:40
by Shalex
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 .