From: mcstockX@Xenquery   
      
   did not look in detail at your code, but here's a quick bunch of   
   suggestions:   
      
   1) use standard oracle pl/sql exception handling to indicate success or   
   failure instead of returning 0 or 1   
   -- also realize that if you handle an exception in your pl/sql code, work   
   done in that procedure up to the exception is NOT rolled back, it's only   
   rolled back if you do not handle the exception   
   2) rollback in linear -- it will rollback all work performed since the prior   
   rollback or commit   
   3) look into rollback to savepoint for more control or the transaction   
   4) should you check the return code? depends on what your functionality   
   requires   
      
   read up on transaction processing in the oracle concepts manual and   
   exception handling in the PL/SQL manual   
      
   -- mcs   
      
    wrote in message   
   news:da2e9ee1.0406070700.40d26356@posting.google.com...   
   | 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 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();   
      
   --- SoupGate-Win32 v1.05   
    * Origin: you cannot sedate... all the things you hate (1:229/2)