Output session roles from procedure : Roles « User Privilege « Oracle PL/SQL Tutorial

SQL>
SQL> create or replace procedure myProcedure
  2    authid current_user
  3    as
  4    begin
  5      for rec in (select * from session_roles)
  6      loop
  7        dbms_output.put_line(rec.role);
  8      end loop;
  9    end;
 10    /

Procedure created.

SQL> execute myProcedure
DBA
SELECT_CATALOG_ROLE
HS_ADMIN_ROLE
EXECUTE_CATALOG_ROLE
DELETE_CATALOG_ROLE
EXP_FULL_DATABASE
IMP_FULL_DATABASE
GATHER_SYSTEM_STATISTICS
SCHEDULER_ADMIN
XDBADMIN
XDBWEBSERVICES

PL/SQL procedure successfully completed.

SQL>
SQL>
SQL> drop procedure myProcedure;

Procedure dropped.

SQL>

36.7.Roles
	36.7.1.	Roles
	36.7.2.	Creating Roles
	36.7.3.	Granting Privileges to Roles
	36.7.4.	Granting Roles to a User
	36.7.5.	Assign Role to User
	36.7.6.	Checking Roles Granted to a User
	36.7.7.	Checking System Privileges Granted to a Role
	36.7.8.	Checking Object Privileges Granted to a Role
	36.7.9.	Assign CONNECT and RESOURCE Roles
	36.7.10.	Default Roles
	36.7.11.	Revoking a Role
	36.7.12.	Revoking Privileges from a Role
	36.7.13.	Dropping a Role
	36.7.14.	Regina and Kristen are manager level users
	36.7.15.	Rich and Brad are maintenance level users
	36.7.16.	Unassign Role
	36.7.17.	Drop Role
	36.7.18.	Assign object privileges to roles
	36.7.19.	Unassign Role from User
	36.7.20.	Output session roles from procedure
	36.7.21.	Query user granted roles