This discussion is archived
1 Reply Latest reply: Oct 30, 2013 3:43 AM by Ivan Bush RSS

Field in data file exceeds maximum length

DBA Associate Newbie
Currently Being Moderated

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);

Legend

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