wm_concat function and small characer buffer

Question

Hi, i have select like:

select substr(account,1,4), currency, amount, module,count(*) quan, wm_concat(id) ids from all_transactions group by substr(account,1,4), currency, amount, module

But sometimes COUNT(*) is more then 600. In that case i get: 'ORA-06502: PL/SQL: : character string buffer too small'

Is there any way out to keep wm_concat(id) for all records? Because excluding this function for entries with big COUNT(*) is the way out.

Answer 1

The problem may be that WM_CONCAT() is trying to generate a string greater than the database-side limit on VARCHAR2, which I believe is 2000 characters. In PL/SQL code the limit is large - 32767, if I'm remembering correctly. You may need to break this into multiple queries. First, do your summation

strAccount  VARCHAR2(4);
strCurrency all_transactions.currency%type;
nAmount     all_transactions.amount%type;
strModule   all_transactions.module%type;
nQuantity   NUMBER;

select substr(account,1,4), currency, amount, module, count(*) quan
  into strAccount, strCurrency, nAmount, strModule, nQuantity
  from all_transactions
  group by substr(account,1,4), currency, amount, module 

then walk a cursor to get the names individually and concatenate them together in whatever application language your're using. If your code is written in PL/SQL it might look like:

strNames  VARCHAR2(32767);

FOR aRow in (select id
               from all_transactions
               where substr(account, 1, 4) = strAccount and
                     currency = strCurrency and
                     amount = nAmount and
                     module = strModule
               order by id)
LOOP
  strNames := strNames || aRow.id || ' ';
END LOOP

Granted, this is not the most elegant solution solution in the world, but given that WM_CONCAT isn't practical here you may be stuck doing something like this.

Share and enjoy.