Re: Stored Procs within a Transaction?

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.

Cheers,
Valentin Received on Mon Feb 05 2007 - 16:00:52 CST