Dynamic Oracle Pivot_In_Clause

You can build dynamic query in your script,look at this example:

variable rr refcursordeclare   bb varchar2(4000);  cc varchar2( 30000 );begin     WITH PIVOT_DATA AS (         SELECT *         FROM         (             SELECT USERNAME, GRANTED_ROLE             FROM DBA_USERS U LEFT OUTER JOIN DBA_ROLE_PRIVS R             ON U.USERNAME = R.GRANTEE          )    )    select ''''|| listagg( granted_role, ''',''' )             within group( order by granted_role ) || '''' as x     into bb    from (      select distinct granted_role from pivot_data    )    ;    cc := q'[    WITH PIVOT_DATA AS (         SELECT *         FROM         (             SELECT USERNAME, GRANTED_ROLE             FROM DBA_USERS U LEFT OUTER JOIN DBA_ROLE_PRIVS R             ON U.USERNAME = R.GRANTEE          )    )    SELECT *    FROM PIVOT_DATA    PIVOT    (        COUNT(GRANTED_ROLE)        FOR GRANTED_ROLE        IN(]'  || bb || q'[) -- Just an example    )    ORDER BY USERNAME ASC]';    open :rr for cc;end;/SET PAGESIZE 200SET LINESIZE 16000print :rr

Here is the result (only small fragment, because it is very wide and long)

-----------------------------------------------------------------------------------------------------------------------------------    USERNAME                       'ADM_PARALLEL_EXECUTE_TASK' 'APEX_ADMINISTRATOR_ROLE' 'AQ_ADMINISTRATOR_ROLE' 'AQ_USER_ROLE'            ------------------------------ --------------------------- ------------------------- ----------------------- ----------------------    ANONYMOUS                      0                           0                         0                       0              APEX_030200                    0                           0                         0                       0            APEX_PUBLIC_USER               0                           0                         0                       0        APPQOSSYS                      0                           0                         0                       0   ..............    IX                             0                           0                         1                       1      OWBSYS                         0                           0                         1                       1