Results 1 to 2 of 2
  1. #1
    Join Date
    Jan 2003
    Posts
    5

    Unanswered: Rollback & Commit

    Hi,

    I have a procedure which will call 3 functions.
    First function will update a record.
    Second function will delete a record.
    Third function will insert a record.

    Each function will return a zero if successful, otherwise return 1.

    My questions are
    1) If one or more of the function fail, and I will call a rollback in
    the procudure, would it rollback all the 3 functions?
    2) Or I should check the return code for each function before I call
    the next function?

    Feel free to tell me your suggestion!

    Thank you!

    Anders




    Procedure my_proc ()
    ret1 number;
    ret2 number;
    ret3 number;
    total := number;
    Begin
    ret1 := function_one();
    ret2 := function_two();
    ret3 := function_three();
    total := ret1 + ret2 +ret3;
    if total > 0 then
    rollback;
    else
    commit;
    end if;
    End my_proc;

    function_one()
    return number is
    ret_one number := 0;
    begin
    update dummy1 set dummy_col ='dummy';
    return ret_one;
    EXCEPTION
    WHEN OTHERS
    THEN
    ret_one:=1;
    RETURN ret_one;
    end function_one();

    function_two()
    return number is
    ret_two number := 0;
    begin
    delete from dummy2 where dummy_col ='dummy';
    return ret_two;
    EXCEPTION
    WHEN OTHERS
    THEN
    ret_two:=1;
    RETURN ret_two;
    end function_two();

    function_three()
    return number is
    ret_three number := 0;
    begin
    insert into dummy3 (dummy_col) values ('dummy');
    return ret_three;
    EXCEPTION
    WHEN OTHERS
    THEN
    ret_three:=1;
    RETURN ret_three;
    end function_three();

  2. #2
    Join Date
    Nov 2002
    Location
    Desk, slightly south of keyboard
    Posts
    697
    Hi,

    Yes all three would be committed or rolled back. Personally though, I wouldn't call all three like that, if function2 or function3 are time consuming, and function1 has already failed, it's a bit of a waste.

    Code:
    if function1 = 1 then
       if function2 = 1 then
          if function3 = 1 then
             commited := True;
             commit;
          end if;
       end if;
    end if;
    if not commited then 
       rollback;
    end fif;
    Useful reading related to your question....
    AUTONOMOUS TRANSACTIONS
    SAVEPOINT

    Hth
    Bill
    Last edited by billm; 06-07-04 at 14:32.
    Please don't email me directly with questions. I've probably just got home from the pub and cannot guarantee the sanity of my answers. In fact, I can't believe I actually made it home.

Posting Permissions

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