SQLLDR Defaults to CHAR(255)

The default length of input fields is 255 characters. If your field is longer than this, you will receive an error message:

scott@10G> select dbms_metadata.get_ddl('TABLE','T') from dual;

DBMS_METADATA.GET_DDL('TABLE','T')
--------------------------------------------------------------------------

  CREATE TABLE "SCOTT"."T"
   (    "X" NUMBER,
        "Y" DATE,
        "Z" VARCHAR2(4000)
   ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
  TABLESPACE "USERS_DATA"

load data
infile *
into table t
truncate
fields terminated by ','
(
    x                                    ,
    y     " to_date(:y,'mm/dd/yyyy ') "    ,
    z    
)

begindata   
1,02/03/2010,VERSION INFORMATION:TNS for 32-bit Windows: Version 10.1.0.2.0 - Production:Oracle Bequeath NT Protocol Adapter for 32-bit Windows: Version 10.1.0.2.0 - ProductionTime: 09-FEB-2009 11:12:21Tracing not turned on.TNS-12560: TNS:protocol adapter errorTNS:protocol adapter error

Table T, loaded from every logical record.
Insert option in effect for this table: TRUNCATE

   Column Name                  Position   Len  Term Encl Datatype
------------------------------ ---------- ----- ---- ---- ---------------------
X                                   FIRST     *   ,       CHARACTER           
Y                                    NEXT     *   ,       CHARACTER           
    SQL string for column : " to_date(:y,'mm/dd/yyyy ') "
Z                                    NEXT     *   ,       CHARACTER           

Record 1: Rejected - Error on table T, column Z.
Field in data file exceeds maximum length

This does not mean the data will not fit into the database column, but rather SQLLDR was expecting 255 bytes or less of input data, and received somewhat more than that. The solution is to simply use CHAR(N) in the control file, where N is big enough to accommodate the largest field length in the input file.

load data
infile *
into table t
truncate
fields terminated by ','
(
    x                                    ,
    y     " to_date(:y,'mm/dd/yyyy ') "    ,
    z     char(10000)
)

begindata   
1,02/03/2010,VERSION INFORMATION:TNS for 32-bit Windows: Version 10.1.0.2.0 - Production:Oracle Bequeath NT Protocol Adapter for 32-bit Windows: Version 10.1.0.2.0 - ProductionTime: 09-FEB-2009 11:12:21Tracing not turned on.TNS-12560: TNS:protocol adapter errorTNS:protocol adapter error


scott@10G> select x,y,length(z) from T;

         X Y          LENGTH(Z)
---------- --------- ----------
         1 03-FEB-10        274