problem with ODP.NET and transactions — oracle-tech

    Forum Stats

  • 3,715,830 Users
  • 2,242,890 Discussions
  • 7,845,629 Comments

Discussions

Howdy, Stranger!

It looks like you're new here. If you want to get involved, click one of these buttons!

problem with ODP.NET and transactions

302366
302366 Member Posts: 2
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

Comments

  • 6379
    6379 Member Posts: 1
    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
    302366 Member Posts: 2
    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
    3004 Member Posts: 204,171
    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
This discussion has been closed.