create function user_has_role (p_user in varchar2  default user, p_role  in varchar2) 
return boolean
as
 
select * from dba_role_privs where p_user = GRANTED_ROLE;
 
end 

create function user_has_role(p_user in varchar2 default user, p_role in varchar2)
return boolean

as

select count(1) 
into v_count
from dba_role_privs
where grantee = p_user
and granted_role = p_role;
 
if v_count = 0
then
  return false;
else
  return true;
end if;

create or replace function user_has_role(p_user in varchar2 default user, p_role in varchar2)
return boolean as
  v_count  pls_integer;
begin
select count(*) 
into v_count
from dba_role_privs
where grantee = p_user
and granted_role = p_role;
if v_count = 0
then
  return false;
else
  return true;
end if;
end;

if user_has_role('username','rolename') then
  -- user has the role, so do something
else
  -- user does not have role, so do something else
end if;

SQL> create or replace function fun_will_fail return number is
  2  begin
  3    select ename
  4      from emp
  5      where empno = 12345;
  6  end;
  7  /

Warning: Function created with compilation errors.

SQL> show errors
Errors for FUNCTION FUN_WILL_FAIL:

LINE/COL ERROR
-------- -----------------------------------------------------------------
3/3      PLS-00428: an INTO clause is expected in this SELECT statement
SQL>

SQL> create or replace function user_has_role(p_user in varchar2 default user, p_role in varchar2)
  2  return boolean as
  3    v_count  pls_integer;
  4  begin
  5  select count(*) 
  6  into v_count
  7  from dba_role_privs
  8  where grantee = p_user
  9  and granted_role = p_role;
 10  if v_count = 0
 11  then
 12    return false;
 13  else
 14    return true;
 15  end if;
 16  end;
 17  /

Warning: Function created with compilation errors.

SQL> show errors
Errors for FUNCTION USER_HAS_ROLE:

LINE/COL ERROR
-------- -----------------------------------------------------------------
5/1      PL/SQL: SQL Statement ignored
7/6      PL/SQL: ORA-00942: table or view does not exist

SQL> select * from all_role_privs;
select * from all_role_privs
              *
ERROR at line 1:
ORA-00942: table or view does not exist

SQL> connect scott/tiger
Connected.

SQL> select count(*) from dba_role_privs;
select count(*) from dba_role_privs
                     *
ERROR at line 1:
ORA-00942: table or view does not exist


SQL> connect sys/***** as sysdba
Connected.

SQL> grant select on dba_role_privs to scott;

Grant succeeded.

SQL> connect scott/tiger
Connected.

SQL> select count(*) from dba_role_privs;

  COUNT(*)
----------
        68

SQL>

SQL> show user
USER is "SYS"
SQL> select * from dictionary where table_name like '%ROLE%';

TABLE_NAME                     COMMENTS
------------------------------ ---------------------------------------------
DBA_ROLES                      All Roles which exist in the database
USER_ROLE_PRIVS                Roles granted to current user
DBA_ROLE_PRIVS                 Roles granted to users and roles
DBA_CONNECT_ROLE_GRANTEES      Information regarding which users are granted
                                CONNECT

ROLE_ROLE_PRIVS                Roles which are granted to roles
ROLE_SYS_PRIVS                 System privileges granted to roles
ROLE_TAB_PRIVS                 Table privileges granted to roles
SESSION_ROLES                  Roles which the user currently has enabled.

8 rows selected.

SQL>

SQL> select owner, object_type
  2  from dba_objects
  3  where object_name = 'DBA_ROLE_PRIVS';

OWNER                          OBJECT_TYPE
------------------------------ -----------------
SYS                            VIEW
PUBLIC                         SYNONYM

SQL>

SQL> grant select on dba_role_privs to scott;

Grant succeeded.

SQL> connect scott/tiger
Connected.

SQL> create or replace function user_logged_in(p_user in varchar2 default user)
  2      return boolean as
  3        v_count  pls_integer;
  4      begin
  5      select count(*)
  6      into v_count
  7      from dba_role_privs
  8      where grantee = p_user;
  9      if v_count = 0
 10     then
 11       return false;
 12     else
 13       return true;
 14     end if;
 15     end;
 16     /

Function created.

SQL>