OraFAQ Forum: Server Utilities

Home » RDBMS Server » Server Utilities » SQL LOADER (ORACLE 10.2.0.1.0 /WINDOWS)

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

SQL LOADER [message #349662]

Mon, 22 September 2008 10:04

mandys
Messages: 14
Registered: February 2008
Location: Pittsburgh

Junior Member

Hi,
I am getting this error Token longer than max allowable length of 258 characters where I have J_NAME.
I have a data file with the following format
"03/06/06,""202000000"",""2002"","" 000000.19"",""ptr TWP"",""00005"",""HER, POHN P"","""","""",""ENV MAT LLC"","""","""","""",""NONE"",""PA"",""999990000"",""PALOCA"","" "","""","""","""","""""
"03/06/06,""100000000"",""2002"","" 000010.72"",""ptr TWP"",""00706"",""DE, TAMMY L"","""","""",""SNP LLC"","""","""",""421 W CHEW ST""
This is my control file...
OPTIONS (SKIP=1)
LOAD DATA
INFILE 'c:\data\sample\sample.csv'
BADFILE 'c:\data\sample\s1.bad'
DISCARDFILE 'c:\data\sample\s1.is'
TRUNCATE
INTO TABLE test_data
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' TRAILING NULLCOLS
( CL_DATE "REPLACE (:CL_DATE,'"')" ,
SN ,
CL_YEAR ,
CL_AMOUNT ,
J_NAME "UPPER(TRIM(:J_NAME))" ,
J_NO "UPPER(TRIM(:J_NO))" ,
IND_NAME "UPPER(TRIM(REPLACE(:IND_NAME,',')))" ,
EMP_NO "UPPER(TRIM(:EMP_NO)" ,
EMP_NAME "UPPER(TRIM(:EMP_NAME))" ,
EMP_NAME1 "UPPER(TRIM(:EMP_NAME1))" ,
ADD_1 "UPPER(TRIM(:ADD_1))" ,
ADD_2 "UPPER(TRIM(:ADD_2))" ,
CITY "UPPER(TRIM(:CITY))" ,
STATE "UPPER(TRIM(:STATE))" ,
ZIP "UPPER(TRIM(:ZIP))" ,
LOCALITY_NAME "UPPER(TRIM(:LOCALITY_NAME))" ,
DETAIL "UPPER(TRIM(:DETAIL)" ,
RDATE DATE "MM/DD/YY" ,
REPONSE_AMOUNT
)
Any help will be highly appreciated

Thanks,
Mandy

Report message to a moderator

Re: SQL LOADER [message #349671 is a reply to message #349662]

Mon, 22 September 2008 10:41

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

Senior Member
Account Moderator

please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.

Regards
Michel

Report message to a moderator

Re: SQL LOADER [message #349714 is a reply to message #349662]

Mon, 22 September 2008 17:53

Barbara Boehmer
Messages: 9101
Registered: November 2002
Location: California, USA

Senior Member

What is the length of the column j_name in the table test_data? You can run into problems if the data your are loading exceeds either the size of the column in the table or the size in the control file. If you do not specify a size in the control file, then the default is 255. This size is prior to any trimming. You can specify a larger size in your control file, such as:

J_NAME CHAR(1000) "UPPER(TRIM(:J_NAME))"

Report message to a moderator

Re: SQL LOADER [message #349760 is a reply to message #349714]

Tue, 23 September 2008 01:01

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

Senior Member
Account Moderator

Quote:

This size is prior to any trimming.

Good to keep this in mind.

Regards
Michel

Report message to a moderator

Previous Topic:	Importing a full export - creating tablespaces first?
Next Topic:	Exports

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Tue Dec 24 11:02:38 CST 2024