470,849 Members | 1,275 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 470,849 developers. It's quick & easy.

OCIRollback problem

I want to do rollback from insert data into master-details table when there
are duplicate ID key in by user. The algorithm is like this

1. insert data into master_table
2. loop
3. select count(*) from detail where ID = <ID key in by user>
4. if count(*) > 0{ $dup = true;}//end if
5. insert data into detail_table
6. End loop
7. If $dup = true{OCIRollback($conn);}
else{OCICommit($conn);}

if there are two duplicate row inserted into detail_table, it should
rollback all transaction but I found that it won't work and still insert
on record to it.

Please advise on this problem, thanks.

Mar 4 '06 #1
1 1273
On Fri, 03 Mar 2006 23:14:26 -0500, juicy wrote:
I want to do rollback from insert data into master-details table when there
are duplicate ID key in by user. The algorithm is like this

1. insert data into master_table
2. loop
3. select count(*) from detail where ID = <ID key in by user>
4. if count(*) > 0{ $dup = true;}//end if
5. insert data into detail_table
6. End loop
7. If $dup = true{OCIRollback($conn);}
else{OCICommit($conn);}

if there are two duplicate row inserted into detail_table, it should
rollback all transaction but I found that it won't work and still insert
on record to it.

Please advise on this problem, thanks.

You should have posted the original code snipped that fails,
not just the pseudocode. Nevertheless, my guess is that
your problem is the fact that oci_execute performs a commit if
the statement execution is successful. Here is what the good
book says:

"oci_execute() executes a previously parsed statement (see oci_parse()).
The optional mode allows you to specify the execution mode (default is
OCI_COMMIT_ON_SUCCESS). If you don't want statements to be committed
automatically, you should specify OCI_DEFAULT as your mode."

In addition to that, I feel inclined to ask why are you de-duping data
like that and why don't you create unique constraint and let the database
winnow out duplicates? Oracle supports things like unique constraints, and
primary keys which will eliminate the duplicates without loops.

--
http://www.mgogala.com

Mar 5 '06 #2

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

reply views Thread by Bruce Davis | last post: by
11 posts views Thread by Kostatus | last post: by
9 posts views Thread by Sudesh Sawant | last post: by
117 posts views Thread by Peter Olcott | last post: by
28 posts views Thread by Jon Davis | last post: by
6 posts views Thread by Ammar | last post: by
2 posts views Thread by Mike Collins | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.