1 Reply Latest reply: Dec 4, 2012 8:15 AM by Seyed_G RSS

    Field in data file exceeds maximum length

    Seyed_G
      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
          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