I am building an interactive report with Apex. Now, I created an trigger that sned an email whenever the a table is updated. I also created a scheduler that run a procedure which update the table at daily basis. the procedure can be run successfully by itself, but when it runs in the job, the job failed, and detailed error is
ORA-20001: This procedure must be invoked from within an application session. ORA-06512: at "APEX_050000.WWV_FLOW_MAIL", line 562 ORA-06512: at "APEX_050000.WWV_FLOW_MAIL", line 588 ORA-06512: at "APEX_050000.WWV_FLOW_MAIL", line 621 ORA-06512: at "APEX_050000.WWV_FLOW_MAIL_API", line 47 ORA-06512: at "USER.EMAIL_UPDATED", line 3 ORA-04088: error during execution of trigger 'USER.EMAIL_UPDATED' ORA-06512: at "USER.CHANGE_CURRENT_CONTACT", line 19 ORA-06512: at line 1
I can run the job successfully with the command as below only by setting use_current_session attribute to true.
BEGIN
DBMS_SCHEDULER.RUN_JOB(
JOB_NAME => 'update_contact',
USE_CURRENT_SESSION => TRUE);
END;
I searched on the internet and find that it is something to do with the security issues. I tried to add find the workspace id by using 'select workspace_id from apex_applications where application_id = :p_app_id' and add a command ' wwv_flow_api.set_security_group_id(workspace_id);'. But that doesn't work.
How can I fix that problem? Thanks in advance for your help!