OraFAQ Forum: Server Utilities » Field in data file exceeds maximum length error even when using Filler/BOUNDFILLER

Home » RDBMS Server » Server Utilities » Field in data file exceeds maximum length error even when using Filler/BOUNDFILLER (WINDOWs XP)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Field in data file exceeds maximum length error even when using Filler/BOUNDFILLER [message #571154]

Wed, 21 November 2012 12:17

vykalra
Messages: 10
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)."

Report message to a moderator

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

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

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/

Report message to a moderator

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

Michel Cadot
Messages: 68758
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

The reason is obvious.

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

Regards
Michel

Report message to a moderator

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

vykalra
Messages: 10
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.

Report message to a moderator