3 Replies Latest reply: Mar 19, 2013 4:24 PM by 838940 RSS

    SQL loader Field in data file exceeds maximum length for CLOB column

    Raluce
      Hi all

      I'm loading data from text file separated by TAB and i got the error below for some lines.
      Event the column is CLOB data type is there a limitation of the size of a CLOB data type.
      The error is:

      Record 74: Rejected - Error on table _TEMP, column DEST.
      Field in data file exceeds maximum length

      I'm using SQL Loader and the database is oracle 11g r2 on linux Red hat 5

      Here are the line causing the error fronm my data file and my table description for test:
      create table TEMP
      (
      CODE VARCHAR2(100),
      DESC VARCHAR2(500),
      RATE     FLOAT,
      INCREASE VARCHAR2(20),
      COUNTRY VARCHAR2(500),
      DEST     CLOB,
      WEEK     VARCHAR2(10),
      IS_SAT VARCHAR2(50),
      IS_SUN VARCHAR2(50)
      )
      
      CONTROL FILE:
      
      LOAD DATA
      INTO TABLE TEMP
      APPEND
      FIELDS TERMINATED BY X'9' TRAILING NULLCOLS
      (
      CODE,
      DESC,
      RATE,
      INCREASE,
      COUNTRY),
      DEST,
      WEEK,
      IS_SAT,
      IS_SUN
      )
      
      Data file:
      
      BHS Mobile     Bahamas - Mobile     0.1430          1     "242357, 242359, 242375, 242376, 242395, 242421, 242422, 242423, 242424, 242425, 242426, 242427, 242428, 242429, 242431, 242432, 242433, 242434, 242435, 242436, 242437, 242438, 242439, 242441, 242442, 242443, 242445, 242446, 242447, 242448, 242449, 242451, 242452, 242453, 242454, 242455, 242456, 242457, 242458, 242462, 242463, 242464, 242465, 242466, 242467, 242468, 24247, 242524, 242525, 242533, 242535, 242544, 242551, 242552, 242553, 242554, 242556, 242557, 242558, 242559, 242565, 242577, 242636, 242646, 242727"               
      BOL Mobile ENTEL     Bolivia - Mobile Entel     0.0865     Increase     591     "67, 68, 71, 72, 73, 740, 7410, 7411, 7412, 7413, 7414, 7415, 7420, 7421, 7422, 7423, 7424, 7425, 7430, 7431, 7432, 7433, 7434, 7435, 7436, 7437, 7440, 7441, 7442, 7443, 7444, 7445, 7450, 7451, 7452, 7453, 7454, 7455, 746, 7470, 7471, 7472, 7475, 7476, 7477, 7480, 7481, 7482, 7483, 7484, 7485, 7486, 7490, 7491, 7492, 7493, 7494, 7495, 7496"               
      Thank you.