Re: a enable constrain problem

Hi Erin,

Something else to consider is a "DEFERRABLE" constraint.

You can then disable the constraint during the load as Daniel suggest and then enable the constraint with the NOVALIDATE clause. The constraint is now enabled and being policed but it allows existing rows to violate the constraint, to be subsequently fixed at your leisure.

Business requirements and how the data is subsequently accessed of course determines whether this is an appropriate or viable course of action.

Cheers

Richard

"Daniel Morgan" <dmorgan_at_exesolutions.com> wrote in message news:3D99B8EE.2331B109_at_exesolutions.com...
> 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 - 20:10:43 CDT