Dealing with errors when creating an adhoc role – part two – duplicate roles

In this post, I wrote about how you can use custom code to create an adhoc role which suppresses errors when the role is invalid.

The next logical extension to the code is another common problem – what happens if the list of users contains duplicate records?  If you attempt to call the standard WF_DIRECTORY APIs, and the list of users contains duplicates, then the API will return an error:

  1  DECLARE
  2    v_rn           VARCHAR2(100);
  3    v_dn           VARCHAR2(100);
  4    v_role_users   WF_DIRECTORY.UserTable;
  5
  6  BEGIN
  7    v_rn := 'MATT'||TO_CHAR(SYSTIMESTAMP,'YYYYMMDDHH24MISSFF');
  8    v_dn := v_rn;
  9
 10    v_role_users(1) := 'SEARLEM';
 11    v_role_users(2) := 'SEARLEM';
 12    v_role_users(3) := 'SEARLEM';
 13
 14    WF_DIRECTORY.CREATEADHOCROLE2 ( ROLE_NAME         => v_rn
 15                                  , ROLE_DISPLAY_NAME => v_dn
 16                                  , ROLE_USERS        => v_role_users );
 17
 18* END;
APPS@R12_DEV > /
DECLARE
*
ERROR at line 1:
ORA-20002: 4016: User/Role relationship for user 'SEARLEM' and
          role 'MATT20120123115841745021000' already exists.
ORA-06512: at "APPS.WF_DIRECTORY", line 2005
ORA-06512: at line 14

In this example, the API raises the same ORA-20002 error with a Workflow specific error ID of 4016, which we can modify the code from the earlier post to catch and deal with this error as well:

CREATE OR REPLACE PROCEDURE xx_create_adhocrole
       ( role_name               IN VARCHAR2
       , role_display_name       IN VARCHAR2
       , language                IN VARCHAR2 DEFAULT NULL
       , territory               IN VARCHAR2 DEFAULT NULL
       , role_description        IN VARCHAR2 DEFAULT NULL
       , notification_preference IN VARCHAR2 DEFAULT 'MAILHTML'
       , role_users              IN WF_DIRECTORY.UserTable
       , email_address           IN VARCHAR2 DEFAULT NULL
       , fax                     IN VARCHAR2 DEFAULT NULL
       , status                  IN VARCHAR2 DEFAULT 'ACTIVE'
       , expiration_date         IN DATE     DEFAULT NULL
       , parent_orig_system      IN VARCHAR2 DEFAULT NULL
       , parent_orig_system_id   IN NUMBER   DEFAULT NULL
       , owner_tag               IN VARCHAR2 DEFAULT NULL ) IS

  e_workflow_error  EXCEPTION;
  PRAGMA EXCEPTION_INIT (e_workflow_error , -20002);

BEGIN

  WF_DIRECTORY.CreateRole ( role_name               => role_name
                          , role_display_name       => role_display_name
                          , orig_system             => 'WF_LOCAL_ROLES'
                          , orig_system_id          => 0
                          , language                => language
                          , territory               => territory
                          , role_description        => role_description
                          , notification_preference => notification_preference
                          , email_address           => email_address
                          , fax                     => fax
                          , status                  => status
                          , expiration_date         => expiration_date
                          , parent_orig_system      => parent_orig_system
                          , parent_orig_system_id   => parent_orig_system_id
                          , owner_tag               => owner_tag );

  IF role_users.COUNT > 0 THEN
    FOR i IN 1 .. role_users.COUNT LOOP
      BEGIN

        WF_DIRECTORY.AddUsersToAdHocRole ( role_name  => role_name
                                         , role_users => role_users(i));

      EXCEPTION
        WHEN e_workflow_error THEN
          IF   (SQLERRM LIKE 'ORA-20002:%3205%')
            OR (SQLERRM LIKE 'ORA-20002:%4016%') THEN
            NULL;
          ELSE
            RAISE;
          END IF;

        WHEN OTHERS THEN
          RAISE;

      END;
    END LOOP;
  END IF;

END xx_create_adhocrole;
/

This version of the code can be downloaded here.

In the next post in this series, I’ll be looking at the different errors which the WF_DIRECTORY API can throw.

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