Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: sqlldr problem

Re: sqlldr problem

From: Manuela Mueller <520040906697-0001_at_t-online.de>
Date: Sun, 14 Oct 2001 12:18:10 +0200
Message-ID: <3BC96662.39F620BA@t-online.de>

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|

> |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|

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 100G
1.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        

)
4. perform the load. Snippet of log file: Table DSINVLINES, loaded from every logical record. Insert option in effect for this table: INSERT  
   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

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US