| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: sqlldr problem
Neil Truby wrote:
>
> 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) ,
> ...
> --------------------- 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|
Hello Neil,
you need to 'massage' your data before you load them.
I replayed your situation with Oracle 8.1.7.
1. create the table:
create table dsinvlines(
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)
);
2. prepare the flat file. Note: all char fields are enclosed by ""
1|"KE0122"|85457|"LN2008381Y"|9|"23419"|"MATT PREM BAKED HAM 100G"|1.0|2.0|""|0.0 1|"KE0122"|85457|"LN2008381Y"|12|"22893"|"MATT HONEYROAST HAM 100G"|1.0|2.0|""|0.0 1|"KE0122"|85457|"LN2008381Y"|13|"22897"|" PORK LUNCHEON MEAT 100G1.0"|.0||"0.0"|0.57
3. prepare the control file:
load data
infile '/../crap.uld'
badfile '/../dsinvlines.bad'
into table dsinvlines
fields terminated by '|'
optionally enclosed by '"'
(
company INTEGER EXTERNAL,
vendor_num char(9) ,
batch_num INTEGER EXTERNAL ,
invoice char(22) ,
seq_nbr INTEGER EXTERNAL ,
product_num char(30) ,
line_desc char(40) ,
unit_order DECIMAL EXTERNAL ,
quantity_inv DECIMAL EXTERNAL,
unit_meas char(6) ,
list_price DECIMAL EXTERNAL
Column Name Position Len Term Encl Datatype
------------------------------ ---------- ----- ---- ----
---------------------
COMPANY FIRST * | O(") CHARACTER
VENDOR_NUM NEXT 9 | O(") CHARACTER
BATCH_NUM NEXT * | O(") CHARACTER
INVOICE NEXT 22 | O(") CHARACTER
SEQ_NBR NEXT * | O(") CHARACTER
PRODUCT_NUM NEXT 30 | O(") CHARACTER
LINE_DESC NEXT 40 | O(") CHARACTER
UNIT_ORDER NEXT * | O(") CHARACTER
QUANTITY_INV NEXT * | O(") CHARACTER
UNIT_MEAS NEXT 6 | O(") CHARACTER
LIST_PRICE NEXT * | O(") CHARACTER
Table DSINVLINES:
3 Rows successfully loaded.
0 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: 64974 bytes(39 rows) Space allocated for memory besides bind array: 0 bytes Total logical records skipped: 0 Total logical records read: 3 Total logical records rejected: 0 Total logical records discarded: 0
for more explanations look at:
http://otn.oracle.com/docs/products/oracle8i/doc_library/817_doc/server.817/a76955/part2.htm#435958
(create yourself a free account at otn if you don't already have one).
...Have a nice day
Manuela Mueller
Received on Sun Oct 14 2001 - 05:18:10 CDT
 |
 |