PK getting marked invalid

From: M Rothwell <ThisIsABadAddress_at_toobad.com>
Date: Mon, 15 Nov 2004 14:51:30 -0800
Message-ID: <41993518$1@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 Received on Mon Nov 15 2004 - 16:51:30 CST