[SQL] 동적 오라클 Pivot_In_Clause

동적 오라클 Pivot_In_Clause

나는 좀 붙어있어. 나는 사용자 역할 관계 피벗 테이블을하고 싶은 내 쿼리는 지금까지 다음과 같습니다 :

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;

정말 잘 작동하고 일을한다, 그러나 나는 우리가 그들을 톤처럼 가지고 있기 때문에, 나는 pivot_in_clause에서 검색하려는 역할을 쓰고 쓰고 싶지 않아 내가가있는 경우 모든 시간을 확인하고 싶지 않아 변경 사항이 적용됩니다.

그래서 pivot_in_clause에서 SELECT를 작성하는 방법은 무엇입니까? 내가 직접 시도 :

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

전체 쿼리의 1 행에 "표현 누락"내가 왜 몰라 :하지만 항상 나에게 ORA-00936을 제공합니다. 거기 pivot_in_clause에 SELECT 할 수 없거나 내가 잘못을하고있는 중이 야?

해결법

1. ==============================

   1.당신은 당신의 스크립트에서 동적 쿼리를 구축 할 수 있습니다 이 예를 보면 :

   당신은 당신의 스크립트에서 동적 쿼리를 구축 할 수 있습니다 이 예를 보면 :

   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
   

   (매우 폭 길이 때문에 단지 작은 단편) 여기서 결과는

   -----------------------------------------------------------------------------------------------------------------------------------
       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      
   

from https://stackoverflow.com/questions/16978047/dynamic-oracle-pivot-in-clause by cc-by-sa and MIT license