AutoCommit - Oracle.

[Guest]

If you call a stored procedure which is an update procedure, is it
autocommitted by default?

Is the same true for an Oracle Stored procedure?

If you call an Oracle Stored procedure with the defaults for the connection,
and the stored procedure has a rollback, I am getting an error which
indicates that I can not do a rollback on a distributed transaction. How can
I call a stored Oracle Procedure and be able to issue a rollback within the
stored procedure without getting and error in ADO.Net when I do this?

 

[Miha Markic [MVP C#]]

Hi Jim,

If you have a rollback in your stored procedure you would need a begintrans
also.
I really don't see having begintrans somewhere and rollback elsewhere.
It is a bad practice unless I am missing something.

 

[Guest]

I guess I always thought the Oracle stored procedures should be self
contained for anyone who wanted to use them...so if I was using Toad on SQL+
and invoked the stored procedures, the user would not have to worry about
having to have to commit the transaction.

So if I was looking for this, then it would seem that I would have to
support 2 versions of the procedures....

 

[Eric]

I guess I always thought the Oracle stored procedures should be self
contained for anyone who wanted to use them...so if I was using Toad on SQL+
and invoked the stored procedures, the user would not have to worry about
having to have to commit the transaction.

This is incorrect. Oracle never autocommits. TOAD has a configuation
setting to indicate if you want it to commit when you exit, but there is
no autocommit in TOAD until you exit.

I never knew Sqlplus to autocommit, but I haven't used it in a few years
so I can't comment on it.

SQL Server has always had a database option for this because it came
from Sybase.

Unlike SQL Server, Oracle is always in a transaction...COMMIT or
ROLLBACK ends the current tranaction and starts the next one.

When it comes to SPs, you should have 2 types of SPs: those with an
external interface can be used to control transactions, but those that
are only internal should not be used to control transactions.

If you use ADO.NET you have to pay close attention to who controls each
transaction.

It's critically important if you have linked DBs and distributed
transactions (updates accross DB boundaries).

Eric