Fairly new to APEX, so apologies if I'm missing any information. I have a simple function for granting a role to a user, which is contained in a package.
create or replace FUNCTION GRANT_ROLE(P_USERNAME IN VARCHAR2, P_ROLE IN VARCHAR2) RETURN NUMBER AS
P_SQL VARCHAR2(400);
BEGIN
P_SQL := 'GRANT ' || P_ROLE || ' TO ' || P_USERNAME;
EXECUTE IMMEDIATE P_SQL;
RETURN 1;
EXCEPTION WHEN OTHERS THEN
RETURN 0;
END GRANT_ROLE_TO;
Works fine in SQL Developer. In APEX, I have a select list of users interactive grid of available roles, an interactive grid with the roles the selected user currently holds. I've loaded two values into hidden elements (P3_USER being the username and P3_CURRENT_ROLE_SELECTION being the role that wants to be given) and pass them into the function so that it can run the grant command. I just have a button with a dynamic action that runs the PL/SQL
declare myVar number := 0;
begin
select PACKAGE.GRANT_ROLE(:P3_USER, :P3_CURRENT_ROLE_SELECTION) into myVar from dual;
end;
The issue I'm running into now is the button doesn't currently do anything. No errors are thrown, but the user is not receiving the role. I'm unsure if there's any other page submit, or branch on submit elements I have to add in order for the database to be properly modified by this function.