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

From: Vladimir M. Zakharychev <vladimir.zakharychev_at_gmail.com>
Date: Thu, 5 Jun 2008 05:40:59 -0700 (PDT)
Message-ID: <45669c49-c2b2-4862-b950-741a34573fa2@r66g2000hsg.googlegroups.com>


On Jun 5, 3:32 am, "Dereck L. Dietz" <diet..._at_ameritech.net> wrote:
> "DA Morgan" <damor..._at_psoug.org> wrote in message
>
> news:1212620682.858137_at_bubbleator.drizzle.com...
>
>
>
> > Dereck L. Dietz wrote:
> >> Oracle 10g 10.2.0.3.0
> >> 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
> > damor..._at_x.washington.edu (replace x with u to respond)
> > Puget Sound Oracle Users Group
> >www.psoug.org
>
> 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.

This looks like some obscure internal error on "insert" into the nt_load_rec collection being translated into ORA-00001 (like insufficient PGA memory to extend the collection, or fetching into the collection already populated with previous fetch, or something else like that.) Did you try to reduce the LIMIT? Did you try to free the collection before going for the next round, like

  nt_load_rec := nt_load_rec_type(); -- or nt_load_rec.delete;   SELECT ... BULK COLLECT INTO nt_load_rec ...;

Also, from the code you posted it doesn't look like you do some extra processing between fetch and insert, you just copy the data from the cursor to the output table in batches. I so then why not just INSERT AS SELECT? Why do you buffer the result set in memory and then bulkinsert  it, wasting time and PGA memory for buffering, if single SQL statement can do it, and more efficiently?

Regards,

   Vladimir M. Zakharychev
   N-Networks, makers of Dynamic PSP(tm)    http://www.dynamicpsp.com Received on Thu Jun 05 2008 - 07:40:59 CDT

Original text of this message