Can anyone see why sqlldr thinks the last field in the below example
record exceeds maximum length?

I show the last field as 1001 characters, including the newline. That
equals 1000 without the newline, and that is the fieldsize,
CHAR(1000).

For grins, I also deleted 1, then 12, then 50 spaces from the middle
of the record it is complaining about, and sqlldr still gave the same
error each time.

Following is a record of the run with 1 of the spaces removed from the
offending record. Following that is a dump of the record itself so
you can see it.

$ ls -1
ex_cust_remarks.ctl
ex_cust_remarks.foo
ex_cust_remarks.foo-1
ex_cust_remarks_cre.sql

$ cat ex_cust_remarks_cre.sql

CREATE TABLE EX_CUST_REMARKS&1 (
CUSTRE_CUST_ID CHAR(15),
CUSTRE_REMARK_DT DATE,
CUSTRE_REMARK_TM CHAR(08),
CUSTRE_REMARK_TYPE_CD CHAR(03),
CUSTRE_AUTO_DISPLAY_FLAG CHAR(01),
CUSTRE_PERSON_ID CHAR(15),
CUSTRE_REMARK_TEXT CHAR(1000)
)
TABLESPACE DATA04
STORAGE(
INITIAL 512M
NEXT 512M
MINEXTENTS 3
);

CREATE INDEX EX_CUST_REMARKS_INDEX_1&1 ON EX_CUST_REMARKS&1
(CUSTRE_CUST_ID)
TABLESPACE DATA04
;

$ cat ex_cust_remarks.ctl
-- Name : ex_cust_remarks.ctl
--
OPTIONS (SILENT=(FEEDBACK,DISCARDS) DIRECT=TRUE)
LOAD DATA
APPEND
INTO TABLE EX_CUST_REMARKS
FIELDS TERMINATED BY '^|^'
-- OPTIONALLY ENCLOSED BY '\\^/'
OPTIONALLY ENCLOSED BY '"'
(

CUSTRE_CUST_ID CHAR nullif CUSTRE_CUST_ID
= '(null)',
CUSTRE_REMARK_DT DATE "DD-MON-YYYY
HH24:MI:SS" nullif CUSTRE_REMARK_DT = '(null)',
CUSTRE_REMARK_TM CHAR nullif
CUSTRE_REMARK_TM = '(null)',
CUSTRE_REMARK_TYPE_CD CHAR nullif
CUSTRE_REMARK_TYPE_CD = '(null)',
CUSTRE_AUTO_DISPLAY_FLAG CHAR nullif
CUSTRE_AUTO_DISPLAY_FLAG = '(null)',
CUSTRE_PERSON_ID CHAR nullif
CUSTRE_PERSON_ID = '(null)',
CUSTRE_REMARK_TEXT CHAR nullif
CUSTRE_REMARK_TEXT = '(null)'
)

$ sqlplus $EX

SQL*Plus: Release 9.2.0.6.0 - Production on Tue May 22 11:50:19 2007

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.


Connected to:
Oracle9i Enterprise Edition Release 9.2.0.6.0 - 64bit Production
With the Partitioning option
JServer Release 9.2.0.6.0 - Production

SQL> drop table ex_cust_remarks;

Table dropped.

