It does not have to be an official application that makes the update. If it happens monthly or so, but with some regularity, there may be someone using e.g. a home-brewn Access app or Excel spreadsheet to create monthly//quarterly/yearly reports and accidentally update some num value. It is very easy in either of these tools to update values without even realising it...
Do your users have a database username & password, or is the database only accessible via sanctioned middleware applications?
Cheers, Tony Around 5/02/2010 10:46 PM, Nigel Thomas said:
Sorry, you both spotted my silly error on which sequence was being selected conditionally. Oops.The main point is that any UPDATE of NUM can cause an ORA-0001. If there is an edge condition in the client app that results in NUM being updated occasionally, that would account for the intermittent behaviour. Oracle Forms (for example) can easily be configured to generate an update to PK/UK columns (because it generates a generic update, and updates by rowid). I wouldn't put it past an O/R mapping like Hibernate to be similarly prone to mis-configuration. If the application sometimes mistakenly writes to that field, your error will appear.The first thing to do is to verify (directly from V$SQL and if possible from the source code) whether there is _any_ SQL which could possibly set AUFT.NUM, even if it is not designed to. Don't accept the word of any developer! He's not lying, but he can be mistaken.HTH NigelOn 5 February 2010 12:32, Mark W. Farnham <mwf@xxxxxxxx <mailto:mwf@xxxxxxxx>> wrote:I thought the constraint violation was on NUM, not ID. If the constraint violation is on ID, then the idea of garbage in :new_id is valid.Updates to NUM, inadvertant or otherwise, seem the likely cause since the behavior is intermittent. The other thing that could be intermittent that comes to mind would some user’s schema scoping referencing a different definition of the sequence.<start whining about decades old enhancement requests> Too bad ORA-00001 does not routinely spit out the duplicate value, along with the two PKs in the case the column set in question is not the PK, in which case the rowid of the existing record could be useful. <end whining about decades old enhancement requests>And while sequences can skip numbers for a variety of circumstances and conditions mostly revolving around sequence cache and crashes, I cannot remember ever seeing a single sequence ever delivering duplicates without wrapping. (And I hope you’re not wrapping on a sequence intended to deliver monotonically increasing unique values.)mwf
