- 3,715,830 Users
- 2,242,890 Discussions
- 7,845,629 Comments
Forum Stats
Discussions
Categories
- 17 Data
- 362.2K Big Data Appliance
- 7 Data Science
- 1.6K Databases
- 472 General Database Discussions
- 3.7K Java and JavaScript in the Database
- 22 Multilingual Engine
- 487 MySQL Community Space
- 5 NoSQL Database
- 7.6K Oracle Database Express Edition (XE)
- 2.8K ORDS, SODA & JSON in the Database
- 417 SQLcl
- 42 SQL Developer Data Modeler
- 184.9K SQL & PL/SQL
- 21K SQL Developer
- 1.9K Development
- 3 Developer Projects
- 32 Programming Languages
- 135.1K Development Tools
- 9 DevOps
- 3K QA/Testing
- 254 Java
- 6 Java Learning Subscription
- 10 Database Connectivity
- 66 Java Community Process
- 1 Java 25
- 9 Java APIs
- 141.1K Java Development Tools
- 6 Java EE (Java Enterprise Edition)
- 153K Java Essentials
- 135 Java 8 Questions
- 86.2K Java Programming
- 270 Java Lambda MOOC
- 65.1K New To Java
- 1.7K Training / Learning / Certification
- 13.8K Java HotSpot Virtual Machine
- 10 Java SE
- 13.8K Java Security
- 3 Java User Groups
- 22 JavaScript - Nashorn
- 18 Programs
- 125 LiveLabs
- 30 Workshops
- 9 Software
- 3 Berkeley DB Family
- 3.5K JHeadstart
- 5.7K Other Languages
- 2.3K Chinese
- 3 Deutsche Oracle Community
- 11 Español
- 1.9K Japanese
- 2 Portuguese
problem with ODP.NET and transactions
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
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
-
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.
-
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 -
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.