Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Dynamic Oracle Pivot_In_Clause

Tags:

sql

oracle

pivot

I'm kinda stuck. I want to do a user-role-relationship pivot table and my query so far looks like this:

WITH PIVOT_DATA AS (
     SELECT *
     FROM
     (
         SELECT USERNAME, GRANTED_ROLE
         FROM DBA_USERS@DB_LINK U LEFT OUTER JOIN DBA_ROLE_PRIVS@DB_LINK R
         ON U.USERNAME = R.GRANTEE
      )
)
SELECT *
FROM PIVOT_DATA
PIVOT
(
    COUNT(GRANTED_ROLE)
    FOR GRANTED_ROLE
    IN('CONNECT') -- Just an example
)
ORDER BY USERNAME ASC;

It works really fine and does the job, but I don't want to write to write any role I want to search for in the pivot_in_clause, because we got like tons of them and I don't want to check every time if there are any changes.

So is there a way to write a SELECT in the pivot_in_clause? I tried it myself:

[...]
PIVOT
(
    COUNT(GRANTED_ROLE)
    FOR GRANTED_ROLE
    IN( SELECT ROLE FROM DBA_ROLES@DB_LINK )
)
[...]

But it always gives me an ORA-00936: "missing expression" in line 1 of the whole query and I don't know why. Can't there be a SELECT in the pivot_in_clause or am I doing it wrong?

like image 431
Chris.V Avatar asked Jun 07 '13 06:06

Chris.V


People also ask

What is the dynamic SQL in Oracle?

Dynamic SQL is a programming technique that enables you to build SQL statements dynamically at runtime. You can create more general purpose, flexible applications by using dynamic SQL because the full text of a SQL statement may be unknown at compilation.

What is dynamic insert in Oracle?

Dynamic insertion is the insertion of a new valid combination into a combinations table from a form other than the combinations form. If you allow dynamic inserts when you set up your key flexfield, a user can enter a new combination of segment values using the flexfield window from a foreign key form.

Can we use subquery in pivot in Oracle?

Oracle PIVOT with subqueryWhen you use a subquery in the pivot_in_clause , Oracle uses all values returned by the subquery for pivoting. Note that the subquery must return a list of unique values. Otherwise, Oracle will raise a run-time error.


1 Answers

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

variable rr refcursor

declare 
  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 200
SET LINESIZE 16000
print :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      
like image 194
krokodilko Avatar answered Sep 23 '22 07:09

krokodilko