I am creating a job to execute a procedure that sends emails to email addresses registered in the database. When I run the procedure, everything works fine, but when I schedule the job to run, nothing happens, even though the job status is "executed".
I've read the documentation but I can't understand why. My job below:
BEGIN
DBMS_SCHEDULER.create_job (
job_name => 'job_enviar_email',
job_type => 'PLSQL_BLOCK',
job_action => 'BEGIN PROC_ENVIA_EMAIL; END;',
start_date => SYSTIMESTAMP,
repeat_interval => 'FREQ=DAILY; BYHOUR=13; BYMINUTE=52',
enabled => TRUE
);
END;
My procedure:
create or replace procedure "PROC_ENVIA_EMAIL" IS
CURSOR c_filiados IS
SELECT EMAIL FROM SF_FILIACAO;
v_email VARCHAR2(255);
l_body VARCHAR2(4000);
l_body_html VARCHAR2(4000);
BEGIN
FOR r_filiado IN c_filiados LOOP
v_email := r_filiado.email;
BEGIN
l_body := 'Please use a HTML mail client.';
l_body_html := '<html><head></head><body>' ||
'<p>HTML message body.</p>' ||
'</body></html>';
APEX_MAIL.SEND(
p_from => 'no-reply@apex-test.com',
p_to => v_email,
p_body => l_body,
p_body_html => l_body_html,
p_subj => 'HTML message subject.');
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('Erro ao enviar email para ' || v_email || ': ' || SQLERRM);
END;
END LOOP;
COMMIT;
end "PROC_ENVIA_EMAIL";
/
Does anyone have any tips or ideas on what could be going wrong?
