I'm pretty sure your problems come from not declaring that field's type and its length. Change your controlfile to :

...
into table calldetail_bcp_new
fields terminated by '%%'
TRAILING NULLCOLS
(CallId,
CallType,
... -- omit some columns here
CallEventLog char (2000) "substr(:CallEventLog,1,2000)",
...)

BTW, I don't understand why you are using "substr ()" part, you said the data was originaly stored in VARCHAR2(2000) column! I would simply throw that out from control file.

Now why were you rejected with VARCHAR2 specification in the controlfile? Because SQL*Loader does not recognise that type as its valid internal or external datatype. Don't confuse this with database datatype, we are talking about SQL*Loader's own datatypes. So specifying CHAR datatype in SQL*Loader simply means the data in that field is character string, can be with variable or fixed length. Do not confuse this with database type CHAR which is fixed length.

When you don't specify field's type SQL*Loader takes it a CHAR (external) datatype with implicit length limit of 255. For longer fields you have to declare datatype explicitely, specifying also its maximum length.


[Edited by jmodic on 01-30-2002 at 05:01 PM]