473,326 Members | 2,805 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,326 software developers and data experts.

Rollback and 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 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();
Jul 19 '05 #1
1 10007
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

<an*********@hotmail.com> wrote in message
news:da**************************@posting.google.c om...
| 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();
Jul 19 '05 #2

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

2
by: Alice | last post by:
Hi all, I have a problem with rollback segment. I am a beginner and do not have much knowledge of DBA. Anyways, the problem is there is a transaction of around 105 MB. This would not fit...
0
by: Colm Connolly | last post by:
Hi all, I'm working with tables stored by the InnoDB engine and would like to be able to commit only if there are no errors generated by a group of statements like this. /* -*- sql -*- */...
1
by: Joăo Santa Bárbara | last post by:
Hi all i have this problem . i have 2 tables ( they are not master detail ) 2 simple tables, and i´m doing a simple update just like this try Trans = Con.BeginTransaction .............
6
by: Tor Heigre | last post by:
Hello While testing our code on DB2 we have encountered a difference in the behaviour of DB2Driver (com.ibm.db2.jcc.DB2Driver with driverType= 4) compared to the drivers offered by Oracle 9i and...
1
by: Jason Huang | last post by:
Hi, I am just wondering how do we test the transaction's Commit and Rollback? How do we simulate a situation to let the transaction fail then Rollback? Thanks for help. Jason
0
by: minapatel | last post by:
I have the following procedure CREATE OR REPLACE procedure FOL_PURGE_CASES as /* type "SET SERVEROUTPUT ON" in sqlplus to debug !! */ cursor all_cases is cursor all_cases is ...
0
by: pip | last post by:
I want to use load by cursor for obvious performance reasons, but following execution of a SQL script I need to check the Unix return code and either commit or rollback, is there anyway I can use...
9
by: edwardscm | last post by:
Hi, i am new to PL/SQL scripting, and need a script to have our operators who run our end of day processes to run their own Oracle PL/SQL and then based on the results of the SQL be prompted to...
1
by: anders_tung | last post by:
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...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.