Oracle - Aggregate CLOB within PIVOT

Question

I have large user data containing their location permissions and groups of these permissions. I need to do a report containing only 1 row for every user.

There are so many permissions per one user in one group of them, I need to use my own aggregate function LISTAGG_CLOB which makes aggregate list but returns CLOB (more than 4000 characters).

This query gives me the list of all users with their permissions but it makes one row for every group of them.

select u.NAME,
lpg.NAME,
LISTAGG_CLOB(l.NAME)
from USERS u
left join LOCATION_PERM lp
on lp.USER_ID = u.ID
left join LOCATION l
on l.ID = lp.LOCATION
left join LOCATION_PERM_GROUP lpg
on lpg.ID = lp.GROUP
group by u.NAME, lpg.NAME

I tried to pivot these data but I can't get it to work, because Oracle doesn't recognize my own aggregate function as aggregate function and none aggregate funcitons (besides LISTAGG which is too small) is meant to aggregate strings.

Samples of what I tried are:

1)

select * from (
    select u.NAME as USER,
    lpg.NAME as PERM_GROUP,
    LISTAGG_CLOB(l.NAME) as PERMISSIONS
    from USERS u
    left join LOCATION_PERM lp
    on lp.USER_ID = u.ID
    left join LOCATION l
    on l.ID = lp.LOCATION
    left join LOCATION_PERM_GROUP lpg
    on lpg.ID = lp.GROUP
    group by u.NAME, lpg.NAME)
pivot (LISTAGG(PERMISSIONS) within group (order by PERMISSIONS) for PERM_GROUP in ('Global', 'Orders', 'Admin') )

But it produces

Buffer too small for CLOB to CHAR or BLOB to RAW conversion error

2)

select * from (
    select u.NAME as USER,
    lpg.NAME as PERM_GROUP,
    LISTAGG_CLOB(l.NAME) as PERMISSIONS
    from USERS u
    left join LOCATION_PERM lp
    on lp.USER_ID = u.ID
    left join LOCATION l
    on l.ID = lp.LOCATION
    left join LOCATION_PERM_GROUP lpg
    on lpg.ID = lp.GROUP
    group by u.NAME, lpg.NAME)
pivot (DISTINCT(PERMISSIONS) for PERM_GROUP in ('Global', 'Orders', 'Admin') )

But it says "missing expression" so I guess I can't use DISTINCT (or UNIQUE) keyword in place of aggregate function. I also tried MAX and other aggregate functions but they accept only numbers as input.

Any suggestions how to pivot these data?

Oracle - Aggregate CLOB within PIVOT

0 Answers