set long 100000
set longchunksize 200
set heading off
set feedback off
set echo off
set verify off
undefine role
select (case
when ((select count(*)
from dba_roles
where role = '&&role') > 0)
then dbms_metadata.get_ddl ('ROLE', '&&role')
else to_clob ('Role does not exist')
end ) Extracted_DDL from dual
UNION ALL
select (case
when ((select count(*)
from dba_role_privs
where grantee = '&&role') > 0)
then dbms_metadata.get_granted_ddl ('ROLE_GRANT', '&&role')
end ) from dual
UNION ALL
select (case
when ((select count(*)
from dba_role_privs
where grantee = '&&role') > 0)
then dbms_metadata.get_granted_ddl ('DEFAULT_ROLE', '&&role')
end ) from dual
UNION ALL
select (case
when ((select count(*)
from dba_sys_privs
where grantee = '&&role') > 0)
then dbms_metadata.get_granted_ddl ('SYSTEM_GRANT', '&&role')
end ) from dual
UNION ALL
select (case
when ((select count(*)
from dba_tab_privs
where grantee = '&&role') > 0)
then dbms_metadata.get_granted_ddl ('OBJECT_GRANT', '&&role')
end ) from dual;