Wednesday, February 4, 2009

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

3 comments:

Simon B said...

The hyperlink to the Oracle documentation points to your local hard drive!

oraclenerd said...

My internal docs...sorry about that.

I've updated the link.

Kris said...

just popping in ;-)