Have Geek Brain

WM_CONCAT uses CASE

I have a choice:

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 the error:  ORA-06502: PL/SQL: : character string buffer too small ... "WMSYS.WM_CONCAT_IMPL"

To avoid the buffer limiting 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 so, I have the same error. How can I avoid this error?

+2


a source to share


1 answer


WM_CONCAT returns VARCHAR2 and therefore has a 4000 character limit in SQL. You can write your own string aggregation function that returned a CLOB if you need more. However, it might be better to think about why you are doing this and if there is no better way at all - for example, using the 10G COLLECT function to return a collection.



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

+3


a source






More articles:


All Articles