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  |
 |
gopieee16
Messages: 28
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 #654097 is a reply to message #654095] |
Wed, 27 July 2016 01:44  |
 |
Littlefoot
Messages: 21823
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> |
|
|
|
|
|
|
|
Goto Forum:
Current Time: Mon Mar 03 17:31:57 CST 2025
|
] 
Blog
Search
Help
Members
Register
Login
Home
