Results 1 to 7 of 7
  1. #1
    Join Date
    Nov 2005
    Posts
    8

    Question Unanswered: query related with nested transaction

    Friends,

    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
    AS
    val INT;
    BEGIN
    val := 1;
    BEGIN SAVEPOINT proc1;
    INSERT INTO tab1 VALUES ( 1 , 2 );
    sp_proc2(val);
    ROLLBACK TO SAVEPOINT proc1;
    END;
    /

    and the called proc is like this

    CREATE OR REPLACE PROCEDURE sp_proc2
    (
    val1 IN INT DEFAULT NULL
    )
    AS
    BEGIN SAVEPOINT proc2;
    UPDATE tab1 SET col1 = 5;
    IF val1 = 0 THEN
    BEGIN
    ROLLBACK TO SAVEPOINT proc2;
    END;
    ELSE
    BEGIN
    COMMIT;
    END;
    END IF;
    END;
    /

    and when i exec the proc1

    ORA-01086: savepoint 'PROC1' never established

    Any suggestions on this .....
    Last edited by sqlpgmr; 11-30-05 at 06:21.

  2. #2
    Join Date
    Jan 2004
    Posts
    370
    You issue a commit in sp_proc2
    You have erased the savepoint.

    .

  3. #3
    Join Date
    Nov 2005
    Posts
    8
    yes i understand.

    My question is can my commit be made specific to the savepoint 'proc2'

  4. #4
    Join Date
    Jan 2004
    Posts
    370
    Check out Autonomous Transactions.

    .

  5. #5
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    But autonomous transactions are not nested, they are independent. Oracle does not support nested transactions.

  6. #6
    Join Date
    Nov 2005
    Posts
    8
    Thanks Skywriter & Tony.

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

    Thanks in advance.

  7. #7
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    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?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •