Home » RDBMS Server » Server Utilities » Field in data file exceeds maximum length error even when using Filler/BOUNDFILLER (WINDOWs XP)
Field in data file exceeds maximum length error even when using Filler/BOUNDFILLER [message #571154] Wed, 21 November 2012 12:17 Go to next message
vykalra
Messages: 3
Registered: November 2012
Junior Member
hi,

i have a .csv file where the length of a field ( say CNUMB) to be be loaded is 240 characters(lenght in database is varchar2(240)). in the input file for some of the reords the value for CNUMB exceeds 240 characters ( see example below where - is space). Hence for now i tried using FILLER/BOUNDFILLER with the CNUMB filed still i get the following error.

Record 9170: Rejected - Error on table DATARAW_FS, column CNUMB
Field in data file exceeds maximum length

What could be thereason ?

Eg:

Distributes up to 3 video signals (camera, DVD, SATV, CATV) to any Television in the home (UHF &
--------------------------------------------------------------------------------------------------------------------
VHF bands)."
Re: Field in data file exceeds maximum length error even when using Filler/BOUNDFILLER [message #571155 is a reply to message #571154] Wed, 21 November 2012 12:24 Go to previous messageGo to next message
BlackSwan
Messages: 22508
Registered: January 2009
Senior Member
>What could be thereason ?
the data exceeds 240 charaters
Either make the column longer/bigger or make the data shorter

Please read and follow the forum guidelines, to enable us to help you:

http://www.orafaq.com/forum/t/88153/0/
Re: Field in data file exceeds maximum length error even when using Filler/BOUNDFILLER [message #571156 is a reply to message #571154] Wed, 21 November 2012 12:52 Go to previous messageGo to next message
Michel Cadot
Messages: 58590
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
The reason is obvious.

Post your table definition and control file and an example of data.

Regards
Michel
Re: Field in data file exceeds maximum length error even when using Filler/BOUNDFILLER [message #571182 is a reply to message #571156] Thu, 22 November 2012 01:27 Go to previous messageGo to next message
vykalra
Messages: 3
Registered: November 2012
Junior Member
Control File is

load data
infile "C:\pimsdb\FSDBInitial.csv"
REPLACE into table PIMSDATARAW_FS
fields terminated by ","
optionally enclosed by '"'
TRAILING NULLCOLS
(
PROJECT_STATUS,
catalog_notes BOUNDFILLER, -- temp changes to load data
PKG_HEIGHT NULLIF PKG_HEIGHT=BLANKS
); // i have removed the extra fields in the control file

Table is

create table PIMSDATARAW_FS
(
project_status VARCHAR2(5),
catalog_notes VARCHAR2(240),
pkg_height NUMBER(7,3)
);

Error i get is



Field in data file exceeds maximum length
Record 9166: Rejected - Error on table PIMSDATARAW_FS, column CATALOG_NOTES.
Field in data file exceeds maximum length
Record 9167: Rejected - Error on table PIMSDATARAW_FS, column CATALOG_NOTES.
Field in data file exceeds maximum length
Record 9168: Rejected - Error on table PIMSDATARAW_FS, column CATALOG_NOTES.
Field in data file exceeds maximum length
Record 9169: Rejected - Error on table PIMSDATARAW_FS, column CATALOG_NOTES.
Field in data file exceeds maximum length
Record 9170: Rejected - Error on table PIMSDATARAW_FS, column CATALOG_NOTES.
Field in data file exceeds maximum length
Record 9171: Rejected - Error on table PIMSDATARAW_FS, column CATALOG_NOTES.
Field in data file exceeds maximum length


As i said earlier i only get this error when i have huge spaces in the CATALOG_NOTES field. as i am dealing with thousands of records for now i want to ignore this field and continue to load the data ( i am not sure why sqlloader picks the catalog_notes field when i have mentioned it to be boundfillder.
Re: Field in data file exceeds maximum length error even when using Filler/BOUNDFILLER [message #571183 is a reply to message #571182] Thu, 22 November 2012 01:36 Go to previous messageGo to next message
Littlefoot
Messages: 19317
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
How about loading SUBSTR(column, 1, 240)? That way you'd still insert *something*.
Re: Field in data file exceeds maximum length error even when using Filler/BOUNDFILLER [message #571186 is a reply to message #571182] Thu, 22 November 2012 01:39 Go to previous messageGo to next message
Michel Cadot
Messages: 58590
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
catalog_notes BOUNDFILLER CHAR(1000),


Regards
Michel
Re: Field in data file exceeds maximum length error even when using Filler/BOUNDFILLER [message #571195 is a reply to message #571186] Thu, 22 November 2012 02:36 Go to previous message
vykalra
Messages: 3
Registered: November 2012
Junior Member
Thanks Michel, your suggestion worked!! Smile
Previous Topic: Restore Dump File Error
Next Topic: Not a valid export file , header failed verification
Goto Forum:
  


Current Time: Sat Jul 26 14:29:41 CDT 2014

Total time taken to generate the page: 0.05851 seconds