This content has been marked as final. Show 4 replies
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
v_Recipient VARCHAR2(400) := 'email@example.com';
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: ' || 'firstname.lastname@example.org' || 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 .
Are you sure that you don't have two instances of the job ?