1 Reply Latest reply: Oct 30, 2013 5:43 AM by Ivan Bush RSS

    Field in data file exceeds maximum length

    DBA Associate

      Hi,



      I am trying to run the following SQL*Loader control job on my Oracle 11gR2 . 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.Please Suggest. Thanks

      It's giving me an error when I run SQL Loader on it,

       

      Record 61: Rejected - Error on table RMS_TABLE, column GEOM.SDO_POINT.X.

      Field in data file exceeds maximum length.

       

      Here is my SQL Loader Control file,

       

      LOAD DATA

      INFILE *

      TRUNCATE

      CONTINUEIF NEXT(1:1) = '#'

      INTO TABLE RMS_TABLE

      FIELDS TERMINATED BY '|'

      TRAILING NULLCOLS (

         Status NULLIF Status = BLANKS,

         Score,

         Match_type NULLIF Match_type = BLANKS,

         Match_addr NULLIF Match_addr = BLANKS,

         Side NULLIF Side = BLANKS,

         User_fld NULLIF User_fld = BLANKS,

         Addr_type NULLIF Addr_type = BLANKS,

         ARC_Street NULLIF ARC_Street = BLANKS,

         ARC_City NULLIF ARC_City = BLANKS,

         ARC_State NULLIF ARC_State = BLANKS,

         ARC_ZIP NULLIF ARC_ZIP = BLANKS,

         INCIDENT_N NULLIF INCIDENT_N = BLANKS,

         CDATE NULLIF CDATE = BLANKS,

         CTIME NULLIF CTIME = BLANKS,

         DISTRICT NULLIF DISTRICT = BLANKS,

      LOCATIONNULLIF LOCATION = BLANKS,
         MAPLOCATIONULLIF MAPLOCATIO = BLANKS,
         LOCATION_TNULLIF LOCATION_T = BLANKS,
         DAYCODENULLIF DAYCODE = BLANKS,
         CAUSENULLIF CAUSE = BLANKS,

         GEOM COLUMN OBJECT

         (

           SDO_GTYPE       INTEGER EXTERNAL,

           SDO_POINT COLUMN OBJECT

             (X            FLOAT EXTERNAL,

              Y            FLOAT EXTERNAL)

         )

      )



      CREATE TABLE RMS_TABLE (

        Status VARCHAR2(1),

        Score NUMBER,

        Match_type VARCHAR2(2),

        Match_addr VARCHAR2(120),

        Side VARCHAR2(1),

        User_fld VARCHAR2(120),

        Addr_type VARCHAR2(20),

        ARC_Street VARCHAR2(100),

        ARC_City VARCHAR2(40),

        ARC_State VARCHAR2(20),

        ARC_ZIP VARCHAR2(10),

        INCIDENT_N VARCHAR2(9),

        CDATE VARCHAR2(10),

        CTIME VARCHAR2(8),

        DISTRICT VARCHAR2(4),

        LOCATION VARCHAR2(128),

        MAPLOCATIO VARCHAR2(100),

        LOCATION_T VARCHAR2(42),

        DAYCODE VARCHAR2(1),

        CAUSE VARCHAR2(17),

        GEOM MDSYS.SDO_GEOMETRY);