How do I tell if a role is valid?

Just 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.

By continuing to use the site, you agree to the use of cookies. more information

In common with almost all professionally run websites, this website logs the IP address of each visitor in order to keep it running reliably. This is also essential for protecting the website and its visitors from malicious attacks, including infection with malware.

This website provides information as a service to visitors such as yourself, and to do this reliably and efficiently, it sometimes places small amounts of information on your computer or device (e.g. mobile phone). This includes small files known as cookies. The cookies stored by this website cannot be used to identify you personally.

We use cookies to understand what pages and information visitors find useful, and to detect problems such as broken links, or pages which are taking a long time to load.

We sometimes use cookies to remember a choice you make on one page, when you have moved to another page if that information can be used to make the website work better. For example:
- avoiding the need to ask for the same information several times during a session (e.g. when filling in forms), or
- remembering that you have logged in, so that you don’t have to re-enter your username and password on every page.

You can prevent the setting of cookies by adjusting the settings on your browser (see your browser Help for how to do this). Be aware that disabling cookies will affect the functionality of this and many other websites that you visit.

Close