sqlldr: Field in data file exceeds maximum length?

From: Phil Lawrence <prlawrence_at_gmail.com>
Date: 22 May 2007 09:38:32 -0700
Message-ID: <1179851912.477171.89230_at_g4g2000hsf.googlegroups.com>

[Quoted] [Quoted] 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> _at_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}_at_$


{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 '_at_fields = split /\^\|\^/; print


$fields[6];' | wc -c
1001



$ cat ex_cust_remarks.foo | perl -ne 'chomp; _at_fields = split /\^\|\^/;


print $fields[6];' | wc -c
1000



$ cat ex_cust_remarks.foo-1 | perl -ne '_at_fields = split /\^\|\^/;


print $fields[6];' | wc -c
1000



$ cat ex_cust_remarks.foo-1 | perl -ne 'chomp; _at_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\

Received on Tue May 22 2007 - 18:38:32 CEST

This message: [ Message body ]
Next message: DA Morgan: "Re: sqlldr: Field in data file exceeds maximum length?"
Previous message: DA Morgan: "Re: HELP NEEDED ( Badly )"
Next in thread: DA Morgan: "Re: sqlldr: Field in data file exceeds maximum length?"
Reply: DA Morgan: "Re: sqlldr: Field in data file exceeds maximum length?"
Reply: Maxim Demenko: "Re: sqlldr: Field in data file exceeds maximum length?"

Contemporary messages sorted: [ by date ] [ by thread ] [ by subject ] [ by author ]

Original text of this message