Search DBA_SCHEDULER_JOB_LOG and DBA_SCHEDULER_JOB_RUN_DETAILS views for your job and see if it encountered any errors.
First thing as odie suggested is to check that the job ran successfully. You can manually run the job if necessary through the DBMS_SCHEDULER.RUN_JOB procedure.
That's creazy!!! I've tried to run it by using DBMS_SCHEDULER.RUN_JOB and it works but it doesn't work "alone" schedulated at the time. Why?
What does the DBA_SCHEDULER_JOB_RUN_DETAILS say for the job's status when it ran as scheduled? SUCEEDED?
I get this message:
ORA-20001: This procedure must be invoked from within an application session. ORA-06512: at "APEX_040200.WWV_FLOW_MAIL", line 334 ORA-06512: at "APEX_040200.WWV_FLOW_MAIL", line 371 ORA-06512: at "APEX_040200.WWV_FLOW_MAIL_API", line 47 ORA-06512: at "ISM_TIMESHEET_SCHEMA.PRC_AVVISO_COMPILAZ_CONSUNTIVI", line 8
It seems that I can't schedulate a job launching a stored procedure containing the HTMLDB_MAIL.SEND built-in, can I?
My stored procedure is this:
create or replace procedure "PRC_AVVISO_COMPILAZ_CONSUNTIVI"
for c1 in (select nvl(e_mail_ufficio,e_mail) e_mail
where username = 'MYNAME') loop
if c1.e_mail is not null then
P_TO => c1.e_mail,
P_FROM => 'firstname.lastname@example.org',
P_BODY => 'Si ricorda di approvare il consuntivo on line',
P_SUBJ => 'Si ricorda di approvare il consuntivo on line');
I've also tried to use the classic UTL_MAIL.SEND but it dosn't work on oracle APEX in clouding because when I compile the stored procedure I get the message: "PLS-00201: identifier 'UTL_MAIL.SEND' must be declared. Compilation failed"
1 person found this helpful
You probably can, apparently it's a Apex security group issue. You need to set the Apex security group otherwise you get this error. I'm not familiar with Apex, so I can't really tell you how to do it.
Thanks. I'm asking to APEX forum