Check out AUTONOMOUS TRANSACTIONS in the documentation.

"Andy Cortwright" <acortwr8@hotmail.com> wrote in message
news:3b2fd26e$1@news.devx.com...
>
> In SQL Server, you can write nested transactions. For
> example I could write code like in the example below. The nested
transactions
> can be independently committed or rolled back.
>
> BEGIN TRANSACTION
> UPDATE ....
> INSERT ....
> BEGIN TRANSACTION
> UPDATE...
> UPDATE...
> UPDATE...
> COMMIT or ROLLBACK
> BEGIN TRANSACTION
> INSERT...
> INSERT...
> COMMIT or ROLLBACK
> COMMIT or ROLLBACK
>
> Also, in SQL Server, I can write stored procedures that each have a
> transaction that either performs commit or rollback at the end. If I call
> several such sp's and I can nest them within one transaction and either
> commit or roll back the entire transaction (see pseudo code below).
>
> BEGIN TRANSACTION
> STORED PROCEDURE #1
> STORED PROCEDURE #2
> STORED PROCEDURE #3
> COMMIT or ROLLBACK (if rollback, then all changes in sp's are rolled back)
>
> STORED PROCEDURE #1
> BEGIN TRANSACTION
> INSERT ...
> UPDATE ...
> COMMIT or ROLLBACK
>
> STORED PROCEDURE #2
> BEGIN TRANSACTION
> INSERT ...
> DELETE ...
> COMMIT or ROLLBACK
>
> STORED PROCEDURE #3
> BEGIN TRANSACTION
> DELETE ...
> UPDATE ...
> COMMIT or ROLLBACK
>
> In Oracle, however, there are no such things as nested transactions and
> once a COMMIT is executed, inside or outside of a stored procedure,
> everything is committed and a new transaction begins.
>
> My question is this...How are transactions typically handled in Oracle
> within the context of writing stored procedures. Say I need to save data
> to 10 tables as one independent transaction and want to use independent
stored
> procedures to do it, how could I?
>
> Any insight you have would be appreciated.
>