RE: SQL*Loader problem - constraint violation

From: Mercadante, Thomas F <NDATFM_at_labor.state.ny.us>
Date: Tue, 08 Jul 2003 05:50:58 -0700
Message-ID: <F001.005C3DA1.20030708054429@fatcity.com>

Saira

It looks like your index is being created on the LOC column, right?

CREATE UNIQUE INDEX LCI_LOC ON
LC_F(LOC) So your change to the lc_rid column did not fix this problem.

Tom Mercadante
Oracle Certified Professional

-----Original Message-----
Sent: Tuesday, July 08, 2003 9:29 AM
To: Multiple recipients of list ORACLE-L

After reading some archives on google, I came up with this solution:

Drop the index.
Use sqlldr to append to the table - for the id column, use: (lc_rid sequence (max,1) ...)
Recreate the index.

However, when I did this, I received an error message saying that I can't have duplicates in the index (of course not because the index is to be unique). But I checked lc_rid for duplicates and didn't find any.

So what am I missing here?

Thanks,
Saira

-----Original Message-----
Saira Somani-Mendelin
Sent: July 7, 2003 3:59 PM
To: Multiple recipients of list ORACLE-L

I'm new to sqlldr and here is my control file:

LOAD DATA
INFILE '/home/oracle/116.csv'
BADFILE '/home/oracle/116.bad'
DISCARDFILE '/home/oracle/116.rej'
APPEND INTO TABLE LC_F
FIELDS TERMINATED BY ','
TRAILING NULLCOLS

(lc_rid,loc,loc_type,loc_size,sku,pkg,lot,uc1,uc2,uc3,zone,area,loc_stt,
cycc_stat,size_load_cap,store_seq,cmd_seq,hgt,wid,dpth,wgt,pal_req,ck_di
git,repl_batch_qty,repl_dynam_qty,repl_uom,trig_batch_qty,trig_dynam_qty

,trig_uom,eq_class,tag_track,cap_by_load,cycc_date,whse,route,carrier,ca rrier_grp,trailer,carrier_trailer,seal,pgmmod,usrmod,modcnt,dtimecre,dti memod,empty,linehaul_carrier,max_fp_cap,search,aisle_grp)

lc_rid is a unique index primary key and I'm not sure how to handle that. Do I include it as a field in my INFILE? Do I define it as NEXTVAL somehow?

Here is a snippet from the log file:

Record 1: Rejected - Error on table LC_F. ORA-00001: unique constraint (TEST65W.LCI_LOC) violated

LCI_LOC looks like this:

CREATE UNIQUE INDEX LCI_LOC ON
LC_F(LOC) If anyone can help, it would be very appreciated. Thank you,
Saira

--

Please see the official ORACLE-L FAQ: http://www.orafaq.net
--

Author: Saira Somani-Mendelin
INET: [EMAIL PROTECTED]

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------

To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).

--

Please see the official ORACLE-L FAQ: http://www.orafaq.net
--

Author: Saira Somani-Mendelin
INET: [EMAIL PROTECTED]

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------