delete affects too many rows

From: Marco Nierlich <mani_at_access.unizh.ch>
Date: Fri, 30 Jul 2004 09:48:54 +0200
Message-ID: <4109fdbf$0$4889$5402220f@news.sunrise.ch>

Hi all

We have an oracle 8.1.7 and the following situation

Table A

---

primary key
foreign key 1
foreign key 2
many data fields

let's assume we have two records like

pk(1);null;fk2(1);data(1) (fk1 is null)
pk(2);fk1(2);null;data(2) (fk2 is null)

In a PL/SQL script, we have a loop that opens two cursors (one for each record) and merges the data so that the result looks like

pk(1);fk1(2);fk2(1);data(1+2)

After that, the second record with pk(2) is deleted.

In 80% this works like expected but in 20%, the delete affects pk(2) and also pk(1). Debugging shows that the delete statement is correct ("delete from Table A where primary key = pk(2)") but anyway it deletes also the row with pk(1). When we execute the delete manually (not in the PL/SQL), only the row with pk(2) is deleted.

Initially, the delete was different but because of the errors, we changed it to the above statement (because we thought of a bug). Initially, the cursor for the pk(2) record was opened for a sql statement with the 'for update' construct and the delete was like "delete Table A where current of cursor2".

We commit the transaction every 100th loop but also tried to commit every loop or even twice in the loop (once after the merge and then after the delete so that the delete is it's own transaction). This does not affect at all.

Any Ideas?

Thanks
Marco Received on Fri Jul 30 2004 - 02:48:54 CDT