Script: role_ddl.sql

-- -----------------------------------------------------------------------------------
-- File Name    : https://oracle-base.com/dba/script_creation/role_ddl.sql
-- Author       : Tim Hall
-- Description  : Displays the DDL for a specific role.
-- Call Syntax  : @role_ddl (role)
-- Last Modified: 27/07/2022 - Increase long to 1000000.
-- -----------------------------------------------------------------------------------

set long 1000000 longchunksize 20000 pagesize 0 linesize 1000 feedback off verify off trimspool on
column ddl format a1000

begin
   dbms_metadata.set_transform_param (dbms_metadata.session_transform, 'SQLTERMINATOR', true);
   dbms_metadata.set_transform_param (dbms_metadata.session_transform, 'PRETTY', true);
end;
/
 
variable v_role VARCHAR2(30);

exec :v_role := upper('&1');

select dbms_metadata.get_ddl('ROLE', r.role) AS ddl
from   dba_roles r
where  r.role = :v_role
union all
select dbms_metadata.get_granted_ddl('ROLE_GRANT', rp.grantee) AS ddl
from   dba_role_privs rp
where  rp.grantee = :v_role
and    rownum = 1
union all
select dbms_metadata.get_granted_ddl('SYSTEM_GRANT', sp.grantee) AS ddl
from   dba_sys_privs sp
where  sp.grantee = :v_role
and    rownum = 1
union all
select dbms_metadata.get_granted_ddl('OBJECT_GRANT', tp.grantee) AS ddl
from   dba_tab_privs tp
where  tp.grantee = :v_role
and    rownum = 1
/

set linesize 80 pagesize 14 feedback on verify on