The Issue
When running in sqlplus, the output is a long concatenated String.
In case row would overflow over 4000 characters, an error would come:
ORA-01489: result of string concatenation is too long
The solution
Convert first member of the concatenation to CLOB, and concatenate the rest.
For example:
This is the SQL:
SELECT key1||','||imsi||','||msisdn||','||voice_mo1||','||voice_mt1||','||
sms_mo1||','||data1||','||voice_mo2||','||voice_mt2||','||sms_mo2||','||
data2||','||voice_mo3||','||voice_mt3||','||sms_mo3||','||data3||','||
voice_mo4||','||voice_mt4||','||sms_mo4||','||data4||','||voice_mo5||','||
voice_mt5||','||sms_mo5||','||data5||','||voice_mo6||','||voice_mt6||','||
sms_mo6||','||data6||','||voice_mo7||','||voice_mt7||','||sms_mo7||','||
data7||','||voice_mo8||','||voice_mt8||','||sms_mo8||','||data8||','||
voice_mo9||','||voice_mt9||','||sms_mo9||','||data9||','||voice_mo10||','||
voice_mt10||','||sms_mo10||','||data10||','||voice_mo11||','||voice_mt11||','||
sms_mo11||','||data11||','||voice_mo12||','||voice_mt12||','||sms_mo12||','||
data12||','||voice_mo13||','||voice_mt13||','||sms_mo13||','||data13||','||
voice_mo14||','||voice_mt14||','||sms_mo14||','||data14||','||
voice_mo15||','||voice_mt15||','||sms_mo15||','||data15||','||
voice_mo16||','||voice_mt16||','||sms_mo16||','||data16||','||
voice_mo17||','||voice_mt17||','||sms_mo17||','||data17||','||
voice_mo18||','||voice_mt18||','||sms_mo18||','||data18||','||
voice_mo19||','||voice_mt19||','||sms_mo19||','||data19||','||
voice_mo20||','||voice_mt20||','||sms_mo20||','||data20||','||
voice_mo21||','||voice_mt21||','||sms_mo21||','||data21||','||
voice_mo22||','||voice_mt22||','||sms_mo22||','||data22||','||
voice_mo23||','||voice_mt23||','||sms_mo23||','||data23||','||
voice_mo24||','||voice_mt24||','||sms_mo24||','||data24||','||
voice_mo25||','||voice_mt25||','||sms_mo25||','||data25||','||
voice_mo26||','||voice_mt26||','||sms_mo26||','||data26||','||
voice_mo27||','||voice_mt27||','||sms_mo27||','||data27||','||
voice_mo28||','||voice_mt28||','||sms_mo28||','||data28||','||
voice_mo29||','||voice_mt29||','||sms_mo29||','||data29||','||
voice_mo30||','||voice_mt30||','||sms_mo30||','||data30||','||
voice_mo31||','||voice_mt31||','||sms_mo31||','||data31||','||
TO_CHAR(ts_last_modified,'YYYYMMDD hh24:mi:ss')||','||affiliate_id
FROM SFI_CUSTOMER_USAGE_HOURLY;
This is giving ORA-01489: result of string concatenation is too long
Converting sql to SELECT CLOB() does not solve error
SELECT TO_CLOB(key1|| ','||imsi||','||msisdn||','||voice_mo1||','||voice_mt1||','||
sms_mo1||','||data1||','||voice_mo2||','||voice_mt2||','||sms_mo2||','||
data2||','||voice_mo3||','||voice_mt3||','||sms_mo3||','||data3||','||
voice_mo4||','||voice_mt4||','||sms_mo4||','||data4||','||voice_mo5||','||
voice_mt5||','||sms_mo5||','||data5||','||voice_mo6||','||voice_mt6||','||
sms_mo6||','||data6||','||voice_mo7||','||voice_mt7||','||sms_mo7||','||
data7||','||voice_mo8||','||voice_mt8||','||sms_mo8||','||data8||','||
voice_mo9||','||voice_mt9||','||sms_mo9||','||data9||','||voice_mo10||','||
voice_mt10||','||sms_mo10||','||data10||','||voice_mo11||','||voice_mt11||','||
sms_mo11||','||data11||','||voice_mo12||','||voice_mt12||','||sms_mo12||','||
data12||','||voice_mo13||','||voice_mt13||','||sms_mo13||','||data13||','||
voice_mo14||','||voice_mt14||','||sms_mo14||','||data14||','||
voice_mo15||','||voice_mt15||','||sms_mo15||','||data15||','||
voice_mo16||','||voice_mt16||','||sms_mo16||','||data16||','||
voice_mo17||','||voice_mt17||','||sms_mo17||','||data17||','||
voice_mo18||','||voice_mt18||','||sms_mo18||','||data18||','||
voice_mo19||','||voice_mt19||','||sms_mo19||','||data19||','||
voice_mo20||','||voice_mt20||','||sms_mo20||','||data20||','||
voice_mo21||','||voice_mt21||','||sms_mo21||','||data21||','||
voice_mo22||','||voice_mt22||','||sms_mo22||','||data22||','||
voice_mo23||','||voice_mt23||','||sms_mo23||','||data23||','||
voice_mo24||','||voice_mt24||','||sms_mo24||','||data24||','||
voice_mo25||','||voice_mt25||','||sms_mo25||','||data25||','||
voice_mo26||','||voice_mt26||','||sms_mo26||','||data26||','||
voice_mo27||','||voice_mt27||','||sms_mo27||','||data27||','||
voice_mo28||','||voice_mt28||','||sms_mo28||','||data28||','||
voice_mo29||','||voice_mt29||','||sms_mo29||','||data29||','||
voice_mo30||','||voice_mt30||','||sms_mo30||','||data30||','||
voice_mo31||','||voice_mt31||','||sms_mo31||','||data31||','||
TO_CHAR(ts_last_modified,'YYYYMMDD hh24:mi:ss')||','||affiliate_id)
FROM SFI_CUSTOMER_USAGE_HOURLY;
This is giving ORA-01489: result of string concatenation is too long
But this works:
SELECT TO_CLOB(key1) ||','||imsi||','||msisdn||','||voice_mo1||','||voice_mt1||','||
sms_mo1||','||data1||','||voice_mo2||','||voice_mt2||','||sms_mo2||','||
data2||','||voice_mo3||','||voice_mt3||','||sms_mo3||','||data3||','||
voice_mo4||','||voice_mt4||','||sms_mo4||','||data4||','||voice_mo5||','||
voice_mt5||','||sms_mo5||','||data5||','||voice_mo6||','||voice_mt6||','||
sms_mo6||','||data6||','||voice_mo7||','||voice_mt7||','||sms_mo7||','||
data7||','||voice_mo8||','||voice_mt8||','||sms_mo8||','||data8||','||
voice_mo9||','||voice_mt9||','||sms_mo9||','||data9||','||voice_mo10||','||
voice_mt10||','||sms_mo10||','||data10||','||voice_mo11||','||voice_mt11||','||
sms_mo11||','||data11||','||voice_mo12||','||voice_mt12||','||sms_mo12||','||
data12||','||voice_mo13||','||voice_mt13||','||sms_mo13||','||data13||','||
voice_mo14||','||voice_mt14||','||sms_mo14||','||data14||','||
voice_mo15||','||voice_mt15||','||sms_mo15||','||data15||','||
voice_mo16||','||voice_mt16||','||sms_mo16||','||data16||','||
voice_mo17||','||voice_mt17||','||sms_mo17||','||data17||','||
voice_mo18||','||voice_mt18||','||sms_mo18||','||data18||','||
voice_mo19||','||voice_mt19||','||sms_mo19||','||data19||','||
voice_mo20||','||voice_mt20||','||sms_mo20||','||data20||','||
voice_mo21||','||voice_mt21||','||sms_mo21||','||data21||','||
voice_mo22||','||voice_mt22||','||sms_mo22||','||data22||','||
voice_mo23||','||voice_mt23||','||sms_mo23||','||data23||','||
voice_mo24||','||voice_mt24||','||sms_mo24||','||data24||','||
voice_mo25||','||voice_mt25||','||sms_mo25||','||data25||','||
voice_mo26||','||voice_mt26||','||sms_mo26||','||data26||','||
voice_mo27||','||voice_mt27||','||sms_mo27||','||data27||','||
voice_mo28||','||voice_mt28||','||sms_mo28||','||data28||','||
voice_mo29||','||voice_mt29||','||sms_mo29||','||data29||','||
voice_mo30||','||voice_mt30||','||sms_mo30||','||data30||','||
voice_mo31||','||voice_mt31||','||sms_mo31||','||data31||','||
TO_CHAR(ts_last_modified,'YYYYMMDD hh24:mi:ss')||','||affiliate_id
FROM SFI_CUSTOMER_USAGE_HOURLY;
No comments:
Post a Comment