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.