sql*loader - inserted value too large for column

From: Arnie <arnie_at_musicstate.com>
Date: 21 Jan 2002 06:59:08 -0800
Message-ID: <be0bd06.0201210659.79bea648_at_posting.google.com>

[Quoted] I am trying to use sql*loader to insert data which may include carriage returns in one of the fields. Since I included a continueif, the correct number of records is being identified, but they are not inserted into the table with a 'inserted value too large for column' error. I tried increasing the size of each field in turn, but I still could not insert data. Can anyone point me in the right direction - it would be much appreciated.
Details:
Table :
CREATE TABLE Caption (

   ImgId VARCHAR2 (13),
   Unnamed_1 VARCHAR2 (60),

   Cap_2 VARCHAR2 (60),
   Cap_3 VARCHAR2 (60),
   Cap_4 VARCHAR2 (60),
   Cap_5 VARCHAR2 (60),
   Cap_6 VARCHAR2 (60))
   

Control file:
LOAD DATA
INFILE 'C:\WINDOWS\DESKTOP\CAPTEST.DAT'
CONTINUEIF NEXT(1) <> '"'
INTO TABLE CAPTION
(

IMGID		CHAR TERMINATED BY '|'OPTIONALLY ENCLOSED BY '"',
UNNAMED_1	CHAR TERMINATED BY '|'OPTIONALLY ENCLOSED BY '"',
CAP_2		CHAR TERMINATED BY '|'OPTIONALLY ENCLOSED BY '"',
CAP_3		CHAR TERMINATED BY '|'OPTIONALLY ENCLOSED BY '"',
CAP_4		CHAR TERMINATED BY '|'OPTIONALLY ENCLOSED BY '"',
CAP_5		CHAR TERMINATED BY '|'OPTIONALLY ENCLOSED BY '"',
CAP_6		CHAR TERMINATED BY '|'OPTIONALLY ENCLOSED BY '"'

)

Data file:
"AAX00001- "|"New Zealand - South Island

Lake Moana, Westland            "|"                                   
                        "|"                                           
                "|"                                                   
        "|"                                                           


"|"                                                            "


"AAX00002-    "|"New Zealand - North Island


Castle Point                    "|"                                   
                        "|"                                           
                "|"                                                   
        "|"                                                           


"|"                                                            "


"AAX00003-    "|"Agriculture - South Island NZ


Southland Farmland nr Clinton"|"                                      
                     "|"                                              
             "|"                                                      
     "|"                                                           


"|"                                                            "


"AAX00004-    "|"New Zealand - South Island


S of Geraldine S Canterbury     "|"                                   
                        "|"                                           
                "|"                                                   
        "|"                                                           


"|"                                                            "

Log file:

SQL*Loader: Release 8.1.7.0.0 - Production on Mon Jan 21 14:52:21 2002

(c) Copyright 2000 Oracle Corporation. All rights reserved.

Control File:   c:\windows\desktop\caption.ctl
Data File:      C:\WINDOWS\DESKTOP\CAPTEST.DAT
  Bad File:     c:\windows\desktop\CAPTEST.bad

  Discard File: none specified  

 (Allow all discards)

Number to load: ALL
Number to skip: 0
Errors allowed: 50
Bind array:     64 rows, maximum of 65536 bytes
Continuation:   1:1 != 0X22(character '"'), in next physical record
Path used:      Conventional

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

   Column Name                  Position   Len  Term Encl Datatype
------------------------------ ---------- ----- ---- ----
---------------------
IMGID                               FIRST     *   |  O(") CHARACTER
UNNAMED_1                            NEXT     *   |  O(") CHARACTER
CAP_2                                NEXT     *   |  O(") CHARACTER
CAP_3                                NEXT     *   |  O(") CHARACTER
CAP_4                                NEXT     *   |  O(") CHARACTER
CAP_5                                NEXT     *   |  O(") CHARACTER
CAP_6                                NEXT     *   |  O(") CHARACTER

Record 1: Rejected - Error on table CAPTION. ORA-01401: inserted value too large for column

Record 2: Rejected - Error on table CAPTION. ORA-01401: inserted value too large for column

Record 3: Rejected - Error on table CAPTION. ORA-01401: inserted value too large for column

Record 4: Rejected - Error on table CAPTION. ORA-01401: inserted value too large for column

Table CAPTION:
  0 Rows successfully loaded.
  4 Rows 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.

Space allocated for bind array:                  65016 bytes(36 rows)
Space allocated for memory besides bind array:        0 bytes

Total logical records skipped:          0
Total logical records read:             4
Total logical records rejected:         4
Total logical records discarded:        0

Run began on Mon Jan 21 14:52:21 2002
Run ended on Mon Jan 21 14:52:25 2002

Elapsed time was:     00:00:04.28
CPU time was:         00:00:00.00

Received on Mon Jan 21 2002 - 15:59:08 CET