Problem with Sqllldr

Had a problem loading data from an ASCII file using sqlldr which is worth noting down.

The dta file contained two description fields which were about 1200 characters long.
Even though I had declared the relevant table columns as Varchar2(4000), the records were rejected saying Max Field Length exceeded.

This was the original CTL file:

LOAD DATA
INFILE 'C:\PN\smq_list.asc' "str X'0a0d'"
BADFILE 'C:\PN\smq_list.bad'
DISCARDFILE 'C:\PN\smq_list.dsc'

INTO TABLE "SMQ_LIST"

FIELDS TERMINATED BY '$'
OPTIONALLY ENCLOSED BY '"'
TRAILING NULLCOLS
(SMQ_CODE INTEGER EXTERNAL,
SMQ_NAME CHAR,
SMQ_LEVEL INTEGER EXTERNAL,
SMQ_DESCRIPTION CHAR,
SMQ_SOURCE CHAR,
SMQ_NOTE CHAR,
MEDDRA_VERSION CHAR,
STATUS CHAR,
SMQ_ALGORITHM CHAR
)


I wasted a lot of time thinking there was some espace character in the fields. Especially if I took just a chunk of the fields, the ctl worked.

The problem was solved however by modifying the CTL file:

LOAD DATA
INFILE 'c:\pn\smq_list.asc'
BADFILE 'c:\pn\smq_list.bad'
DISCARDFILE 'c:\pn\smq_list.dsc'

INTO TABLE "SMQ_LIST"

FIELDS TERMINATED BY '$'
OPTIONALLY ENCLOSED BY '"'
TRAILING NULLCOLS
(SMQ_CODE INTEGER EXTERNAL,
SMQ_NAME CHAR,
SMQ_LEVEL INTEGER EXTERNAL,
SMQ_DESCRIPTION CHAR(2000),
SMQ_SOURCE CHAR(2000),
SMQ_NOTE CHAR,
MEDDRA_VERSION CHAR,
STATUS CHAR,
SMQ_ALGORITHM CHAR
)

It looks like there is a default length for CHAR fields, and if we need it to be over that amount, we should specify in the CTL file.