ORA-00001: unique constraint (%s.%s) violated
Dereck L. Dietz
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?
DA Morgan
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
damo...@x.washington.edu (replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org
Dereck L. Dietz
"DA Morgan" <damo...@psoug.org> wrote in message
news:12126206...@bubbleator.drizzle.com...
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.
Vladimir M. Zakharychev
> "DA Morgan" <damor...@psoug.org> wrote in message
> > 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 bulk-
insert 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
Dereck L. Dietz
was structurally modified without anybody being notified of the changes.
The error being displayed was a "false" error and had to do with the code
had been expecting column sizes of 11 and 12 but was receiving columns sized
25 and 20 instead.
DA Morgan
A table definition was modified without anybody being notified?
I'd say you've got a problem a lot bigger than anything that an
exception handler can catch.
--
Daniel A. Morgan
Oracle Ace Director & Instructor
University of Washington
damo...@x.washington.edu (replace x with u to respond)
Dereck L. Dietz
Yes I strongly suggested better change management be implemented.