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

"Daniel A. Morgan" <damorgan_at_exesolutions.com> wrote in message news:<3C4BD3EF.A6C97D3F_at_exesolutions.com>...
> 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

I agree with your preferred methodology - I am trying to dump the exact data and structure from a Pervasive SQL db using Oracle 8.1.7. There are 220,000 records. I have an extraction program that can produce insert into statements, but I wanted to try something faster and was told that SQL*Loader is the equivalent to SQL Server's BCP. I am new to Oracle and have not tried UTL_FILE yet - I will have a look. Received on Tue Jan 22 2002 - 11:33:12 CET