Goal: Provide ability to have a user click a "Forgot password" button on the APEX application login page, and have APEX reset the password and email password to user.
Issue: If the user forgot his password, I cannot invoke
apex_util.reset_pw(:p100_username);
as a process on that page, as it will give the error
User nobody requires ADMIN privilege to perform this operation.
I have tried invoking the following PL/SQL block via a scheduled job, using dbms_scheduler.create_job:
declare
n_security_group_id number;
begin
n_security_group_id := apex_util.find_security_group_id(p_workspace=>v('WORKSPACE_NAME'));
apex_util.set_security_group_id(n_security_group_id);
apex_util.reset_pw(p_user=> v('P1101_USERNAME'),
p_msg=> 'Your password reset request for ' || v('APP_TITLE') || ' is being processed by ' || job_owner );
update KFNAD.ref_users set PASSWORD_RESET_DONE = sysdate where email_address = upper(v('P1101_USERNAME'));
commit;
end;
When the job runs, I get this error:
ORA-20001: Package variable g_security_group_id must be set.
ORA-06512: at "APEX_050000.WWV_FLOW_API", line 1339
ORA-06512: at "APEX_050000.WWV_FLOW_API", line 1374
ORA-06512: at "APEX_050000.WWV_FLOW_FND_USER_INT", line 2606
ORA-06512: at "APEX_050000.HTMLDB_UTIL", line 1444 ORA-06512: at line 8
I am able to invoke that PL/SQL block in the APEX SQL Workshop without any errors.