Hi,
I am trying to setup a email notification job using Oracle 11gR2. Per online documnetation I have done the following setup, but the email notification job is failing. If you guys have any ideas, please assist.
Step 1:
BEGIN
DBMS_SCHEDULER.set_scheduler_attribute('email_server', 'oracle.kotaise.com:25');
DBMS_SCHEDULER.set_scheduler_attribute('email_sender', 'no_reply@kotaise.com');
END;
/
Step2:
BEGIN
DBMS_SCHEDULER.create_job (
job_name => 'email_notification_job',
job_type => 'PLSQL_BLOCK',
job_action => 'BEGIN NULL; END;',
start_date => SYSTIMESTAMP,
repeat_interval => 'freq=minutely; bysecond=0',
enabled => TRUE);
END;
/
Step 3:
BEGIN
DBMS_SCHEDULER.ADD_JOB_EMAIL_NOTIFICATION (
job_name => 'email_notification_job',
recipients => 'chartere@kotaise.com',
sender => 'no_reply@kotaise.com',
subject => 'Scheduler Job Notification-%job_owner%.%job_name%-%event_type%',
body => '%event_type% occurred at %event_timestamp%. %error_message%',
events => 'JOB_FAILED, JOB_BROKEN, JOB_DISABLED, JOB_SCH_LIM_REACHED');
END;
/
Step 4:
I have created the following job that executes a package which executes a package "test_package.pdata". This package is not created yet, becuase I wanted to see whether the email notification is working or not. As expected the "TEST_JOB" is failed becuase of missing package. But the email notification job "'email_notification_job'", which runs every minute, failed to send an email notification.
BEGIN
SYS.DBMS_SCHEDULER.CREATE_JOB
(
job_name => 'test_job'
,start_date => SYSTIMESTAMP
,repeat_interval => 'freq=minutely; bysecond=0'
,end_date => NULL
,job_class => 'DEFAULT_JOB_CLASS'
,job_type => 'PLSQL_BLOCK'
,job_action => 'BEGIN test_package.pdata; END;'
,comments => NULL
,enabled => TRUE
);
end;
/
I have queried the DBA_SCHEDULER_NOTIFICATIONS table, and I see that the email notification job did not started.
JOB_NAME RECIPIENT EVENT
EMAIL_NOTIFICATION_JOB chartere@kotaise.com JOB_FAILED
EMAIL_NOTIFICATION_JOB chartere@kotaise.com JOB_BROKEN
EMAIL_NOTIFICATION_JOB chartere@kotaise.com JOB_SCH_LIM_REACHED
EMAIL_NOTIFICATION_JOB chartere@kotaise.com JOB_DISABLED
Thanks