Re: PK getting marked invalid

"M Rothwell" <ThisIsABadAddress_at_toobad.com> wrote in message news:41993518$1_at_usenet01.boi.hp.com...
> Oracle 9iR2
> HPUX
>
> I have a table
>
> CREATE TABLE TABLE1
> (
> PART_NUMBER VARCHAR2(20) NOT NULL,
> PRICE NUMBER(14,2) NULL,
> OBSOLETE_FLAG VARCHAR2(1) NULL
> )
> /
> ALTER TABLE TABLE1
> ADD CONSTRAINT PART_ESPARES_PK
> PRIMARY KEY (PART_NUMBER)
> USING INDEX
> NOLOGGING ENABLE VALIDATE
> /
>
> and a SQL LOADER script:
>
> LOAD DATA
> TRUNCATE
> INTO TABLE TABLE1
> FIELDS TERMINATED BY '|'
> (
> part_number CHAR,
> price DECIMAL EXTERNAL,
> obsolete_flag CHAR
> )
>
> This has been working correctly up until this am. The data file had 2
> duplicates in it. Normally these 2 records should log an error and the
> records would be written to a .bad file.
>
> Today, however, it finished it's load without any errors. The PK was
> marked as invalid and the duplicate records were loaded. I dropped the
> PK, removed the 2 dups then re-created the PK and all was well.
>
> For a test, I re-ran the load script and the same thing happened.
>
> Why were these records not written to a bad file and why did the PK get
> marked as invalid? How can I fix this issue.
>
> for this table, I'm not sure if the data file ever had dups in it
> previously, but I have other tables being loaded in the same manner, and
> the load process will keep the PK valid and write any dups to a .bad file.
>
> Thanks

they arent bad, i presume you use diect=true. Any pk violations go in and you have to get rid maunally Received on Mon Nov 15 2004 - 17:35:59 CST