problem with ODP.NET and transactions

302366

Having a problem with the ODP.NET data provider when using transactions. Here is a simple example to reproduce the problem.

OraConnection LConnection = new OraConnection();
try
{
LConnection.ConnectionString = "<Your data here>";
LConnection.Open();
OraTransaction LTransaction = LConnection.BeginTransaction();
OraCommand LCommand = LConnection.CreateCommand();
LCommand.CommandText = "create table Test ( id int not null primary key )";
LCommand.Transaction = LTransaction;
LCommand.ExecuteNonQuery();
LTransaction.Commit();
}
finally
{
LConnection.Dispose();
}

This example throws an InvalidOperatorException when the transaction is committed. This same sequence of commands works with other ADO.NET data providers.

Has anyone seen this? Is this a known issue, or am I doing something wrong.

Thanx,
Bryn Rhodes
Alphora

6379

What happens when you use a datatype other than int, like number(10)? Oracle doesn't have an "int" datatype, so I would guess that it's causing the error.

302366

Brad,

The statement works fine in SQL*Plus because Oracle maps the ANSI data types to the native, (number(10) in this case). The command also works fine if the ExecuteNonQuery call is not wrapped by the transaction calls. The exception is thrown by the Transaction.Commit() call.

Thanx
Bryn
Alphora

3004

Bryn,

The command that the application executes is a DDL statement. An execution of a DDL statement against an Oracle database commits the transaction if one existed. Therefore, the current implementation of OraTransaction internally gets flagged as a "completed" transaction in such a case. Therefore a Commit() or a Rollback() on the "completed" transaction will throw an exception since the transaction has already completed when the DDL statement was executed.

What some of the other providers are doing is that a new transaction is created behind your back after a DDL statement execution if a transaction already existed. Hence the commit() and a rollback() will work. However, the rollback will obviously not rollback the entire work since all the work before the DDL execution is committed by the DDL execution.

Our recommendation currently is to not start a transaction for DDL statment executions.

thanks,

- nari