How to Change this Pl/SQL Codes to handle multiple roles in Apex?

Question

I have 2 codes of pl/sql to add and edit users and assign them to roles. However I don't know how to modify these codes to make Apex accept more than one role.

This is my ADD_USER

begin

APEX_CUSTOM_AUTH.SET_USER(:P7_USER_NAME);

APEX_ACL.ADD_USER_ROLE (
p_application_id => 100,
p_user_name => :P7_USER_NAME,
p_role_static_id => :P7_USER_ROLE
);
end;

This is my EDIT_USER

DECLARE

l_as_user_any_role BOOLEAN := false;

BEGIN

l_as_user_any_role := APEX_ACL.HAS_USER_ANY_ROLES (
p_application_id => 100,
p_user_name => :P7_USER_NAME);

if l_as_user_any_role then

APEX_ACL.REPLACE_USER_ROLES (
p_application_id => 100,
p_user_name => :P7_USER_NAME,
p_role_static_ids => wwv_flow_t_varchar2( :P7_USER_ROLE) );

end if;
end;

Now the issues

1 - I can add, and edit a user if he only has 1 role. Working fine.

2 - I cannot add or edit a user if I try to assign the user multiple roles.

Here is the error on submit: No data was found.

Can anyone help correct my codes?

Answer 1

Did you try apex_t_varchar2 type for p_role_static_ids parameter:

begin
  apex_util.set_workspace(:WORKSPACE);
  apex_acl.add_user_roles(
    -- assigns an array of roles to a user
    p_application_id  => 100,
    p_user_name       => :P7_USER_NAME,
    p_role_static_ids => apex_t_varchar2('USER', 'ADMINISTRATOR')
  );
  commit;
end;
/

You should also check your page item P7_USER_ROLE if it holds the values as comma seperated text for multiple values. The "no data found" indicates that either the user name is not found in the system or the role does not match. That's why, I'd debug my page item value to see if really is what I expected.