Home » RDBMS Server » Server Utilities » Field in data file exceeds maximum length (Oracle10g)
| Field in data file exceeds maximum length [message #561044] |
Wed, 18 July 2012 08:12  |
 |
naveendara
Messages: 11
Registered: June 2012
Location: Hyderabad
|
Junior Member |
|
|
I have data in ngf_test.dat file like
NGFID;RECTYPE;RECNAME
57717832;19;MDU
PARENT
CHILD
inputs;PCODE: 340-RES L7N 3H1|101_109|111_112|114_122|201_212|214_222|301_312|314_322|401_412|414_422|501_512|514_516|518_522|601_612|614_616|618_622|701_712|7 14_716|718_722|801_812|814_816|818_822|901_912|914_916|918_922|1001_1012|1014_1016|1018_1022|1801_1810|1812|1814|1901_1910|1912|1914| 2001_2010|2012|2014|;
owner;;
location;1270 MAPLE CROSSING BLVD;
type;RESIDENTIAL;
accumulated_length;0;
diagram_dwg_number;BR1270MC;
no_units;233;
work_order;;
wireless;;
voip_ready;;
152519037;19;MDU
PARENT
CHILD
owner;;
location;1484 PILGRIMS WAY;73026986
type;RESIDENTIAL;
accumulated_length;0;
diagram_dwg_number;;
no_units;;
work_order;;
wireless;;
voip_ready;;
I need to insert these two records into below tables(NGF_REC_LINK,MDU_19).
I got below mentioned result while trying to execute my ctl file (ngf_test.ctl Please find the Attachment )
For 1st record : I am getting beloe error
Record 1: Rejected - Error on table NGF_REC_LINK, column TABLENAME.
Field in data file exceeds maximum length
For 2nd record :
Because inputs filed is missing in file,Data is miss arranged into table like
NGFID INPUTS OWNER LOCATION TYPE ACCUMULATED_LENGTH DIAGRAM_DWG_NUMBER NO_UNITS WORK_ORDER WIRELESS VOIP_READY
152519037 owner location;1484 PILGRIMS WAY;73026986 ype;RESIDENTIAL accumulated_length;0 iagram_dwg_number o_units work_order ireless voip_ready
CREATE TABLE NGF_REC_LINK
(
NGFID NUMBER(20),
GRFID NUMBER(20),
TABLENAME VARCHAR2(50),
PARENT VARCHAR2(1000),
CHILD VARCHAR2(3000),
PROVINCE VARCHAR2(3)
);
CREATE TABLE MDU_19
(
NGFID NUMBER(20),
GRFID NUMBER(20),
INPUTS VARCHAR2(500),
OWNER VARCHAR2(200),
LOCATION VARCHAR2(50),
TYPE VARCHAR2(250),
ACCUMULATED_LENGTH VARCHAR2(50),
DIAGRAM_DWG_NUMBER VARCHAR2(50),
NO_UNITS VARCHAR2(50),
WORK_ORDER VARCHAR2(50),
WIRELESS VARCHAR2(50),
VOIP_READY VARCHAR2(50)
);
Could you please provide me with some assistance with this SQL Loader problem
Thanks in advance..
[mod-edit: code tags added by bb; next time please add them yourself]
-
Attachment: ngf_test.ctl
(Size: 1.22KB, Downloaded 2496 times)
[Updated on: Wed, 18 July 2012 10:49] by Moderator Report message to a moderator |
|
|
|
|
|
| Re: Field in data file exceeds maximum length [message #561069 is a reply to message #561044] |
Wed, 18 July 2012 11:40  |
 |
Barbara Boehmer
Messages: 9104
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
naveendara wrote on Wed, 18 July 2012 06:12
... For 1st record : I am getting beloe error
Record 1: Rejected - Error on table NGF_REC_LINK, column TABLENAME.
Field in data file exceeds maximum length ...
You have conflicting or duplicate terminated by ... clauses. You can fix it by removing the first one and supplying one for each individual field or you can increase the char(100) to, for example char(5000). I would tend to use the first method.
naveendara wrote on Wed, 18 July 2012 06:12
... For 2nd record :
Because inputs filed is missing in file,Data is miss arranged into table like ...
Garbage in results in garbage out. If a field is missing there should at least be a separate delimiter for that missing field. If this is a common problem in your data, then you may need to resort to loading each record into one column in a staging table, using either SQL*Loader or an external table, then using SQL to parse out the data and insert into your target tables.
|
|
|
|
|
|
| Re: Field in data file exceeds maximum length [message #561241 is a reply to message #561240] |
Thu, 19 July 2012 13:21 |
|
Barbara Boehmer
Messages: 9104
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
Perhaps you did not do everything exactly as I suggested. It would help if you posted what you tried. Here is the control file that I used in testing, that loaded both records.
options(skip=1)
load data
append
continueif next preserve (1:1) = x'09'
into table ngf_rec_link
TRAILING NULLCOLS
(ngfid position (1) terminated by ';',
filler1 boundfiller terminated by ';',
filler2 boundfiller TERMINATED BY X'9',
parent TERMINATED BY X'9' "ltrim (:parent, 'PARENT;')",
child TERMINATED BY X'9' "ltrim (:child, 'CHILD;')",
tablename char(100) ":filler2 || '_' || :filler1")
into table MDU_19
when (filler1 = '19') and (filler2 ='MDU')
FIELDS TERMINATED BY X'9'
TRAILING NULLCOLS
(ngfid position (1) terminated by ';',
filler1 boundfiller terminated by ';',
filler2 boundfiller ,
filler3 filler ,
filler4 filler ,
inputs char(500) "rtrim (ltrim (:inputs, 'inputs;'), ';')",
owner "rtrim (ltrim (:owner, 'owner;'), ';')",
location "rtrim (ltrim (:location, 'location;'), ';')",
type "rtrim (ltrim (:type, 'type;'), ';')",
accumulated_length "rtrim (ltrim (:accumulated_length, 'accumulated_length;'), ';')",
diagram_dwg_number "rtrim (ltrim (:diagram_dwg_number, 'diagram_dwg_number;'), ';')",
no_units "rtrim (ltrim (:no_units, 'no_units;'), ';')",
work_order "rtrim (ltrim (:work_order, 'work_order;'), ';')",
wireless "rtrim (ltrim (:wireless, 'wireless;'), ';')",
voip_ready "rtrim (ltrim (:voip_ready, 'voip_ready;'), ';')")
|
|
|
|
|
|
Goto Forum:
Current Time: Thu Jan 30 20:21:54 CST 2025
|
] 
Blog
Search
Help
Members
Register
Login
Home
