Oracle Nested Transactions - or lack thereof

[Andy Cortwright]

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.

[Craig Clearman]

Andy,

>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?

Generally, you do not write any transaction flow in a stored
procedure. That is considered program-domain. Your program should
start the transaction, and commit / rollback the transaction depending
upon its entire sequence of events.

Ciao, Craig