Devart Forums

Posted: **Tue 07 Dec 2010 23:41**

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

Posted: **Mon 13 Dec 2010 12:03**

Thank you for your report. We have reproduced the described behaviour (probably, it is a mistype: your Select() method's command contains dropScript instead of selectScript). We will investigate the issue and notify you about the results as soon as possible.

Posted: **Fri 17 Dec 2010 17:33**

We have fixed the problem. The fix will be available in the next build of dotConnect for Oracle. I will post here when it is available for download.

Posted: **Wed 12 Jan 2011 17:44**

New build of dotConnect for Oracle 6.00.86 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): http://secure.devart.com/ .
For more information, please refer to http://www.devart.com/forums/viewtopic.php?t=19968 .

Devart Forums

Severe problems with dotConnect and Oracle 9i Server

Severe problems with dotConnect and Oracle 9i Server