Home » RDBMS Server » Server Utilities » Token longer than max allowable length of 258 chars (oracle db ,10g , linux redhat 5)
Token longer than max allowable length of 258 chars [message #513023] Thu, 23 June 2011 05:40 Go to next message
aelmahadi
Messages: 1
Registered: June 2011
Location: KHRT
Junior Member
Hi all,

i tried to apply a sql case statement in sql loader control file in " " the load succeed with 258 chars case or decode statement but when i add more cases it return
sql loader 350 token longer than max

LOAD DATA
INFILE 'F:\Vou\vou20110613_102_951454.unl'
BADFILE 'F:\Vou\vou.pad'
DISCARDFILE 'F:\Vou\vou.dic'
replace
INTO TABLE vou_test_2
FIELDS TERMINATED BY '|'
TRAILING NULLCOLS
(SERIALNO,TIMESTAMP "to_date(:TIMESTAMP,'yy/mm/dd hh24:mi:ss')" ,BRANDID,SUBCOSID,CHARGINGPARTYNUMBER,PAYTYPE,VOUCHERPINNUMBER,VOUCHERBATCHNUMBER,VOUCHERSEQUENCE,VOUCHERCOSID,VOUCHERSPID,CARDFACEVA LUE,CARDVALUEADDED,CARDVALIDITYADDED,TRADETIME "to_date(:TRADETIME,'yy/mm/dd hh24:mi:ss')" ,OPERATEDBY,THIRDPARTYNUMBER,ACCESSMETHOD,TRADETYPE,RECHARGEAREANUMBER,TRANSITIONID,VOUCHERENCRYPTNUMBER,Location_Number,RESERVED2,RE SERVED3,PRODUCTID,SERVICETYPE,OLDUSERSATE,CURRENTUSERSATE,PREVIOUSACTIVESTOP "to_date(:PREVIOUSACTIVESTOP,'yy/mm/dd hh24:mi:ss')",NEWACTIVESTOP "to_date(:NEWACTIVESTOP,'yy/mm/dd hh24:mi:ss')",SUSPENDSTOP "to_date(:SUSPENDSTOP,'yy/mm/dd hh24:mi:ss')",DISABLESTOP "to_date(:DISABLESTOP,'yy/mm/dd hh24:mi:ss')" ,VALIDITYADDED,REWARDCYCLES,REBATEPRODUCTID,BILLCYCLEID,SUBSCRIBERID,RESERVED4,RESULTCODE,ACCOUNTID,RECHARGETAX,RECHARGEPENALTY,CURRE NCYCODE,RESERVED5,PREPAIDBALANCEBEFORE,RECHARGEFORPREPAID,PREPAIDBALANCE,POSTPAIDBALANCEBEFORE,RECHARGEFORPOSTPAID,POSTPAIDBALANCE,AC COUNTTYPE1,REWARDAMOUNT1,CURRENTACCTAMOUNT1,ACCOUNTTYPE2,REWARDAMOUNT2,CURRENTACCTAMOUNT2,ACCOUNTTYPE3,REWARDAMOUNT3,CURRENTACCTAMOUN T3,ACCOUNTTYPE4,REWARDAMOUNT4,
CURRENTACCTAMOUNT4,ACCOUNTTYPE5,REWARDAMOUNT5,CURRENTACCTAMOUNT5,ACCOUNTTYPE6,REWARDAMOUNT6,CURRENTACCTAMOUNT6,ACCOUNTTYPE7,REWARDAMO UNT7
,CURRENTACCTAMOUNT7,ACCOUNTTYPE8,REWARDAMOUNT8,CURRENTACCTAMOUNT8,ACCOUNTTYPE9,REWARDAMOUNT9,CURRENTACCTAMOUNT9,ACCOUNTTYPE10,REWARDA MOUNT10,
CURRENTACCTAMOUNT10,BONUSVALIDITY1,BONUSVALIDITY2,BONUSVALIDITY3,BONUSVALIDITY4,BONUSVALIDITY5,BONUSVALIDITY6,BONUSVALIDITY7,BONUSVAL IDITY8,
BONUSVALIDITY9,BONUSVALIDITY10,PREVIOUSSUSPENDSTOP "to_date(:PREVIOUSSUSPENDSTOP,'yy/mm/dd hh24:mi:ss')",
PREVIOUSDISABLESTOP "to_date(:PREVIOUSDISABLESTOP,'yy/mm/dd hh24:mi:ss')",AGENTNAME,ADDTIONALINFO,FILE_TIME,FILE_NAME,
ODATE "SYSDATE",ACCOUNT_2001 "CASE WHEN:ACCOUNTTYPE1='2001'THEN :REWARDAMOUNT1 WHEN :ACCOUNTTYPE2='2001' THEN :REWARDAMOUNT2
WHEN :ACCOUNTTYPE3='2001' THEN :REWARDAMOUNT3
WHEN :ACCOUNTTYPE4='2001' THEN :REWARDAMOUNT4
WHEN :ACCOUNTTYPE5='2001' THEN :REWARDAMOUNT5 END")

the above one succeed when i edit the case statement as follow

ACCOUNT_2001 "CASE WHEN:ACCOUNTTYPE1='2001'THEN :REWARDAMOUNT1 WHEN :ACCOUNTTYPE2='2001' THEN :REWARDAMOUNT2
WHEN :ACCOUNTTYPE3='2001' THEN :REWARDAMOUNT3
WHEN :ACCOUNTTYPE4='2001' THEN :REWARDAMOUNT4
WHEN :ACCOUNTTYPE5='2001' THEN :REWARDAMOUNT5
WHEN :ACCOUNTTYPE6='2001' THEN :REWARDAMOUNT6
WHEN :ACCOUNTTYPE7='2001' THEN :REWARDAMOUNT7
WHEN :ACCOUNTTYPE8='2001' THEN :REWARDAMOUNT8
WHEN :ACCOUNTTYPE9='2001' THEN :REWARDAMOUNT9
WHEN :ACCOUNTTYPE10='2001' THEN :REWARDAMOUNT10 END"

i got the Error sql loader 350 token longer than max allowable length of 258 chars .

note : i cant modify the table structure to shorten the column names .

please help me ....
thanlks

Report message to a moderator

Re: Token longer than max allowable length of 258 chars [message #513027 is a reply to message #513023] Thu, 23 June 2011 06:07 Go to previous messageGo to next message
cookiemonster
Messages: 13920
Registered: September 2008
Location: Rainy Manchester
Senior Member
I suggest you use an external table instead - that limition shouldn't apply to them.

Report message to a moderator

Re: Token longer than max allowable length of 258 chars [message #513095 is a reply to message #513027] Thu, 23 June 2011 11:29 Go to previous message
Barbara Boehmer
Messages: 9088
Registered: November 2002
Location: California, USA
Senior Member
There are various options.

You could try using decode instead of case to shorten the code.

You could put the case statement in a function, then use the function as your expression, passing all of the possible parameters to it.

You could load the data into a staging table using either SQL*Loader or an external table (which does not allow expressions in the table creation), then use SQL to insert from the staging table to the target destination table without such a limit on the length of the case statement.

Report message to a moderator

Previous Topic: importing 9i dump into 10g
Next Topic: Using Sql Loader issue
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Fri Apr 19 18:46:44 CDT 2024
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.