I have 4 DBMS scheduler jobs , which checks for a specific job status in DB and sends an email , when i started the schedule for the first week the jobs executed fine from next week I am getting two emails from each job , when i check the logs USER_SCHEDULER_JOB_RUN_DETAILS I see only one run , which seems weird to me so i disabled one job and left the three jobs in schedule , next time i got two emails from 3 jobs and one from disabled job . After checking logs i see that there is no entry of the disabled job execution . I am not sure where is the problem i can't find any log from where the disabled job executing. Please help me
Job schedule is to run every Saturday
Interval setup :
start_date => trunc(SYSDATE)+ 8.5/24,
repeat_interval => 'TRUNC(LEAST(NEXT_DAY(SYSDATE,''SATURDAY'') )) + 8.5/24'
I tried to schedule the same jobs using DBMS_JOB but i still get the same problem , I created the procedure with all code in and scheduled it using dbms job , first day it run once second day it run twice ( sending two emails) Inow i am not sure if issue is with my code or scheduler Procedure
v_Recipient VARCHAR2(400) := 'firstname.lastname@example.org';
v_Subject VARCHAR2(80) := 'TEST_Email';
v_Mail_Host VARCHAR2(30) := 'localhost';
crlf VARCHAR2(2) := chr(13)||chr(10);
select count(*) into v_count from TEC_CODERETURN@RPRD where interface like 'FOR002B' and trunc(rundate) =trunc(sysdate);
if v_count = 0
v_Mail_Conn := utl_smtp.Open_Connection(v_Mail_Host, 25);
'Date: ' || to_char(sysdate, 'Dy, DD Mon YYYY hh24:mi:ss') || UTL_TCP.CRLF ||
'From: ' || 'email@example.com' || UTL_TCP.CRLF ||
'Subject: '|| v_Subject || UTL_TCP.CRLF ||
'To: ' || v_Recipient || UTL_TCP.CRLF ||
'This is a test Alert'|| UTL_TCP.CRLF
WHEN utl_smtp.Transient_Error OR utl_smtp.Permanent_Error then
raise_application_error(-20000, 'Unable to send mail: '||sqlerrm);
DBMS job creation
(job => jobno,
what => 'TEST_ALERT;',
next_date => trunc(sysdate)+0.1/24,
interval => 'SYSDATE + 1',
no_parse => TRUE );
DBMS_OUTPUT.put_line ('Created Job - the job number is:' || TO_CHAR (jobno));
It's really ver curious.
When you run you code (procedure) by hand how many email do you receive ?
Are you sure that you have this job defined only once ?
Run job sucessfuly (dba_jobs.failures=0 for this job)?
I tested these jobs with minutes interval close to 30 40 times , it runs fine with one email. once the day passes it starts sending second email. one time i disabled the job for a day but i still got email form the job and i don't see any log of the job run. Seems Oracle bug.
We opened SR but no reply yet so I had to switch to UNIX using Cron and sendmail .