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 Go to next message
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 603 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 #561050 is a reply to message #561044] Wed, 18 July 2012 08:56 Go to previous messageGo to next message
joy_division
Messages: 4949
Registered: February 2005
Location: East Coast USA
Senior Member
My guess is the line starting with 3H1 is too long.
Re: Field in data file exceeds maximum length [message #561069 is a reply to message #561044] Wed, 18 July 2012 11:40 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8922
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 #561240 is a reply to message #561069] Thu, 19 July 2012 13:05 Go to previous messageGo to next message
naveendara
Messages: 11
Registered: June 2012
Location: Hyderabad
Junior Member
Thanks barbara,

I have tried with your suggestion,but still I am getting same error for 1st Record.


Quote:
... 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 ...

Can you please help me to solve this problem.
Re: Field in data file exceeds maximum length [message #561241 is a reply to message #561240] Thu, 19 July 2012 13:21 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8922
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;'), ';')") 

Re: Field in data file exceeds maximum length [message #561301 is a reply to message #561241] Fri, 20 July 2012 06:19 Go to previous message
naveendara
Messages: 11
Registered: June 2012
Location: Hyderabad
Junior Member
Thank's for your help Barbara it's working fine.

Previous Topic: import the table
Next Topic: Migrating using Transportable tablespace
Goto Forum:
  


Current Time: Thu Sep 24 03:58:02 CDT 2020