How to get list of grantee who does have two different roles and comabine all grantee and granted_role to one output

Sometimes the requirement comes from Apps Team to provide the list of users whose role starts with XYZ or ABCD for a specific profile say DEFAULT.

Usually what we are doing, we are fetching all the list of users who does have XYZ role make one report and agaon for ABCD roles and making another report..But in my today's blog how to combine both the report to one.

Scenario 1: I am finding all the grantee who does have roles starts with JAVA

P.N - KHATAI is my database name :) :)

SQL> select name,username,account_status,profile,granted_role from v$database u, dba_users v , dba_role_privs w where
granted_role like 'JAVA%' and w.grantee=v.username and v.username!='SYSTEM'  and profile='DEFAULT' order by 2;


NAME      USERNAME                       ACCOUNT_STATUS
--------- ------------------------------ -----------------------
PROFILE                        GRANTED_ROLE
------------------------------ ------------------------------
KHATAI    ORDSYS                         EXPIRED & LOCKED
DEFAULT                        JAVAUSERPRIV

KHATAI    OWBSYS                         EXPIRED & LOCKED
DEFAULT                        JAVAUSERPRIV

KHATAI    OWBSYS                         EXPIRED & LOCKED
DEFAULT                        JAVA_ADMIN

KHATAI    SYS                            OPEN
DEFAULT                        JAVAIDPRIV

KHATAI    SYS                            OPEN
DEFAULT                        JAVADEBUGPRIV

KHATAI    SYS                            OPEN
DEFAULT                        JAVAUSERPRIV

KHATAI    SYS                            OPEN
DEFAULT                        JAVA_ADMIN

KHATAI    SYS                            OPEN
DEFAULT                        JAVA_DEPLOY

KHATAI    SYS                            OPEN
DEFAULT                        JAVASYSPRIV

KHATAI    XDB                            EXPIRED & LOCKED
DEFAULT                        JAVAUSERPRIV


10 rows selected.

SQL>

Scenario 2: I am finding all the grantee who does have roles starts with OLAP


select name,username,account_status,profile,granted_role from v$database u, dba_users v , dba_role_privs w where
granted_role like 'OLAP%' and w.grantee=v.username and v.username!='SYSTEM'  and profile='DEFAULT' order by 2;


NAME      USERNAME                       ACCOUNT_STATUS                   PROFIL
E                        GRANTED_ROLE
--------- ------------------------------ -------------------------------- ------
------------------------ ------------------------------
KHATAI    OLAPSYS                        EXPIRED & LOCKED                 DEFAUL
T                        OLAP_DBA
KHATAI    OWBSYS                         EXPIRED & LOCKED                 DEFAUL
T                        OLAP_USER
KHATAI    SYS                            OPEN                             DEFAUL
T                        OLAP_XS_ADMIN
KHATAI    SYS                            OPEN                             DEFAUL
T                        OLAP_USER
KHATAI    SYS                            OPEN                             DEFAUL
T                        OLAPI_TRACE_USER
KHATAI    SYS                            OPEN                             DEFAUL
T                        OLAP_DBA

6 rows selected.


Now I want to combine both the roles and want a single output with (10rows for JAVA+6 rows of OLAP role) = 16rows.

select name,username,account_status,profile,granted_role from v$database u, dba_users v , dba_role_privs w where
REGEXP_LIKE(w.granted_role,'^(JAVA|OLAP)') and w.grantee=v.username and v.username!='SYSTEM'  and profile='DEFAULT' order by 2;



NAME      USERNAME                       ACCOUNT_STATUS
--------- ------------------------------ ------------------------------
PROFILE                        GRANTED_ROLE
------------------------------ ------------------------------
KHATAI    OLAPSYS                        EXPIRED & LOCKED
DEFAULT                        OLAP_DBA

KHATAI    ORDSYS                         EXPIRED & LOCKED
DEFAULT                        JAVAUSERPRIV

KHATAI    OWBSYS                         EXPIRED & LOCKED
DEFAULT                        JAVA_ADMIN

KHATAI    OWBSYS                         EXPIRED & LOCKED
DEFAULT                        JAVAUSERPRIV

KHATAI    OWBSYS                         EXPIRED & LOCKED
DEFAULT                        OLAP_USER

KHATAI    SYS                            OPEN
DEFAULT                        JAVAUSERPRIV

KHATAI    SYS                            OPEN
DEFAULT                        JAVADEBUGPRIV

KHATAI    SYS                            OPEN
DEFAULT                        OLAPI_TRACE_USER

KHATAI    SYS                            OPEN
DEFAULT                        OLAP_DBA

KHATAI    SYS                            OPEN
DEFAULT                        JAVA_ADMIN

KHATAI    SYS                            OPEN
DEFAULT                        JAVAIDPRIV

KHATAI    SYS                            OPEN
DEFAULT                        OLAP_USER

KHATAI    SYS                            OPEN
DEFAULT                        JAVA_DEPLOY

KHATAI    SYS                            OPEN
DEFAULT                        JAVASYSPRIV

KHATAI    SYS                            OPEN
DEFAULT                        OLAP_XS_ADMIN

KHATAI    XDB                            EXPIRED & LOCKED
DEFAULT                        JAVAUSERPRIV


16 rows selected.

SQL>


Hope it will helpful to you !! Enjoy !!