Re: ORA-00001: unique constraint (%s.%s) violated

From: Dereck L. Dietz <>
Date: Wed, 4 Jun 2008 19:32:31 -0400
Message-ID: <DBF1k.7587$>

"DA Morgan" <> wrote in message
> Dereck L. Dietz wrote:
>> Oracle 10g
>> Windows 2003 Server
>> ERROR at line 1:
>> ORA-00001: unique constraint (%s.%s) violated
>> I'm getting the above error despite the fact that the error line is
>> pointing to a BULK FETCH into a nested table and also that the table
>> being loaded has no constraints defined on it whatsoever.
>> This very same code ran without problem last month (monthly run) and the
>> last time the code has been changed at all has been back in January 2008.
>> With the %s.%s in the description I'm wondering if it's a false error and
>> something else is going on. I can make no sense why I'm getting a
>> constraint violation where there is no constraint.
>> Has anybody else run into anything like this?
> I've never seen it before but I would suggest starting in DBA
> constraints looking for any constraints on the table and any
> constraints referencing the table.
> What bothers me about what you've written is that it is impossible to
> violate a constraint with a BULK FETCH so you seem to be pointing your
> finger in the wrong direction. Find the INSERT or UPDATE statement that
> is triggering the problem and post it.
> --
> Daniel A. Morgan
> Oracle Ace Director & Instructor
> University of Washington
> (replace x with u to respond)
> Puget Sound Oracle Users Group

Here is the code. According to the compiler I use and the exception routine it's pointing the error out as being the FETCH statement.

  FETCH cr_claims_rx_cout <----- line identified by Oracle as in error     BULK COLLECT INTO nt_load_rec
    LIMIT 100000;   EXIT WHEN nt_load_rec.COUNT = 0;

  • Insert RX Carve Out rows into the HMP_PRESCRIPT05 table

   FORALL v_row IN INDICES OF nt_load_rec      INSERT /*+ APPEND NOLOGGING */ INTO hmp_prescript05 VALUES nt_load_rec(v_row);

I am puzzed because 1) the error is being pointed to a FETCH and how can you violate any constraint fetching and selecting and 2) the table being inserted into doesn't have ANY constraints - not even a NOT NULL constraint. So if there is a constraint being violated where is it?

I already checked the DBA_CONSTRAINTS table and there was nothing in it for this table.

The error string is also not giving any constraint by name making me wonder if the error being displayed is itself somehow wrong. Received on Wed Jun 04 2008 - 18:32:31 CDT

Original text of this message