The solution is quite simple...
Against each of your field names in the control file, you need to specify a
datatype. If you put CHAR(4000) after each field, you will find each line
loads succesfully.
Robert Nicholson <steffi_at_shell8.ba.best.com> wrote in message
news:yl33ddvhrrq.fsf_at_shell8.ba.best.com...
> I'm going out of mind on this one. I'm trying to load jobserve's comma
> deliminited file into Oracle using SQL Loader. I usually have no problem
doing
> this ..
>
> I'm using Oracle 815
>
> Here's what I have as my schema
>
> ID NUMBER
> JOB_TYPE
VARCHAR2(4000)
> DESCRIPTION
VARCHAR2(4000)
> SKILLS
VARCHAR2(4000)
> LOCATION
VARCHAR2(4000)
> START_DATE
VARCHAR2(4000)
> TERM
VARCHAR2(4000)
> RATE
VARCHAR2(4000)
> AGENCY
VARCHAR2(4000)
> CONTACT
VARCHAR2(4000)
> FIELD10
VARCHAR2(4000)
> FIELD11
VARCHAR2(4000)
> EMAIL
VARCHAR2(4000)
> URL
VARCHAR2(4000)
> JOB_REFERENCE
VARCHAR2(4000)
>
> Here's what I have a my control file
>
> load data infile *
> replace
> into table listing
> fields terminated by "," optionally enclosed by '"'
> ( id sequence(max,1),
> job_type,
> description,
> skills,
> location,
> start_date,
> term,
> rate,
> agency,
> contact,
> field10,
> field11,
> email,
> url,
> job_reference)
>
> Here's the offending record that claims to exceed the maximum length
>
> I will not break the lines
>
> "Contract","'Diamonds Are Forever' Analyst Programmer","AAA US investment
bank are looking for an experienced Analyst Programmer.You will be a good
team player & have excellent interpersonal & communication skills.You will
have Gloss HV.Knowledge of global system
design&implementation,Unix,Sybase,design & development experience.Good
business knowledge would be very beneficial.Excellent
rates+enviroment.","City, London","ASAP","contract",,"Olympian
Consultancy","Jay Aaronson","0207 786 8700","0207 786
8711","jay_aaronson@plexian.com","http://www.olympian.co.uk","JS",
>
> Why does this exceed anything when it's variable format and 4000 is the
> limit?
>
> thank you.
>
> Here's the log file
>
>
> SQL*Loader: Release 8.1.5.0.0 - Production on Sun Feb 4 13:35:09 2001
>
> (c) Copyright 1999 Oracle Corporation. All rights reserved.
>
> Control File: d:\java\projects\jsp\jspdev\db\jobserve.ctl
> Data File: d:\archives\jobserve.csv
> Bad File: d:\java\projects\jsp\jspdev\db\jobserve.bad
> Discard File: none specified
>
> (Allow all discards)
>
> Number to load: ALL
> Number to skip: 0
> Errors allowed: 50
> Bind array: 64 rows, maximum of 65536 bytes
> Continuation: none specified
> Path used: Conventional
>
> Table LISTING, loaded from every logical record.
> Insert option in effect for this table: REPLACE
>
> Column Name Position Len Term Encl Datatype
> ------------------------------ ---------- ----- ---- ---- ----------------
> ID SEQUENCE (MAX,
1)
> JOB_TYPE FIRST * , O(") CHARACTER
> DESCRIPTION NEXT * , O(") CHARACTER
> SKILLS NEXT * , O(") CHARACTER
> LOCATION NEXT * , O(") CHARACTER
> START_DATE NEXT * , O(") CHARACTER
> TERM NEXT * , O(") CHARACTER
> RATE NEXT * , O(") CHARACTER
> AGENCY NEXT * , O(") CHARACTER
> CONTACT NEXT * , O(") CHARACTER
> FIELD10 NEXT * , O(") CHARACTER
> FIELD11 NEXT * , O(") CHARACTER
> EMAIL NEXT * , O(") CHARACTER
> URL NEXT * , O(") CHARACTER
> JOB_REFERENCE NEXT * , O(") CHARACTER
>
> Record 2: Rejected - Error on table LISTING, column SKILLS.
> Field in data file exceeds maximum length
> Record 6: Rejected - Error on table LISTING, column SKILLS.
> Field in data file exceeds maximum length
> Record 7: Rejected - Error on table LISTING, column SKILLS.
> Field in data file exceeds maximum length
> Record 8: Rejected - Error on table LISTING, column SKILLS.
> Field in data file exceeds maximum length
> Record 9: Rejected - Error on table LISTING, column SKILLS.
> Field in data file exceeds maximum length
> Record 10: Rejected - Error on table LISTING, column SKILLS.
> Field in data file exceeds maximum length
> Record 12: Rejected - Error on table LISTING, column SKILLS.
> Field in data file exceeds maximum length
> Record 13: Rejected - Error on table LISTING, column SKILLS.
> Field in data file exceeds maximum length
> Record 15: Rejected - Error on table LISTING, column SKILLS.
> Field in data file exceeds maximum length
> Record 20: Rejected - Error on table LISTING, column SKILLS.
> Field in data file exceeds maximum length
> Record 21: Rejected - Error on table LISTING, column SKILLS.
> Field in data file exceeds maximum length
> Record 25: Rejected - Error on table LISTING, column SKILLS.
> Field in data file exceeds maximum length
> Record 27: Rejected - Error on table LISTING, column SKILLS.
> Field in data file exceeds maximum length
> Record 29: Rejected - Error on table LISTING, column SKILLS.
> Field in data file exceeds maximum length
> Record 30: Rejected - Error on table LISTING, column SKILLS.
> Field in data file exceeds maximum length
> Record 31: Rejected - Error on table LISTING, column SKILLS.
> Field in data file exceeds maximum length
> Record 34: Rejected - Error on table LISTING, column SKILLS.
> Field in data file exceeds maximum length
> Record 35: Rejected - Error on table LISTING, column SKILLS.
> Field in data file exceeds maximum length
> Record 37: Rejected - Error on table LISTING, column SKILLS.
> Field in data file exceeds maximum length
> Record 38: Rejected - Error on table LISTING, column SKILLS.
> Field in data file exceeds maximum length
> Record 42: Rejected - Error on table LISTING, column SKILLS.
> Field in data file exceeds maximum length
> Record 44: Rejected - Error on table LISTING, column SKILLS.
> Field in data file exceeds maximum length
> Record 45: Rejected - Error on table LISTING, column SKILLS.
> Field in data file exceeds maximum length
> Record 49: Rejected - Error on table LISTING, column SKILLS.
> Field in data file exceeds maximum length
> Record 50: Rejected - Error on table LISTING, column SKILLS.
> Field in data file exceeds maximum length
> Record 63: Rejected - Error on table LISTING, column SKILLS.
> Field in data file exceeds maximum length
> Record 66: Rejected - Error on table LISTING, column SKILLS.
> Field in data file exceeds maximum length
> Record 72: Rejected - Error on table LISTING, column SKILLS.
> Field in data file exceeds maximum length
> Record 78: Rejected - Error on table LISTING, column SKILLS.
> Field in data file exceeds maximum length
> Record 80: Rejected - Error on table LISTING, column SKILLS.
> Field in data file exceeds maximum length
> Record 83: Rejected - Error on table LISTING, column SKILLS.
> Field in data file exceeds maximum length
> Record 84: Rejected - Error on table LISTING, column SKILLS.
> Field in data file exceeds maximum length
> Record 86: Rejected - Error on table LISTING, column SKILLS.
> Field in data file exceeds maximum length
> Record 94: Rejected - Error on table LISTING, column SKILLS.
> Field in data file exceeds maximum length
> Record 97: Rejected - Error on table LISTING, column SKILLS.
> Field in data file exceeds maximum length
> Record 98: Rejected - Error on table LISTING, column SKILLS.
> Field in data file exceeds maximum length
> Record 104: Rejected - Error on table LISTING, column SKILLS.
> Field in data file exceeds maximum length
> Record 108: Rejected - Error on table LISTING, column SKILLS.
> Field in data file exceeds maximum length
> Record 109: Rejected - Error on table LISTING, column SKILLS.
> Field in data file exceeds maximum length
> Record 110: Rejected - Error on table LISTING, column SKILLS.
> Field in data file exceeds maximum length
> Record 112: Rejected - Error on table LISTING, column SKILLS.
> Field in data file exceeds maximum length
> Record 113: Rejected - Error on table LISTING, column SKILLS.
> Field in data file exceeds maximum length
> Record 114: Rejected - Error on table LISTING, column SKILLS.
> Field in data file exceeds maximum length
> Record 119: Rejected - Error on table LISTING, column SKILLS.
> Field in data file exceeds maximum length
> Record 120: Rejected - Error on table LISTING, column SKILLS.
> Field in data file exceeds maximum length
> Record 123: Rejected - Error on table LISTING, column SKILLS.
> Field in data file exceeds maximum length
> Record 124: Rejected - Error on table LISTING, column SKILLS.
> Field in data file exceeds maximum length
> Record 126: Rejected - Error on table LISTING, column SKILLS.
> Field in data file exceeds maximum length
> Record 128: Rejected - Error on table LISTING, column SKILLS.
> Field in data file exceeds maximum length
> Record 129: Rejected - Error on table LISTING, column SKILLS.
> Field in data file exceeds maximum length
> Record 135: Rejected - Error on table LISTING, column SKILLS.
> Field in data file exceeds maximum length
>
> MAXIMUM ERROR COUNT EXCEEDED - Above statistics reflect partial run.
>
> Table LISTING:
> 84 Rows successfully loaded.
> 51 Rows not loaded due to data errors.
> 0 Rows not loaded because all WHEN clauses were failed.
> 0 Rows not loaded because all fields were null.
>
>
> Space allocated for bind array: 65430 bytes(18 rows)
> Space allocated for memory besides bind array: 0 bytes
>
> Total logical records skipped: 0
> Total logical records read: 135
> Total logical records rejected: 51
> Total logical records discarded: 0
>
> Run began on Sun Feb 04 13:35:09 2001
> Run ended on Sun Feb 04 13:35:09 2001
>
> Elapsed time was: 00:00:00.18
> CPU time was: 00:00:00.08
Received on Sun Feb 04 2001 - 08:29:10 CST
