Dave wrote:
> "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
I hadn't thought of that - the script calls a ksh script that is a
wrapper for sqlldr. I checked that wrapper, and direct=true was set.
It works fine when direct=false.
Thanks - I should have caught that one.
Michael
Received on Tue Nov 16 2004 - 11:11:46 CST
