Re: a enable constrain problem

Samuel H Roseman wrote:

> Erin,
>
> Oracle "kicks-out" ROWIDsof offending rows. The target of the EXCEPTIONS
> INTO clause must in be a table having a specific structure.
> See $ORACLE_HOME/rdbms/admin/utlexcpt.sql.
> If you run this script, it should create a table that you can use as the
> target of your EXCEPTIONS INTO clause.
>
> Sam
> OCP Trainee
>
> Erin Gu wrote:
>
> >Hi,
> >I am trying to figure out a way to validate data from a direct path load.
> >For example, the primary key constrain, I have to disable it in order to use
> >the direct load method, then when I try to enable it, I got error like this:
> >ORA-02437: cannot validate (PRDMDEV.ERIN1_PK) - primary key violated
> >
> >I know this failed because there are some deplicated entries in the table.
> >My real question is is there a way to let the database "kick-out" the
> >duplicated entries, and only keep the valid ones? I searched the web,
> >someone suggested something like this:
> >
> >SQL> alter table erin1 enable validate constraint erin1_pk EXCEPTIONS INTO
> >erin1_exp;
> >alter table erin1 enable validate constraint erin1_pk EXCEPTIONS INTO
> >erin1_exp
> >*
> >ERROR at line 1:
> >ORA-00604: error occurred at recursive SQL level 1
> >ORA-00913: too many values
> >
> >But it seems doesn't work. The erin1_exp is another table with same schema
> >as erin1. Can someone tell me what is wrong with this alter table command?
> >
> >Thanks in advance!
> >
> >Erin
> >
> >

There will be no exceptions if constraints are disabled.

I would suggest that you write a procedure to execute after your direct loads that will query each table, remove the dups, and re-enable the constraints.

Daniel Morgan Received on Tue Oct 01 2002 - 10:02:27 CDT