Dealing with errors when creating an adhoc role – part two – duplicate roles
January 30th, 2012 MattIn 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.
