Re: sqlldr problem

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: 14 Oct 2001 07:32:56 -0700
Message-ID: <9qc7mo01jvi@drn.newsguy.com>

In article <5T4y7.19539$U97.104834_at_NewsReader>, "Neil says...
>
>Could anyone help me please? I'm trying to load data originally obtained
>from an Informix database from an ASCII table.
>
>One of the fields has a blank in it. Every row is rejected with the
>message: "Field in data file exceeds maximum length"
>
>I'm baffled!
>
>thanks
>Neil
>
>---------------------- .ctl file -----------------------------------
>load data
>infile '/scratch/crap.uld'
>badfile '/home/oracle/dsinvlines.bad'
>insert into table dsinvlines
>fields terminated by '|'
>(
> company smallint ,
> vendor_num char(9) ,
> batch_num integer ,
> invoice char(22) ,
> seq_nbr smallint ,
> product_num char(30) ,
> line_desc char(40) ,
> unit_order decimal(12,3) ,
> quantity_inv decimal(12,3) ,
> unit_meas char(6) ,
> list_price decimal(14,4) ,
>...

since all of your input data is character strings -- you should be using the char type for all columns. I just loaded your data successfully using:

load data
infile *
replace
into table dsinvlines
fields terminated by '|'
(
company ,
vendor_num ,
batch_num ,
invoice ,
seq_nbr ,
product_num ,
line_desc ,
unit_order ,
quantity_inv ,
unit_meas ,
list_price
)
begindata
1|KE0122|85457|LN2008381Y|9|23419|MATT PREM BAKED HAM

100G|1.0|2.0||0.0|1.215|0.0|2.43|Z|0.0|0.0|0.0|0.
0|0.0|0.0|0.0| | | |1|KE0122|85457|LN2008381Y|10|23417|MATT PREM SMOKED HAM
100G|1.0|2.0||0.0|1.215|0.0

|2.43|Z|0.0|0.0|0.0|0.0|0.0|0.0|0.0| | | | 1|KE0122|85457|LN2008381Y|11|23415|MATT PREM RST TURKEY 100G|1.0|4.0||0.0|1.215|0.0|4.86|Z|0.0|0.0|0.0| 0.0|0.0|0.0|0.0| | | |
1|KE0122|85457|LN2008381Y|12|22893|MATT HONEYROAST HAM 100G|1.0|2.0||0.0|0.9|0.0|1.8|Z|0.0|0.0|0.0|0.0| 0.0|0.0|0.0| | | |
1|KE0122|85457|LN2008381Y|13|22897|M PORK LUNCHEON MEAT 100G|1.0|2.0||0.0|0.57|
>--------------------- load.log ------------------------------------
>Table DSINVLINES, loaded from every logical record.
>Insert option in effect for this table: INSERT
>
> Column Name Position Len Term Encl Datatype
>------------------------------ ---------- ----- ---- ---- ------------------
>---
>COMPANY FIRST 2 SMALL INTEGER
>VENDOR_NUM NEXT 9 | CHARACTER
>BATCH_NUM NEXT 4 INTEGER
>INVOICE NEXT 22 | CHARACTER
>SEQ_NBR NEXT 2 SMALL INTEGER
>PRODUCT_NUM NEXT 30 | CHARACTER
>LINE_DESC NEXT 40 | CHARACTER
>UNIT_ORDER NEXT 7 PACKED DECIMAL
>(12, 3)
>QUANTITY_INV NEXT 7 PACKED DECIMAL
>(12, 3)
>UNIT_MEAS NEXT 6 | CHARACTER
>LIST_PRICE NEXT 8 PACKED DECIMAL
>(14, 4)
>...
>Record 1: Rejected - Error on table DSINVLINES, column UNIT_MEAS.
>Field in data file exceeds maximum length
>Record 2: Rejected - Error on table DSINVLINES, column UNIT_MEAS.
>Field in data file exceeds maximum length
>Record 3: Rejected - Error on table DSINVLINES, column UNIT_MEAS.
>Field in data file exceeds maximum length
>...
>---------------------- ASCII file-----------------------------------
>1|KE0122|85457|LN2008381Y|9|23419|MATT PREM BAKED HAM 100G|1.0|2.0|
>|0.0|1.215|0
>.0|2.43|Z|0.0|0.0|0.0|0.0|0.0|0.0|0.0| | | |
>1|KE0122|85457|LN2008381Y|10|23417|MATT PREM SMOKED HAM 100G|1.0|2.0|
>|0.0|1.215
>|0.0|2.43|Z|0.0|0.0|0.0|0.0|0.0|0.0|0.0| | | |
>1|KE0122|85457|LN2008381Y|11|23415|MATT PREM RST TURKEY 100G|1.0|4.0|
>|0.0|1.215
>|0.0|4.86|Z|0.0|0.0|0.0|0.0|0.0|0.0|0.0| | | |
>1|KE0122|85457|LN2008381Y|12|22893|MATT HONEYROAST HAM 100G|1.0|2.0|
>|0.0|0.9|0.
>0|1.8|Z|0.0|0.0|0.0|0.0|0.0|0.0|0.0| | | |
>1|KE0122|85457|LN2008381Y|13|22897|M PORK LUNCHEON MEAT 100G|1.0|2.0|
>|0.0|0.57|
>
>

--
Thomas Kyte (tkyte@us.oracle.com)             http://asktom.oracle.com/ 
Expert one on one Oracle, programming techniques and solutions for Oracle.
http://www.amazon.com/exec/obidos/ASIN/1861004826/  
Opinions are mine and do not necessarily reflect those of Oracle Corp 

Received on Sun Oct 14 2001 - 09:32:56 CDT