Today i've been encountered with problem in running a nested transaction and i guess the sample script given below illustrates well abt the same ...
I've a proc "sp_proc1" as follows
CREATE OR REPLACE PROCEDURE sp_proc1
val := 1;
BEGIN SAVEPOINT proc1;
INSERT INTO tab1 VALUES ( 1 , 2 );
ROLLBACK TO SAVEPOINT proc1;
and the called proc is like this
CREATE OR REPLACE PROCEDURE sp_proc2
val1 IN INT DEFAULT NULL
BEGIN SAVEPOINT proc2;
UPDATE tab1 SET col1 = 5;
IF val1 = 0 THEN
ROLLBACK TO SAVEPOINT proc2;
But is there any possibilty of commiting the savepoint in proc2 alone and when rollbacking the savepoint in proc1 , both transaction can be rollbacked ??
( In my understanding , if i used Pragma autonomous_transaction in proc2, then commit in proc2 , commits the savepoint in proc2 alone. But When the savepoint in proc1 is rollbacked only the transaction in proc1 is reverted... is my understanding is correct??)
Your understanding about ATs is correct - they are indeed "autonomous".
There is no possibility of committing part of a transaction. But why do you feel you need to? If you rollback the main transaction, the proc2 change will rollback also; if you commit the main transaction the proc2 change will commit also. Isn't that what you need?