2 Replies Latest reply: Aug 23, 2013 8:29 AM by LostInPermuation RSS

    on load, getting error:  Field in data file exceeds maximum length

    LostInPermuation

      Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

      PL/SQL Release 11.2.0.3.0 - Production

      CORE    11.2.0.3.0    Production

      TNS for Solaris: Version 11.2.0.3.0 - Production

      NLSRTL Version 11.2.0.3.0 - Production

       

       

      I'm trying to load a table, small in size (110 rows, 6 columns).  One of the columns, called NOTES is erroring when I run the load.  It is saying that the column size exceeds max limit.  As you can see here, the table column is set to 4000 Bytes)

      CREATE TABLE NRIS.NRN_REPORT_NOTES

      (

        NOTES_CN      VARCHAR2(40 BYTE)               DEFAULT sys_guid()            NOT NULL,

        REPORT_GROUP  VARCHAR2(100 BYTE)              NOT NULL,

        AREACODE      VARCHAR2(50 BYTE)               NOT NULL,

        ROUND         NUMBER(3)                       NOT NULL,

        NOTES         VARCHAR2(4000 BYTE),

        LAST_UPDATE   TIMESTAMP(6) WITH TIME ZONE     DEFAULT systimestamp          NOT NULL

      )

      TABLESPACE USERS

      RESULT_CACHE (MODE DEFAULT)

      PCTUSED    0

      PCTFREE    10

      INITRANS   1

      MAXTRANS   255

      STORAGE    (

                  INITIAL          80K

                  NEXT             1M

                  MINEXTENTS       1

                  MAXEXTENTS       UNLIMITED

                  PCTINCREASE      0

                  BUFFER_POOL      DEFAULT

                  FLASH_CACHE      DEFAULT

                  CELL_FLASH_CACHE DEFAULT

                 )

      LOGGING

      NOCOMPRESS

      NOCACHE

      NOPARALLEL

      MONITORING;

      I did a little investigating, and it doesn't add up.

      when i run

      select max(lengthb(notes)) from NRIS.NRN_REPORT_NOTES

      I get a return of

      643

      .

       

      That tells me that the largest size instance of that column is only 643 bytes.  But EVERY insert is failing.

      Here is the loader file header, and first couple of inserts:

       

      LOAD DATA

      INFILE *

      BADFILE './NRIS.NRN_REPORT_NOTES.BAD'

      DISCARDFILE './NRIS.NRN_REPORT_NOTES.DSC'

      APPEND INTO TABLE NRIS.NRN_REPORT_NOTES

      Fields terminated by ";" Optionally enclosed by '|'

      (

        NOTES_CN,

        REPORT_GROUP,

        AREACODE,

        ROUND NULLIF (ROUND="NULL"),

        NOTES,

        LAST_UPDATE TIMESTAMP WITH TIME ZONE "MM/DD/YYYY HH24:MI:SS.FF9 TZR" NULLIF (LAST_UPDATE="NULL")

      )

      BEGINDATA

      |E2ACF256F01F46A7E0440003BA0F14C2|;|DEMOGRAPHICS|;|A01003|;3;|Demographic results show that 46 percent of visits are made by females.  Among racial and ethnic minorities, the most commonly encountered are Native American (4%) and Hispanic / Latino (2%).  The age distribution shows that the Bitterroot has a relatively small proportion of children under age 16 (14%) in the visiting population.  People over the age of 60 account for about 22% of visits.   Most of the visitation is from the local area.  More than 85% of visits come from people who live within 50 miles.|;07/29/2013 16:09:27.000000000 -06:00

      |E2ACF256F02046A7E0440003BA0F14C2|;|VISIT DESCRIPTION|;|A01003|;3;|Most visits to the Bitterroot are fairly short.  Over half of the visits last less than 3 hours.  The median length of visit to overnight sites is about 43 hours, or about 2 days.  The average Wilderness visit lasts only about 6 hours, although more than half of those visits are shorter than 3 hours long.   Most visits come from people who are fairly frequent visitors.  Over thirty percent are made by people who visit between 40 and 100 times per year.  Another 8 percent of visits are from people who report visiting more than 100 times per year.|;07/29/2013 16:09:27.000000000 -06:00

      |E2ACF256F02146A7E0440003BA0F14C2|;|ACTIVITIES|;|A01003|;3;|The most frequently reported primary activity is hiking/walking (42%), followed by downhill skiing (12%), and hunting (8%).  Over half of the visits report participating in relaxing and viewing scenery.|;07/29/2013 16:09:27.000000000 -06:00

       

      Here is the full beginning of the loader log, ending after the first row return.  (They ALL say the same error)

      SQL*Loader: Release 10.2.0.4.0 - Production on Thu Aug 22 12:09:07 2013

       

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

       

      Control File:   NRIS.NRN_REPORT_NOTES.ctl

      Data File:      NRIS.NRN_REPORT_NOTES.ctl

        Bad File:     ./NRIS.NRN_REPORT_NOTES.BAD

        Discard File: ./NRIS.NRN_REPORT_NOTES.DSC

      (Allow all discards)

       

      Number to load: ALL

      Number to skip: 0

      Errors allowed: 50

      Bind array:     64 rows, maximum of 256000 bytes

      Continuation:    none specified

      Path used:      Conventional

       

      Table NRIS.NRN_REPORT_NOTES, loaded from every logical record.

      Insert option in effect for this table: APPEND

       

         Column Name                  Position   Len  Term Encl Datatype

      ------------------------------ ---------- ----- ---- ---- ---------------------

      NOTES_CN                            FIRST     *   ;  O(|) CHARACTER

      REPORT_GROUP                         NEXT     *   ;  O(|) CHARACTER

      AREACODE                             NEXT     *   ;  O(|) CHARACTER

      ROUND                                NEXT     *   ;  O(|) CHARACTER

          NULL if ROUND = 0X4e554c4c(character 'NULL')

      NOTES                                NEXT     *   ;  O(|) CHARACTER

      LAST_UPDATE                          NEXT     *   ;  O(|) DATETIME MM/DD/YYYY HH24:MI:SS.FF9 TZR

          NULL if LAST_UPDATE = 0X4e554c4c(character 'NULL')

       

      Record 1: Rejected - Error on table NRIS.NRN_REPORT_NOTES, column NOTES.

      Field in data file exceeds maximum length...

       

       

      I am not seeing why this would be failing.