Hi,
I tried to do the same queries using System.Data.OracleClient.OracleConnection instead of Devart.Data.Oracle.OracleConnection. It works fine with System.Data.OracleClient.OracleConnection.
Result with Devart.Data.Oracle.OracleConnection
Result with System.Data.OracleClient.OracleConnection
Source code with Devart.Data.Oracle.OracleConnection :
___________________________________________________________
using System;
using System.Collections.Generic;
using System.Text;
using Devart.Common;
using Devart.Data;
using Devart.Data.Oracle;
using System.Data;
namespace mgdis.administration
{
class Program
{
private static OracleConnection _ConnectionTypee = null;
private static int _myCounter = 0;
private static void CreateConnection()
{
_ConnectionTypee = new Devart.Data.Oracle.OracleConnection();
_ConnectionTypee.Server = "172.16.1.33";
_ConnectionTypee.Sid = "PROGOSF";
_ConnectionTypee.Port = 1521;
_ConnectionTypee.UserId = "TEST";
_ConnectionTypee.Password = "TEST";
_ConnectionTypee.Direct = true;
_ConnectionTypee.ConnectionString += "Pooling=false;";
// We set true to conserve french chars
_ConnectionTypee.Unicode = true;
}
private static string GetUniqueValueString(OracleCommand oCom, CommandBehavior tmpB)
{
IDataReader dr = null;
try
{
dr = oCom.ExecuteReader(tmpB);
string ret = string.Empty;
while (dr.Read())
ret = dr.GetString(0);
dr.Close();
return ret;
}
catch (Exception ex)
{
if (dr != null && !dr.IsClosed) dr.Close();
throw ex;
}
}
static void Main(string[] args)
{
//OracleTransaction trs = null;
string currentCommand = string.Empty;
// Creation of the connection
CreateConnection();
try
{
_ConnectionTypee.Open();
_myCounter = 0;
// Drop the table before
currentCommand = "DROP TABLE TESTINSERTLNG";
_ConnectionTypee.Unicode = true;
OracleCommand oCom = new OracleCommand(currentCommand, _ConnectionTypee);
oCom.CommandType = CommandType.Text;
oCom.ExecuteNonQuery();
// Creation of the table with a LONG column
currentCommand = "CREATE TABLE TESTINSERTLNG (";
currentCommand += "MYKEY NUMBER(5) DEFAULT 0 NOT NULL";
currentCommand += ",PREFERENCE LONG NOT NULL)";
_ConnectionTypee.Unicode = true;
oCom = new OracleCommand(currentCommand, _ConnectionTypee);
oCom.CommandType = CommandType.Text;
oCom.ExecuteNonQuery();
// Insertion of 2 chars without unicode activated
string insertString = "".PadLeft(2, 'a');
Console.WriteLine("Insertion of {0} chars without special chars", insertString.Length);
Console.WriteLine("======================================================================");
InsertAndReadValue(ref currentCommand, ref oCom, insertString, false);
Console.WriteLine("");
// Insertion of 2 chars with unicode activated
InsertAndReadValue(ref currentCommand, ref oCom, insertString, true);
// Insertion of 2 chars without unicode activated
insertString = "".PadLeft(2, 'é');
Console.WriteLine("Insertion of {0} chars with only special chars", insertString.Length);
Console.WriteLine("======================================================================");
InsertAndReadValue(ref currentCommand, ref oCom, insertString, false);
Console.WriteLine("");
// Insertion of 2 chars with unicode activated
InsertAndReadValue(ref currentCommand, ref oCom, insertString, true);
// Insertion of 3 chars without unicode activated
insertString = "aéa";
Console.WriteLine("Insertion of {0} chars with special chars", insertString.Length);
Console.WriteLine("======================================================================");
InsertAndReadValue(ref currentCommand, ref oCom, insertString, false);
Console.WriteLine("");
// Insertion of 3 chars with unicode activated
InsertAndReadValue(ref currentCommand, ref oCom, insertString, true);
Console.WriteLine("");
Console.WriteLine("Press any key to stop");
Console.ReadLine();
}
finally
{
if (_ConnectionTypee.State == ConnectionState.Open)
_ConnectionTypee.Rollback();
if (_ConnectionTypee != null)
if (_ConnectionTypee.State != ConnectionState.Closed) { _ConnectionTypee.Close(); }
}
}
private static void InsertAndReadValue(ref string currentCommand, ref OracleCommand oCom, string insertString, bool withUnicodeTrue)
{
_myCounter++;
_ConnectionTypee.Unicode = withUnicodeTrue;
_ConnectionTypee.Open();
// Insertion
currentCommand = "INSERT INTO TESTINSERTLNG (MYKEY, PREFERENCE) VALUES (:mykey, :preference)";
oCom = new OracleCommand(currentCommand, _ConnectionTypee);
OracleParameter paramKey = new OracleParameter("mykey", _myCounter);
paramKey.OracleDbType = OracleDbType.Integer;
oCom.Parameters.Add(paramKey);
OracleParameter paramPref = new OracleParameter("preference", insertString);
paramPref.OracleDbType = OracleDbType.Long;
paramPref.Size = insertString.Length;
oCom.Parameters.Add(paramPref);
oCom.ExecuteNonQuery();
// Get de last inserted value
currentCommand = "SELECT preference FROM TESTINSERTLNG WHERE mykey=" + _myCounter.ToString();
oCom = new OracleCommand(currentCommand, _ConnectionTypee);
CommandBehavior comBehav = CommandBehavior.CloseConnection;
string lastValue = GetUniqueValueString(oCom, comBehav);
Console.WriteLine("Unicode : {0}", _ConnectionTypee.Unicode.ToString());
Console.WriteLine("\t insert string length \t\t: \t{0}", insertString.Length.ToString());
Console.WriteLine("\t read value length \t\t: \t{0}", lastValue.Length.ToString());
Console.WriteLine("\t inserted value (between |) \t:\t|{0}|", insertString);
Console.WriteLine("\t readed value (between |) \t:\t|{0}|", lastValue);
Console.WriteLine("");
}
}
}
___________________________________________________________
Thanks for help