DBMS_SESSION.IS_ROLE_ENABLED Interesting (to me anyway) thing I learned recently.

DBMS_SESSION.IS_ROLE_ENABLED

The call looks like this:

DBMS_SESSION.IS_ROLE_ENABLED (
   rolename    VARCHAR2) 
  RETURN BOOLEAN;

A few times in the pass I've had need to check the role of the logged in user. I would do that with something like this:

CREATE OR REPLACE
FUNCTION role_enabled( p_role_name IN VARCHAR2 ) RETURN BOOLEAN
IS
  l_dummy VARCHAR2(1);
BEGIN
  SELECT 'Y'
  INTO l_dummy
  FROM dba_role_privs
  WHERE grantee = USER
    AND role = p_role_name;

  RETURN TRUE;
EXCEPTION
  WHEN no_data_found THEN
    RETURN FALSE;
END role_enabled;
/

I did something similar here (and I can't believe no one told me about this function!).

Lesson? Before building something on your own (even something this simple), search through the documentation to see if Oracle has already done it.

Update This was shown to me by my colleage, @serge_a_storms

Labels: dbms_session, oracle, plsql

  ¶ 2/04/2009 10:21:00 PM