Re: Stored Procs within a Transaction?

From: DA Morgan <damorgan_at_psoug.org>
Date: Tue, 06 Feb 2007 09:11:18 -0800
Message-ID: <1170781877.118402@bubbleator.drizzle.com>

Mike Ross wrote:

> "Valentin Minzatu" <valentinminzatu_at_yahoo.com> wrote in message 
> news:1170712850.520519.190770_at_l53g2000cwa.googlegroups.com...

>> On Feb 5, 4:14 pm, "Mike Ross" <m..._at_perq.com> wrote:
>>> I have been migrating my company's application to an Oracle 10g database,
>>> and I have run into a strange problem:
>>>
>>> I begin a transaction, execute several SQL insert and update statements,
>>> then call a Stored Procedure that does a table insert, then I do a couple
>>> more SQL insert statements, and then I rollback the transaction. All of
>>> the
>>> activity is rolled back - EXCEPT the insert that happened in the Stored
>>> Procedure - that activity remains in the database. The Stored Proc is
>>> very
>>> simple - it just inserts a row into a table, and it doesn't declare any
>>> of
>>> its own transactions or anythign like that.
>>>
>>> I am using Microsoft Visual Studio 2005 and ADO.NET to do this, using
>>> OracleCommand and OracleTransaction objects, calling
>>> OracleCommand.ExecuteNonQuery to submit the commands to Oracle.
>>>
>>> Can anybody help me understand why the Stored Procedure activity is NOT
>>> getting rolled back?
>> Maybe because you call the SP in auto-commit mode? I am not even
>> familiar with ADO .NET techonology, but given that there is no
>> explicit commit in your stored procedure that's what I would next look
>> at.
>>

> 
> I'm not explicitly setting an auto-commit mode.  Is it set on by default in 
> stored procs?

No!

SQL*Plus: Release 10.2.0.2.0 - Production on Tue Feb 6 09:09:41 2007

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - Production With the Partitioning, OLAP and Data Mining options

SQL> CREATE TABLE t (

2 col1 VARCHAR2(3));

Table created.

SQL> CREATE OR REPLACE PROCEDURE tproc AS

   2 BEGIN
   3 INSERT INTO t VALUES ('ZZZ');
   4 END tproc;
   5 /

Procedure created.

SQL> exec tproc

PL/SQL procedure successfully completed.

SQL> SELECT * FROM t;

COL

---
ZZZ

SQL> rollback;

Rollback complete.

SQL> SELECT * FROM t;

no rows selected

SQL>

There is no such thing in Oracle.
-- 
Daniel A. Morgan
University of Washington
damorgan_at_x.washington.edu
(replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org

Received on Tue Feb 06 2007 - 11:11:18 CST