This discussion is archived
1 Reply Latest reply: Dec 4, 2012 6:15 AM by Seyed_G RSS

Field in data file exceeds maximum length

Seyed_G Newbie
Currently Being Moderated
Hi all,
I am trying to run the following SQL*Loader control job on my Oracle 10g Personal Edition. Running the SQL*Loader control job results in the ‘Field in data file exceeds maximum length’ error message. Below, I am listing the control file, table definition, the data and finally the generated log.

Here is the control job;
LOAD DATA
INFILE 'C:\Temp\my_test_file.csv'
BADFILE 'C:\Temp\my_test_file.bad'
DISCARDFILE 'C:\Temp\my_test_file.dsc'
DISCARDMAX 999

INTO TABLE "SEYED"."DATASTAGE_REPORT_PATH"
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
TRAILING NULLCOLS
(
USERODBCSQL 

)
Here is the table definition:
CREATE TABLE SEYED.DATASTAGE_REPORT_PATH
(
  USERODBCSQL  VARCHAR2(4000 BYTE)
)
Here is the data:
"INSERT INTO BCDEDATA(CASENUM, COUNTY, RESCOUNTY, CASELAST, CASEFIRST, CASESTATUS, ADDRESS, CITY, STATE, ZIPCODE, GUARDIAN, INDICATOR, PHONE_NO, SUPV, DIST, CROSS1, CR1PERS, CR1IND, CROSS2, CR2PERS, CR2IND, CROSS3, CR3PERS, CR3IND, CROSS4, CR4PERS, CR4IND, FINDADDRESS, FINDCITY, FINDSTATE, FINDZIP, PROPIND, HOMEVALUE, HOMEQUITY, HOMELOTS, HOMEOWNPCT, BAPPDATE) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?);"
Here is the log:
SQL*Loader: Release 10.2.0.1.0 - Production on Mon Dec 3 15:55:55 2012

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

Control File:   c:\temp\MyControlJob.ctl
Data File:      C:\Temp\my_test_file.csv
  Bad File:     C:\Temp\my_test_file.bad
  Discard File: C:\Temp\my_test_file..dsc 
 (Allow 999 discards)

Number to load: ALL
Number to skip: 0
Errors allowed: 99
Bind array:     64 rows, maximum of 256000 bytes
Continuation:    none specified
Path used:      Conventional

Table "SEYED"."DATASTAGE_REPORT_PATH", loaded from every logical record.
Insert option in effect for this table: INSERT
TRAILING NULLCOLS option in effect

   Column Name                  Position   Len  Term Encl Datatype
------------------------------ ---------- ----- ---- ---- ---------------------
USERODBCSQL                         FIRST     *   ,    "  CHARACTER            

Record 1: Rejected - Error on table "SEYED"."DATASTAGE_REPORT_PATH", column USERODBCSQL.
Field in data file exceeds maximum length

Table "SEYED"."DATASTAGE_REPORT_PATH":
  0 Rows successfully loaded.
  1 Row 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:                  16512 bytes(64 rows)
Read   buffer bytes: 1048576

Total logical records skipped:          0
Total logical records read:             1
Total logical records rejected:         1
Total logical records discarded:        0

Run began on Mon Dec 03 15:55:55 2012
Run ended on Mon Dec 03 15:55:56 2012

Elapsed time was:     00:00:00.73
CPU time was:         00:00:00.17
Any ideas how this error could be resolved?

Thank you for your input,

Seyed
  • 1. Re: Field in data file exceeds maximum length
    Seyed_G Newbie
    Currently Being Moderated
    All,
    I found out that without indicating the length of the coluimn in the control file, SQL*Loader would use the default of 255 characters. I modified the control file by setting the length to CHAR(4000). This resolved the error.
    LOAD DATA
    INFILE 'C:\Temp\my_test_file.csv'
    BADFILE 'C:\Temp\my_test_file.bad'
    DISCARDFILE 'C:\Temp\my_test_file.dsc'
    DISCARDMAX 9999999
    
    INTO TABLE "SEYED"."DATASTAGE_REPORT_PATH"
    FIELDS TERMINATED BY ','
    ENCLOSED BY '"'
    TRAILING NULLCOLS
    (
    USERODBCSQL char(4000) NULLIF USERODBCSQL = '(null)'
    
    )
    Seyed

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points