CODEWITHSUNDEEP
c#oracle-databasetransactionsodp.netodp.net-managed
Husqvik
Husqvik

Reputation: 5809

ODP.NET - disable auto commit

Is there a simple way how to disable auto commit when using ODP.NET? I want to start transactions using SET TRANSACTION command, not using Connection.BeginTransaction neither TransactionScope. Also I want to any DML start transaction (if not started yet) but not commit changes until I issue COMMIT command. I know that other Oracle providers (JDBC or Devart) support this but I would like to achieve the same behavior with ODP.NET.

I also found there is private field probably controlling that in managed version of ODP.NET but it's hidden within implementation of physical connection which is difficult to access via OracleConnection instance. It also seems in unmanaged version this setting is outside Oracle.DataAccess.dll assembly.

Upvotes: 1

Views: 5115

Answers (1)

Christian Shay
Christian Shay

Reputation: 2635

The only way to disable autocommit with ODP.NET is to use BeginTransaction/new TransactionScope.

You can make it clear to your users in your UI that autocommit is on by default and they can turn if off by clicking a toolbar button, etc at which time you can call Begin Transaction using some options you can place in a properties or options page.

This is exactly what we do with our Oracle Developer Tools in the Query Window.

Another possible thing to look into is anonymous PL/SQL for sending up a block of SQL statements at once.

As for "SET TRANSACTION", I am not sure if it will have an effect if you have already done a "BeginTransaction" but you can document that this command is not supported if that is the case (we would have to do the same in our query window if so).

If you would like ODP.NET to be enhanced with this feature at some future date, you may want to put in a request over at the ODP.NET feature request page:

http://apex.oracle.com/pls/apex/f?p=18357:46

Upvotes: 5

Related Questions