Oracle: SQL Loader example: explicitly declare a column's width in the control file in order to prevent the *Field in data file exceeds maximum length* error message

Create table statement

create table tq84_sql_loader_6 (
   id    number not null,
   tx    varchar2(4000)
);

create table tq84_sql_loader_6_ok (
   id    number not null,
   tx    varchar2(4000)
);

Control file without explicit declaration of field with

load data
into table tq84_sql_loader_6
fields terminated by "|"
(
  id,
  tx
)

»Correct« control file

load data
into table tq84_sql_loader_6_ok
fields terminated by "|"
(
  id,
  tx char(4000)
)

The data file

1|This line should be fine
2|This line contains more than 255 characters and will cause an "Field in data file exceeds maximum length" error message. Of course, I need some more text here in order to reach this 255 character limit. For example, by adding some city names: New York, Los Angeles, Berlin, Berne, Lucerne, Winterthur, Basle, Paris, Tokyo, Cape Town etc.
3|This line should be fine, too

Running it all

start create_table

host  sqlldr control=load_6.ctl    data=load_6.dat userid=rene
host  sqlldr control=load_6_ok.ctl data=load_6.dat userid=rene

select id, substrb(tx,1, 50) from tq84_sql_loader_6;

prompt
prompt  Note: Second record not loaded
prompt

drop table tq84_sql_loader_6    purge;

select id, substrb(tx,1, 50) from tq84_sql_loader_6_ok;
prompt
prompt  Note: Second record was loaded
prompt

drop table tq84_sql_loader_6_ok purge;

See also