OCIRollback problem

Do you have a question? Post it now! No Registration Necessary.  Now with pictures!

Threaded View
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

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.

Re: OCIRollback problem

On Fri, 03 Mar 2006 23:14:26 -0500, juicy wrote:

Quoted text here. Click to load it

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.


Site Timeline