How do I tell if a role is valid?
April 8th, 2011 MattJust a quick post in response to a question on the OTN forums today – if I have a role, how do I know whether it’s valid or not?
The quickest way to do it (I think!) would be just to check the end date – if the end date is set, and we’re past that point in time, then the role is invalid. BUT – what if there is more to it than that? What if Oracle modifies the product so that the end date field isn’t the only thing that needs to be tested?
The way that I recommend doing the check (and I’ve recently written some code around this very subject!) is to use the standard WF_DIRECTORY API to fetch me the information about the role. If the role is invalid, then there won’t be any data returned; if the rolw is valid, then you get data back.
And here’s the code:
DECLARE
l_display_name WF_ROLES.display_name%TYPE;
l_email_address WF_ROLES.email_address%TYPE;
l_notification_pref WF_ROLES.notification_preference%TYPE;
l_language WF_ROLES.language%TYPE;
l_territory WF_ROLES.territory%TYPE;
l_module VARCHAR2(100);
l_role WF_ROLES.name%TYPE := '&role';
BEGIN
WF_DIRECTORY.getRoleInfo ( role => l_role
, display_name => l_display_name
, email_address => l_email_address
, notification_preference => l_notification_pref
, language => l_language
, territory => l_territory );
IF l_display_name IS NULL THEN
dbms_output.put_line('Role is invalid');
ELSE
dbms_output.put_line('Role is valid');
END IF;
END;
/
It can be downloaded here.
