Hi All,
I have created a job using DBMS_SCHEDULER in oracle and add email notifications.
Job is running fine as per schedule but email is not receiving.
Could you help on this.
I have followed the below steps.
Step 1:
DECLARE
p_source_schema VARCHAR2(50):= '&p_source_schema';
p_dw_scheama VARCHAR2(50):= '&p_dw_scheama';
BEGIN
DBMS_SCHEDULER.CREATE_JOB (
job_name => 'DATAMART_JOB',
job_type => 'PLSQL_BLOCK',
job_action => 'BEGIN DATAMART_DATALOAD_PRC('''||p_source_schema||''','''||p_dw_scheama||'''); END ;',
start_date => NULL,
repeat_interval => 'FREQ=MINUTELY;INTERVAL=15',
end_date => NULL,
enabled => FALSE,
auto_drop => FALSE,
comments => 'To load source data into DW ');
DBMS_SCHEDULER.SET_ATTRIBUTE(
name => 'DATAMART_JOB',
attribute => 'job_priority', value => '1');
DBMS_SCHEDULER.SET_ATTRIBUTE(
name => 'DATAMART_JOB',
attribute => 'logging_level', value => DBMS_SCHEDULER.LOGGING_OFF);
DBMS\_SCHEDULER.ENABLE ('DATAMART\_JOB\_TEST');
END;
Step2:
BEGIN
DBMS_SCHEDULER.ADD_JOB_EMAIL_NOTIFICATION (
job_name => 'DATAMART_JOB',
recipients => 'prasad.a@****.com',
sender => 'prasad.a@***.com',
subject => 'Oracle Scheduler Job Notification - %job_owner%.%job_name%.%job_subname% %event_type%',
body => 'Job: %job_owner%.%job_name%.%job_subname%
Event: %event_type%
Date: %event_timestamp%
Log id: %log_id%
Job class: %job_class_name%
Run count: %run_count%
Failure count: %failure_count%
Retry count: %retry_count%
Error code: %error_code%
Error message:
%error_message%
',
events => 'JOB_BROKEN, JOB_CHAIN_STALLED, JOB_FAILED, JOB_OVER_MAX_DUR, JOB_RUN_COMPLETED, JOB_SCH_LIM_REACHED, JOB_STOPPED, JOB_SUCCEEDED',
filter_condition => ''
);
END;
Step 3:
exec dbms_scheduler.set_scheduler_attribute('email_server','smtp.office365.com');
Thanks
Prasad