Oracle sqlldr: Field in data file exceeds maximum length

I tried to include comments field into my migration scripts to a new DB and what was my surprise when I received error „Field in data file exceeds maximum length“ from sqlldr utility. BTW that utility is very handy – it loads CSV file and load it into specified table. All is driven by control *.ctl file.

I found this solution for this specific problem. The key is that by default each field is expected to be just 255 char long. In this case both DB had definition VARCHAR2(4000) for comments (in Oracle you can find out by sql command desc tablename and about 200 records have this field > 250.

The solution is slightly change the CTL file:

cat 100618.ctl
load data
 infile '../csv/100618.csv'
 append
 into table Emloyee
 fields terminated by "," optionally enclosed by "'"
 ( ID,NAME,COMMENTS CHAR(4000),BIRTH_DATE )

The script itself to load data based on this CTL file is easy:

bash-3.00$ cat autosqlldr.sh 
#!/bin/bash
CTLFILE=${1?Ctl file not specified!}
export ORACLE_SID=MYDB
export ORACLE_HOME=/usr/local/ora1020
export PATH=$PATH:$ORACLE_HOME/bin
cd /space/loader
sqlldr username/secretpass control=$CTLFILE