CODEWITHSUNDEEP
oracle-databaseplsql
user16509434
user16509434

Reputation: 1

Display the output of EXECUTE IMMEDIATE statement within a LOOP

I am trying to display the two column values (grantee and granted_role) from this execute immediate statement and tried various methods but nothing seems to work.

declare
v_stmt1 varchar2(1024);
begin
  for x in ( select  grantee from dba_tab_privs where grantee IN (select role from dba_roles)  and PRIVILEGE not in ('SELECT') group by grantee)
  loop
   v_stmt1 := 'select grantee,granted_role from dba_role_privs where granted_role in ('||''''||x.grantee||''''||')';
      execute immediate v_stmt1;
   DBMS_OUTPUT.PUT_LINE --- How to display to screen???
end loop;  
end;
/

Important Note: I am writing a procedure which will display list of users who have a granted role which has privileges other than SELECT. Any help would be greatly appreciated.

Upvotes: 0

Views: 194

Answers (1)

Littlefoot
Littlefoot

Reputation: 142705

There's nothing dynamic in your query, so I suggest you don't use it at all & simplify code (as a consequence):

SQL> set serveroutput on
SQL>
SQL> BEGIN
  2     FOR x
  3        IN (SELECT grantee, granted_role
  4              FROM dba_role_privs
  5             WHERE granted_role IN
  6                      (  SELECT grantee
  7                           FROM dba_tab_privs
  8                          WHERE     grantee IN (SELECT role FROM dba_roles)
  9                                AND PRIVILEGE NOT IN ('SELECT')
 10                       GROUP BY grantee))
 11     LOOP
 12        DBMS_OUTPUT.put_line (x.grantee || ': ' || x.granted_role);
 13     END LOOP;
 14  END;
 15  /
SYS: APEX_ADMINISTRATOR_READ_ROLE
SYS: ORDS_ADMINISTRATOR_ROLE
SYS: ORDS_RUNTIME_ROLE
ORDS_PUBLIC_USER: ORDS_RUNTIME_ROLE
SYS: APEX_ADMINISTRATOR_ROLE
<snip>

Upvotes: 1

Related Questions