Home » RDBMS Server » Server Utilities » Field in data file exceeds maximum length - SQLLDR (windows7,11.2.0.2)
Field in data file exceeds maximum length - SQLLDR [message #654071] Tue, 26 July 2016 22:33 Go to next message
gopieee16
Messages: 26
Registered: May 2011
Location: Chennai
Junior Member

My control file looks like this

load data
infile tire_fitment_18.txt
into table tire_fitment_18
truncate
-- fields terminated by  X'09' optionally enclosed by '"'
fields terminated by  '|' optionally enclosed by  '"'
trailing nullcols
(
 CAR_TIRE_ID                                        integer external,
 BASE_VEHICLE_ID                                    integer external,
 VEHICLE_ID                                         integer external,
 YEAR_ID                                            integer external,
 MAKE_ID                                            integer external,
 MAKE_NAME                                          "trim(:MAKE_NAME)",
 TG_MAKE_NAME                                       "trim(:TG_MAKE_NAME)",
 MODEL_ID                                           integer external,
 MODEL_NAME                                         "trim(:MODEL_NAME)",
 TG_MODEL_NAME                                      "trim(:TG_MODEL_NAME)",
 SUBMODEL_ID                                        integer external,
 SUBMODEL_NAME                                      "trim(:SUBMODEL_NAME)",
 REGION_ID                                          integer external,
 OPTION1                                            "trim(:OPTION1)",
 MODEL_DESC                                         "trim(:MODEL_DESC)",
 CLEANED_OPTIONS                                    "trim(:CLEANED_OPTIONS)",
 TIRE_SIZE                                          "trim(:TIRE_SIZE)",
 )

My table is

Name                  Null Type          
--------------------- ---- ------------- 
CAR_TIRE_ID                NUMBER(20)    
BASE_VEHICLE_ID            NUMBER(10)    
VEHICLE_ID                 NUMBER(10)    
YEAR_ID                    NUMBER(10)    
MAKE_ID                    NUMBER(10)    
MAKE_NAME                  VARCHAR2(50)  
TG_MAKE_NAME               VARCHAR2(25)  
MODEL_ID                   NUMBER(10)    
MODEL_NAME                 VARCHAR2(100) 
TG_MODEL_NAME              VARCHAR2(40)  
SUBMODEL_ID                NUMBER(10)    
SUBMODEL_NAME              VARCHAR2(80)  
REGION_ID                  NUMBER(10)    
OPTION1                    VARCHAR2(70)  
MODEL_DESC                 VARCHAR2(40)  
CLEANED_OPTIONS            VARCHAR2(200) 
TIRE_SIZE                  VARCHAR2(20)

I get following errors, can you please help me in solving this.

Record 1: Rejected - Error on table TIRE_FITMENT_18, column CAR_TIRE_ID.
Field in data file exceeds maximum length
Record 3: Rejected - Error on table TIRE_FITMENT_18, column CAR_TIRE_ID.
Field in data file exceeds maximum length

Record 6646: Rejected - Error on table TIRE_FITMENT_18, column MODEL_ID.
ORA-01722: invalid number
Record 6648: Rejected - Error on table TIRE_FITMENT_18, column MODEL_ID.
ORA-01722: invalid number


Thanks,
GKL
Re: Field in data file exceeds maximum length - SQLLDR [message #654074 is a reply to message #654071] Wed, 27 July 2016 00:15 Go to previous messageGo to next message
Frank Naude
Messages: 4579
Registered: April 1998
Senior Member
Please post the first 3 records of the input file (tire_fitment_18.txt).
Re: Field in data file exceeds maximum length - SQLLDR [message #654075 is a reply to message #654071] Wed, 27 July 2016 00:16 Go to previous messageGo to next message
Littlefoot
Messages: 21806
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Make CAR_TIRE_ID column larger. Don't store values different from a NUMBER into MODEL_ID column.

For some more information, post sample data (lines 1 - 3, lines 6446 - 6448 from the FITMENT_18.TXT file.
Re: Field in data file exceeds maximum length - SQLLDR [message #654079 is a reply to message #654075] Wed, 27 July 2016 00:39 Go to previous messageGo to next message
gopieee16
Messages: 26
Registered: May 2011
Location: Chennai
Junior Member

Thanks, Littlefoot i already tried increasing the column length, but still same error is displaying.

I have attached the table data in attachment.
Re: Field in data file exceeds maximum length - SQLLDR [message #654095 is a reply to message #654079] Wed, 27 July 2016 01:31 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

What did you posted? Are these lines of tire_fitment_18.txt?
If not post lines 1, 3, 6646 and 6648 of this file.

Re: Field in data file exceeds maximum length - SQLLDR [message #654097 is a reply to message #654095] Wed, 27 July 2016 01:44 Go to previous messageGo to next message
Littlefoot
Messages: 21806
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
The problem is in the very first column which contains the delimiter (pipe sign) itself, so SQL*Loader assumes that "nothing" (that precedes that pipe sign) actually is the CAR_TIRE_ID, while all other columns are "shifted" left.

Therefore, if you modify the control file as follows:
- SKIP the first two lines
- use WHEN clause to skip "underlines"
- include one FILLER column at the beginning
- remove the last comma sign (I'm surprised of how it didn't raise an error on your system)
things are much better.

options (skip=2)
load data
infile tire_fitment_18.txt
into table tire_fitment_18
truncate
when (1) != '-'
fields terminated by  '|' optionally enclosed by  '"'
trailing nullcols
(
 the_first                                          filler,
 CAR_TIRE_ID                                        integer external,
 BASE_VEHICLE_ID                                    integer external,
 VEHICLE_ID                                         integer external,
 YEAR_ID                                            integer external,
 MAKE_ID                                            integer external,
 MAKE_NAME                                          "trim(:MAKE_NAME)",
 TG_MAKE_NAME                                       "trim(:TG_MAKE_NAME)",
 MODEL_ID                                           integer external,
 MODEL_NAME                                         "trim(:MODEL_NAME)",
 TG_MODEL_NAME                                      "trim(:TG_MODEL_NAME)",
 SUBMODEL_ID                                        integer external,
 SUBMODEL_NAME                                      "trim(:SUBMODEL_NAME)",
 REGION_ID                                          integer external,
 OPTION1                                            "trim(:OPTION1)",
 MODEL_DESC                                         "trim(:MODEL_DESC)",
 CLEANED_OPTIONS                                    "trim(:CLEANED_OPTIONS)",
 TIRE_SIZE                                          "trim(:TIRE_SIZE)"
 )
SQL> $sqlldr scott/tiger@ora11 control=test18.ctl log=test18.log

SQL*Loader: Release 11.2.0.2.0 - Production on Sri Srp 27 08:28:33 2016

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

Commit point reached - logical record count 10
Commit point reached - logical record count 11

SQL> select count(*) from tire_fitment_18;

  COUNT(*)
----------
         6

SQL>
Re: Field in data file exceeds maximum length - SQLLDR [message #654099 is a reply to message #654097] Wed, 27 July 2016 01:49 Go to previous messageGo to next message
gopieee16
Messages: 26
Registered: May 2011
Location: Chennai
Junior Member

Thanks Littlefoot for your suggestion i'll try this option and update you.

Michel i copied first 6 records from my data file. All the records having similar kind of data so only i copied only first 6 rows.
Re: Field in data file exceeds maximum length - SQLLDR [message #654101 is a reply to message #654099] Wed, 27 July 2016 01:54 Go to previous message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

OK, so the problem come from the line starting with '-' and the first pipe as Littlefoot pointed you.

Previous Topic: Sql Loader
Next Topic: csscaner and csalter
Goto Forum:
  


Current Time: Thu Mar 28 04:00:34 CDT 2024