Home » RDBMS Server » Server Utilities » SQL LOADER (ORACLE 10.2.0.1.0 /WINDOWS)
SQL LOADER [message #349662] Mon, 22 September 2008 10:04 Go to next message
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
Re: SQL LOADER [message #349671 is a reply to message #349662] Mon, 22 September 2008 10:41 Go to previous messageGo to next message
Michel Cadot
Messages: 64102
Registered: March 2007
Location: Nanterre, France, http://...
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
Re: SQL LOADER [message #349714 is a reply to message #349662] Mon, 22 September 2008 17:53 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8621
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))"
Re: SQL LOADER [message #349760 is a reply to message #349714] Tue, 23 September 2008 01:01 Go to previous message
Michel Cadot
Messages: 64102
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
This size is prior to any trimming.

Good to keep this in mind.

Regards
Michel
Previous Topic: Importing a full export - creating tablespaces first?
Next Topic: Exports
Goto Forum:
  


Current Time: Fri Dec 02 20:49:16 CST 2016

Total time taken to generate the page: 0.16998 seconds