SQL> @ex_cust_remarks_cre ''
old 1: CREATE TABLE EX_CUST_REMARKS&1 (
new 1: CREATE TABLE EX_CUST_REMARKS (

Table created.

old 1: CREATE INDEX EX_CUST_REMARKS_INDEX_1&1 ON EX_CUST_REMARKS&1
new 1: CREATE INDEX EX_CUST_REMARKS_INDEX_1 ON EX_CUST_REMARKS

Index created.

SQL> quit
Disconnected from Oracle9i Enterprise Edition Release 9.2.0.6.0 -
64bit Production
With the Partitioning option
JServer Release 9.2.0.6.0 - Production

$ cat ex_cust_remarks.foo-1 | sqlldr ${EX_ORA_USER}/${EX_ORA_PASS}@$
{EX_ORA_DB} control=./ex_cust_remarks.ctl data=\"-\"
log=ex_cust_remarks.foo.log bad=ex_cust_remarks.foo.bad
2>>ex_cust_remarks.foo.err 1>>ex_cust_remarks.foo.out

$ ls -1tr
ex_cust_remarks.ctl
ex_cust_remarks_cre.sql
ex_cust_remarks.foo
ex_cust_remarks.foo-1
ex_cust_remarks.foo.out
ex_cust_remarks.foo.log
ex_cust_remarks.foo.err
ex_cust_remarks.foo.bad

$ cat ex_cust_remarks.foo.log

SQL*Loader: Release 9.2.0.6.0 - Production on Tue May 22 11:51:20 2007

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

Control File: ./ex_cust_remarks.ctl
Data File: -.dat
Bad File: ex_cust_remarks.foo.bad
Discard File: none specified

(Allow all discards)

Number to load: ALL
Number to skip: 0
Errors allowed: 50
Continuation: none specified
Path used: Direct
Silent options: FEEDBACK and DISCARDS

Table EX_CUST_REMARKS, loaded from every logical record.
Insert option in effect for this table: APPEND

Column Name Position Len Term Encl Datatype
------------------------------ ---------- ----- ---- ----
---------------------
CUSTRE_CUST_ID FIRST * O(")
CHARACTER
Terminator string : '^|^'
NULL if CUSTRE_CUST_ID = 0X286e756c6c29(character '(null)')
CUSTRE_REMARK_DT NEXT * O(") DATE DD-MON-
YYYY HH24:MI:SS
Terminator string : '^|^'
NULL if CUSTRE_REMARK_DT = 0X286e756c6c29(character '(null)')
CUSTRE_REMARK_TM NEXT * O(")
CHARACTER
Terminator string : '^|^'
NULL if CUSTRE_REMARK_TM = 0X286e756c6c29(character '(null)')
CUSTRE_REMARK_TYPE_CD NEXT * O(")
CHARACTER
Terminator string : '^|^'
NULL if CUSTRE_REMARK_TYPE_CD = 0X286e756c6c29(character '(null)')
CUSTRE_AUTO_DISPLAY_FLAG NEXT * O(")
CHARACTER
Terminator string : '^|^'
NULL if CUSTRE_AUTO_DISPLAY_FLAG = 0X286e756c6c29(character
'(null)')
CUSTRE_PERSON_ID NEXT * O(")
CHARACTER
Terminator string : '^|^'
NULL if CUSTRE_PERSON_ID = 0X286e756c6c29(character '(null)')
CUSTRE_REMARK_TEXT NEXT * O(")
CHARACTER
Terminator string : '^|^'
NULL if CUSTRE_REMARK_TEXT = 0X286e756c6c29(character '(null)')

Record 1: Rejected - Error on table EX_CUST_REMARKS, column
CUSTRE_REMARK_TEXT.
Field in data file exceeds maximum length

Table EX_CUST_REMARKS:
0 Rows successfully loaded.
1 Row not loaded due to data errors.
0 Rows not loaded because all WHEN clauses were failed.
0 Rows not loaded because all fields were null.

Bind array size not used in direct path.
Column array rows : 5000
Stream buffer bytes: 256000
Read buffer bytes: 1048576

Total logical records skipped: 0
Total logical records read: 1
Total logical records rejected: 1
Total logical records discarded: 0
Total stream buffers loaded by SQL*Loader main thread: 0
Total stream buffers loaded by SQL*Loader load thread: 0

Run began on Tue May 22 11:51:20 2007
Run ended on Tue May 22 11:51:20 2007

Elapsed time was: 00:00:00.20
CPU time was: 00:00:00.11


Here is some info on the example record:
$ cat ex_cust_remarks.foo | perl -ne '@fields = split /\^\|\^/; print
$fields[6];' | wc -c
1001

$ cat ex_cust_remarks.foo | perl -ne 'chomp; @fields = split /\^\|\^/;
print $fields[6];' | wc -c
1000

$ cat ex_cust_remarks.foo-1 | perl -ne '@fields = split /\^\|\^/;
print $fields[6];' | wc -c
1000

$ cat ex_cust_remarks.foo-1 | perl -ne 'chomp; @fields = split /\^\|
\^/; print $fields[6];' | wc -c
999

$ echo '<record>' && fold -w 40 ex_cust_remarks.foo && echo '</
record>'
<record>
XXXXXXXXXXXXXXX^|^13-APR-2003 00:00:00^|
^XXXXXXXX^|^XX ^|^X^|^XXX9999 ^|^
XXXXXXXX: XXXXX XXXXX XXXXX: XXXX - XXX
XXXXX XXXXXXX XX XXX-XXXX XXXXXXXXXXX X
X: 9999






















</record>

$ vis -wo -F40 ex_cust_remarks.foo
XXXXXXXXXXXXXXX^|^13-APR-2003\04000:00\
:00^|^XXXXXXXX^|^XX\040^|^X^|^XXX9999\
\040\040\040\040\040\040\040\040^|^XXX\
XXXXX:\040XXXXX\040XXXXX\040\040XXXXX:\
\040XXXX\040-\040XXXXXXXX\040XXXXXXX\
\040XX\040XXX-XXXX\040\040XXXXXXXXXXX\
\040XX:\0409999\040\040\040\040\040\
\040\040\040\040\040\040\040\040\040\
\040\040\040\040\040\040\040\040\040\
\040\040\040\040\040\040\040\040\040\
\040\040\040\040\040\040\040\040\040\
\040\040\040\040\040\040\040\040\040\
\040\040\040\040\040\040\040\040\040\
\040\040\040\040\040\040\040\040\040\
\040\040\040\040\040\040\040\040\040\
\040\040\040\040\040\040\040\040\040\
\040\040\040\040\040\040\040\040\040\
\040\040\040\040\040\040\040\040\040\
\040\040\040\040\040\040\040\040\040\
\040\040\040\040\040\040\040\040\040\
\040\040\040\040\040\040\040\040\040\
\040\040\040\040\040\040\040\040\040\
\040\040\040\040\040\040\040\040\040\
\040\040\040\040\040\040\040\040\040\
\040\040\040\040\040\040\040\040\040\
\040\040\040\040\040\040\040\040\040\
\040\040\040\040\040\040\040\040\040\
\040\040\040\040\040\040\040\040\040\
\040\040\040\040\040\040\040\040\040\
\040\040\040\040\040\040\040\040\040\
\040\040\040\040\040\040\040\040\040\
\040\040\040\040\040\040\040\040\040\
\040\040\040\040\040\040\040\040\040\
\040\040\040\040\040\040\040\040\040\
\040\040\040\040\040\040\040\040\040\
\040\040\040\040\040\040\040\040\040\
\040\040\040\040\040\040\040\040\040\
\040\040\040\040\040\040\040\040\040\
\040\040\040\040\040\040\040\040\040\
\040\040\040\040\040\040\040\040\040\
\040\040\040\040\040\040\040\040\040\
\040\040\040\040\040\040\040\040\040\
\040\040\040\040\040\040\040\040\040\
\040\040\040\040\040\040\040\040\040\
\040\040\040\040\040\040\040\040\040\
\040\040\040\040\040\040\040\040\040\
\040\040\040\040\040\040\040\040\040\
\040\040\040\040\040\040\040\040\040\
\040\040\040\040\040\040\040\040\040\
\040\040\040\040\040\040\040\040\040\
\040\040\040\040\040\040\040\040\040\
\040\040\040\040\040\040\040\040\040\
\040\040\040\040\040\040\040\040\040\
\040\040\040\040\040\040\040\040\040\
\040\040\040\040\040\040\040\040\040\
\040\040\040\040\040\040\040\040\040\
\040\040\040\040\040\040\040\040\040\
\040\040\040\040\040\040\040\040\040\
\040\040\040\040\040\040\040\040\040\
\040\040\040\040\040\040\040\040\040\
\040\040\040\040\040\040\040\040\040\
\040\040\040\040\040\040\040\040\040\
\040\040\040\040\040\040\040\040\040\
\040\040\040\040\040\040\040\040\040\
\040\040\040\040\040\040\040\040\040\
\040\040\040\040\040\040\040\040\040\
\040\040\040\040\040\040\040\040\040\
\040\040\040\040\040\040\040\040\040\
\040\040\040\040\040\040\040\040\040\
\040\040\040\040\040\040\040\040\040\
\040\040\040\040\040\040\040\040\040\
\040\040\040\040\040\040\040\040\040\
\040\040\040\040\040\040\040\040\040\
\040\040\040\040\040\040\040\040\040\
\040\040\040\040\040\040\040\040\040\
\040\040\040\040\040\040\040\040\040\
\040\040\040\040\040\040\040\040\040\
\040\040\040\040\040\040\040\040\040\
\040\040\040\040\040\040\040\040\040\
\040\040\040\040\040\040\040\040\040\
\040\040\040\040\040\040\040\040\040\
\040\040\040\040\040\040\040\040\040\
\040\040\040\040\040\040\040\040\040\
\040\040\040\040\040\040\040\040\040\
\040\040\040\040\040\040\040\040\040\
\040\040\040\040\040\040\040\040\040\
\040\040\040\040\040\040\040\040\040\
\040\040\040\040\040\040\040\040\040\
\040\040\040\040\040\040\040\040\040\
\040\040\040\040\040\040\040\040\040\
\040\040\040\040\040\040\040\040\040\
\040\040\040\040\040\040\040\040\040\
\040\040\040\040\040\040\040\040\040\
\040\040\040\040\040\040\040\040\040\
\040\040\040\040\040\040\040\040\040\
\040\040\040\040\040\040\040\040\040\
\040\040\040\040\040\040\040\040\040\
\040\040\040\040\040\040\040\040\040\
\040\040\040\040\040\040\040\040\040\
\040\040\040\040\040\040\040\040\040\
\040\040\040\040\040\040\040\040\040\
\040\040\040\040\040\040\040\040\040\
\040\040\040\040\040\040\040\040\040\
\040\040\040\040\040\040\040\040\040\
\040\040\040\040\040\040\040\040\040\
\040\040\040\040\040\040\040\040\040\
\040\040\040\040\040\040\040\040\040\
\040\040\040\040\040\040\040\040\012\

Phil Lawrence wrote:
CREATE TABLE test (
testcol VARCHAR2(4000));

Load into this table ... then:

SELECT MAX(LENGTH(testcol)) FROM test;

Two thoughts. The first is that your assumption about the size is
incorrect. The second is CHAR(1000)? You've got to be kidding.
--
Daniel A. Morgan
University of Washington
damorgan@x.washington.edu
(replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org

Phil Lawrence schrieb:
If you don't specify length of the input character data, sqlldr uses
default of 255 characters.
If you change the controlfile definition to
CUSTRE_REMARK_TEXT CHAR(1000) nullif CUSTRE_REMARK_TEXT
= '(null)'

you should be able to load the data without problems.
Also consider the Daniels remark about the use of CHAR datatype in your
table - i can't imagine a case when using VARCHAR2 instead would not be
beneficial.

Best regards

Maxim

I found the answer at:
http://www.orafaq.com/forum/t/50219/0/

The problem occurs because the default size for sqlldr is char(255).
Find the column(s) you suspect might be very large and specify in the
ctl file

COL1 CHAR(500) NULLIF COL1=BLANKS,
COL2 CHAR(500) NULLIF COL2=BLANKS,

where COL1 and COL2 are the columns that are wider than the default
255 xters.