setting the OracleDbType based on the size of the string

[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?

[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.

[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.

[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.

[slaxman]

will come handy. thanks

[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 .