Use of hash functions in group by?

Question

I'd use a listagg() to generate a list of components a certain customer has and compare it to another list generated the same way somewhere else to detect inconsistencies. The problem is that sometimes a customer can have too many components and exceed the maximum output of listagg(). I was wondering if there could be a way to generate a hash from a group of ordered rows, in a way I could use to make the same validation. Sort of:

with my_tabe as
(
    select 'user1' as usrid, '1' as prodcode from dual union
    select 'user1' as usrid, '2' as prodcode from dual union
    select 'user1' as usrid, '3' as prodcode from dual union
    select 'user2' as usrid, '2' as prodcode from dual union
    select 'user2' as usrid, '3' as prodcode from dual union
    select 'user2' as usrid, '4' as prodcode from dual
)
select
    userid,
    ORA_HASH(prodcode) WITHIN GROUP (ORDER BY prodcode)
from
    my_table
group by userid

Is there any function that could be used to that end?

Answer 1

You can use xmlagg instead of listagg as a workaround for the listagg limitation:

SQL> set long 10000
SQL> select listagg(id, ',') within group (order by id)
  2    from (select level as id from dual connect by level < 1050)
  3  /
  from (select level as id from dual connect by level < 1050)
                                *
ERROR at line 2:
ORA-01489: result of string concatenation is too long


SQL> select rtrim(xmlagg(xmlelement(e,id,',').extract('//text()') order by id).GetClobVal(),',')
  2    from (select level as id from dual connect by level < 1050)
  3  /

RTRIM(XMLAGG(XMLELEMENT(E,ID,',').EXTRACT('//TEXT()')ORDERBYID).GETCLOBVAL(),','
--------------------------------------------------------------------------------
1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30
,31,32,33,34,35,36,37,38,39,40,41,42,43,44,45,46,47,48,49,50,51,52,53,54,55,56,5
7,58,59,60,61,62,63,64,65,66,67,68,69,70,71,72,73,74,75,76,77,78,79,80,81,82,83,
84,85,86,87,88,89,90,91,92,93,94,95,96,97,98,99,100,101,102,103,104,105,106,107,
108,109,110,111,112,113,114,115,116,117,118,119,120,121,122,123,124,125,126,127,
128,129,130,131,132,133,134,135,136,137,138,139,140,141,142,143,144,145,146,147,
148,149,150,151,152,153,154,155,156,157,158,159,160,161,162,163,164,165,166,167,
...