Dealing with errors when creating an adhoc role – part three – WF_DIRECTORY error codes

In this last part in the series, I’m going to look at some other common errors which you might hit when creating an adhoc role using the WF_DIRECTORY APIs.  The first part of the series can be read here, and the second part is here.

The table below lists the error numbers and messages which can be returned by the WF_DIRECTORY package – the SQLCODE returned by the error will always be -20002, with the error number included in the text of the SQLERRM.

Error Name Number Error Message
WF_DUP_ROLE 395 Duplicate role &DISPNAME (‘&ROLENAME’)
WF_INVALID_ROLE 396 Invalid role name ‘&ROLENAME’
WF_DUP_USER 397 Duplicate user &DISPNAME (‘&USERNAME’)
WF_INVALID_USER 398 Invalid user name ‘&USERNAME’
WF_NO_LANG_TERR 399 Could not resolve NLS language and territory
WF_INVALID_NTF_PREF 4004 ‘&NTF_PREF’ is not an allowed notification preference.
WF_INVAL_CONCAT_NAME 4001 ‘&NAME’ is an invalid internal name because it contains a colon. For this particular role, if the name contains a colon, it has to be ‘&ORIG_SYSTEM:&ORIG_SYS_ID’ .
WF_ROLENAME_TOO_LONG 4002 ‘&NAME’ exceeds the maximimum length of &LENGTH characters.
WF_INVALID_ROLE_STATUS 4003 ‘&STATUS’ is not an allowed status.
WF_DUP_USER_ROLE 4016 User/Role relationship for user ‘&UNAME’ and role ‘&RNAME’ already exists.

This list of errors is not a definitive list of every error which may be thrown by the API, this is a list of the most likely ones which can occur when using adhoc roles.

If you need to look for any of these errors in your code, then include the error codes within the exception handler – one thing to be careful of (and it’s caught me in the past!) is that if you are working in multiple languages, ensure that you only look for the error number rather than the error message as well.  For example, check for “%4016%” within SQLERRM rather than looking for “%4016%already exists”, since the text will vary depending on the Workflow language!

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.

Dealing with errors when creating an adhoc role – part one – invalid roles

I was recently asked about a problem with trying to create and adhoc role, and some of the errors which might occur.

I am sending notification to multiple users.  While doing that if the roles are valid, then there is no problem.

The problem occurs when one of the roles isn’t valid, for example:
v_role_users => ‘ROLE1 ROLE2 ROLE3’;

In this example, if ROLE2 is not valid then the notification is not sent to anyone because the role cannot be built. Instead, we get this error: 3205: ‘ADHOC_ROLE’ is not a valid role or user name.

If one of the roles is invalid, I still want the notification to be sent to the valid roles.

How to achieve this?

The way round the problem is to modify the code which builds the role, so that rather than passing all the users into the API at the same time, they are passed in sequentially.  When each role is added, include additional error handling to catch any error and handle that.

Unfortunately, the Workflow development team use the same custom error code (-20002) for all errors which occur in the product, which means that as well as looking for the error to occur, we then need to parse the SQLERRM error message to check for the Workflow specific error number (3205 in this case).

The following code creates a new procedure which you can call to create the role, without any particularly invasive changes – instead of calling WF_DIRECTORY APIs to create a new role, call the procedure instead, which will suppress expected errors but still raise anything unexpected:

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%' THEN
            NULL;
          ELSE
            RAISE;
          END IF;

        WHEN OTHERS THEN
          RAISE;

      END;
    END LOOP;
  END IF;

END xx_create_adhocrole;
/

The SQL file can also be found here.

In later posts, I’ll be looking at extending this to catch different, common errors.

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