WM_CONCAT use CASE

Question

I have a select:

select substr(acc,1,4)
       ,currency
       , amount
       , module
       , count(*)
       , wm_concat(trn_ref_no) trn  
from all_entries 
where date = to_date ('01012010','DDMMYYYY')
group by substr(acc,1,4),currency, amount, module

In this case I get an error: ORA-06502: PL/SQL: : character string buffer too small ... "WMSYS.WM_CONCAT_IMPL"

To avoid buffer limit error I changed it to:

select substr(acc,1,4)
        ,currency
       , amount
       , module
      , count(*)
      , (case when count(*) < 10 then wm_concat(trn_ref_no) else null end) trn  
from fcc.acvw_all_ac_entries 
where trn_dt = to_date ('05052010','DDMMYYYY')
group by substr(acc,1,4),currency, amount, module

But even in this case i have the same error. How can i avoid this error?

Answer 1

WM_CONCAT returns a VARCHAR2, and so is constrained to a maximum of 4000 characters in SQL. You could write your own string aggregate function that returned a CLOB if you need more than that. However, it may be better to think about why you are doing this, and whether there isn't a better way altogether - e.g. using the 10G COLLECT function to return a collection.

See this article on string aggregation techniques for how you might write your own aggregate function.