CODEWITHSUNDEEP
sqlstringplsqlbuffer
Ruslan
Ruslan

Reputation: 319

Character string buffer too small

I have select:

select v.accs, v.currency,v.amount,v.drcr_ind, count(*) qua,wm_concat(ids) npx_IDS,
wm_concat(px_dtct) npx_DTCT 
from table v
group by accs, currency, amount, drcr_ind

but i get error ORA-06502: PL/SQL: : character string buffer too small if i'll remove one string, because sometimes (when v.accs= 3570) count(*) = 215 but when i try to skip using wm_concat for v.accs= 3570 for example this way:

select v.accs, v.currency,v.amount,v.drcr_ind, count(*) qua,wm_concat(ids) npx_IDS,
(case when v.accs = 3570 then wm_concat(px_dtct) else 'too many' end) npx_DTCT 
from table v
group by accs, currency, amount, drcr_ind

i still have the same error message. But why?

Upvotes: 0

Views: 2411

Answers (3)

Rene
Rene

Reputation: 10541

You concatenate results from a query. This query can result in a lot of rows so eventually you will run out of string length. Maybe concatenation is not the way to go here. Depends on what you want to achieve of course.

Upvotes: 1

Kirill Leontev
Kirill Leontev

Reputation: 10941

First, as it has already been told, you have to switch then and else clauses in your query. Then, I guess you should also similarily process your second wm_concat, the one that works with ids. 

select v.accs, v.currency,v.amount,v.drcr_ind, count(*) qua,
(case when v.accs = 3570 then 'too many' else wm_concat(ids) end) npx_IDS,
(case when v.accs = 3570 then 'too many' else wm_concat(px_dtct) end) npx_DTCT 
from table v
group by accs, currency, amount, drcr_ind

And, finally, why do you think that only v.accs = 3570 is able to bring 06502 error in front of you? I suppose you should handle all of them.

Upvotes: 0

Erich Kitzmueller
Erich Kitzmueller

Reputation: 36987

Why? Because you still use wm_concat for accs=3570... swap the THEN and ELSE part of your CASE expression

select v.accs, v.currency,v.amount,v.drcr_ind, count(*) qua,wm_concat(ids) npx_IDS,
       (case when v.accs = 3570 then 'too many' else wm_concat(px_dtct) end) npx_DTCT
  from table v group by accs, currency, amount, drcr_ind

Upvotes: 0

Related Questions