Re: Very strange problem.

Scarab, I suspect your problem has to do with the fact that idate is an Oracle date datatype and as such it also includes time. That means your query does not affect all rows for a specific date, as in day of the month, the same since depending on when the query runs rows some rows for a day will test true and some will test false. That is if the query time is 9 AM then any row on the same day that has a time value greater than 9 AM will not be included in the result set.

Try where ( idate < trunc(sysdate - 30) )

This will treat all date values for the cutoff date the same, that is, time values of 00:00:00 - 23:59:59 will all test true in the where clause.

The other potential problem you have has to the with the Oracle read consistency model and how inserts, updates, and deletes are done in this table. It is not wise to use rowid values fetched in non-select for update cursors to peform update or delete operations if concurrent delete operations exist, without specifying additional selection criteria. User A selects a list of rows to be processed without locking them. User B deletes row 60 and commits. Now user B or C inserts a new row and commits. The space was available at rowid 60 so Oracle reuses it. Now session A gets around to rowid 60 and issues a delete by rowid. This is not the same row that was fetched by the driving cursor. Whenever concurrent deletes are possible then before doing a delete or update by rowid you should verify that the row still meets the selection criteria. In your example the idate would need to still be < trunc(sysdate) - 30. If on update or delete the additional criteria fails then no action is taken, that is, the delete or update would not affect any rows. This is not considered an error by Oracle and processing would continue.

HTH -- Mark D Powell -- Received on Tue Dec 27 2005 - 20:51:11 CST