PL/SQL Script to Remove and Add Users to Adhoc Role

DECLARE
  ln_role_users_count NUMBER;
  lc_role_name apps.wf_roles.NAME%TYPE  := 'XX_TEST_ROLE';
BEGIN
  ------------------------------
  --Checking if Adhoc Role Exits
  ------------------------------
  SELECT COUNT (1)
  INTO ln_role_users_count
  FROM apps.wf_user_roles
  WHERE NAME       = lc_role_name;
 
  IF ln_role_users_count > 0 THEN
      --------------------------------
	  --Removing users from Adhoc Role
      --------------------------------
	  apps.wf_directory.removeusersfromadhocrole
				(role_name      => lc_role_name);
 
    dbms_output.put_line("Users removed from Role!");
  END IF;
  --------------------------------
  --Adding users to Adhoc Role
  --------------------------------  
  --< Add custom logic to add multiple users using a for loop >--
    apps.wf_directory.adduserstoadhocrole
                (role_name       => lc_role_name,
                 role_users      => 'STHALLAM');  
 
END; 
/

NOTE: You should not use COMMIT in this script if you are using this in a Procedure of a Workflow Activity.

Also See: