Hi,
im using dotConnect for Oracle 6.0.58.0 with .NET 4.
I've problems working with our oracle9i 9.2.0.1.0 database server. I've tried using OCI 9.2 and 11gR2. The Problems do not occur with a 11gR2 server.
The problem is, that a command using the same SQL string will not get executed correctly the second or third time, especially if the first or first two executions had thrown an exceptions (like ora-00942: table or view does not exist).
To make this even more complicated the behavior is different with 9.2 client and 11gR2 client.
For the example i've defined three simple methods:
Code: Select all
static readonly string createScript = "create table test_table1 (id number)";
static void Create(OracleConnection connection)
{
try
{
using (OracleCommand command = new OracleCommand(createScript, connection))
command.ExecuteNonQuery();
Console.WriteLine("Create: No error");
}
catch (Exception e) { Console.WriteLine("Create: Exception {0}", e.Message); }
}
static readonly string dropScript = "drop table test_table1 cascade constraints";
static void Drop(OracleConnection connection)
{
try
{
using (OracleCommand command = new OracleCommand(dropScript, connection))
command.ExecuteNonQuery();
Console.WriteLine("Drop: No error");
}
catch (Exception e) { Console.WriteLine("Drop: Exception {0}", e.Message); }
}
static readonly string selectScript = "select count(*) from test_table1";
static void Select(OracleConnection connection)
{
try
{
object result = null;
using (OracleCommand command = new OracleCommand(dropScript, connection))
result = command.ExecuteScalar();
Console.WriteLine("Select: {0}", result);
}
catch (Exception e) { Console.WriteLine("Select: Exception {0}", e.Message); }
}
I'm always opening the connection at the beginning:
Code: Select all
using (var connection = new OracleConnection())
{
connection.Server = "oracle92";
connection.UserId = "foo";
connection.Password = "bar";
connection.Open();
//Insert example scenario here.
}
Example with CLIENT 9.2.0.1 and SERVER 9.2.0.1.0:
Code: Select all
Create(connection); //This create does work
Drop(connection); //This drop does work
Create(connection); //This create does nothing. The table is not created.
Select(connection);
//Output
//======
//Create: No error
//Drop: No error
//Create: No error
//Select: Exception ORA-00942: Tabelle oder View nicht vorhanden
Example with CLIENT 9.2.0.1 and SERVER 9.2.0.1.0:
Code: Select all
Create(connection);
Select(connection);
Drop(connection);
Select(connection);
Select(connection);
//Output
//======
//Create: No error
//Select: 0
//Drop: No error
//Select: Exception ORA-00942: Tabelle oder View nicht vorhanden
//Select: Exception ORA-01003: keine Anweisung wurde analysiert/geparst
After the ORA-01003 that specific SQL command will always throw ORA-01003, even if you create the table again (manually).
Example with CLIENT 11.2.0.1 and SERVER 9.2.0.1.0:
Code: Select all
Create(connection); //this create does work
Select(connection);
Drop(connection); //this drop does work
Select(connection);
Select(connection);
Create(connection); //this create does work
Select(connection); //this select does still throw an exception
//Output
//======
//Create: No error
//Select: 0
//Drop: No error
//Select: Exception ORA-00942: Tabelle oder View nicht vorhanden
//Select: Exception ORA-01003: keine Anweisung wurde analysiert/geparst
//Create: No error
//Select: Exception ORA-01003: keine Anweisung wurde analysiert/geparst
I hope you can provide a workaround or fix as we still have customers with old 9.2 databases.
Thanks and with kind regards,
Bernhard