Oracle sqlldr: Field in data file exceeds maximum length

Filed under: database — nax @ 16:17

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'
 into table Emloyee
 fields terminated by "," optionally enclosed by "'"

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

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

Comments are closed.

Powered by WordPress