Re: sql*loader - inserted value too large for column

[Quoted] How many records? How time-sensitive it the load? What version of Oracle? But have you considered UTL_FILE?:

With SQL*Loader my preference is always to blow things into a holding table as fast as possible and then use a proc to break it up, validate it, and move it to application tables. SQL*Loader can be a very blunt instrument for what often requires surgical skills.

Daniel Morgan

Arnie wrote:

> 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 - 09:40:15 CET