Sample for adding scheduler e-mail notification
Connected to SQL*PLUS using a privileged user.Using the set_scheduler_attribute procedure we have set the email_sender attribute to the SMTP server IP address, and specified the port to 25:
SQL> connect / as sysdba
Connected.
SQL> exec DBMS_SCHEDULER.SET_SCHEDULER_ATTRIBUTE('email_server','10.155.252.333:25');
PL/SQL procedure successfully completed.
where:
host is the host name or IP address of the SMTP server.
port is the TCP port on which the SMTP server listens. If not specified, the default port of 25 is used.
If this attribute is not specified, set to NULL, or set to an invalid SMTP server address, the Scheduler cannot send job state e-mail notifications. SMTP servers that require secure sockets (SSL) connections or require user authentication are not supported.
Optional Setup (Default email sender )
Using the same procedure we have set the email_sender attribute to set the default email address for the email notifications:
SQL> exec DBMS_SCHEDULER.SET_SCHEDULER_ATTRIBUTE('email_sender','DBMS_NOTIFICATION@organization.net');
PL/SQL procedure successfully completed.
Confirm the email server has been set
Using the get_scheduler_attribute procedure we can check the current values of both email_server and email_sender attributes.
declare
v_att VARCHAR2(64);
v_att2 varchar2(64);
BEGIN
DBMS_SCHEDULER.GET_SCHEDULER_ATTRIBUTE('email_server', v_att);
DBMS_SCHEDULER.GET_SCHEDULER_ATTRIBUTE('email_sender', v_att2);
dbms_output.put_line('server: ' || v_att);
dbms_output.put_line('sender: ' || v_att2);
END;
/
Create Sample Job
Connecting to the test user to create a sample job and created the test_mail job, that will start after 10 seconds from now.
SQL> BEGIN
DBMS_SCHEDULER.CREATE_JOB (
job_name => 'TEST_MAIL',
job_type => 'PLSQL_BLOCK',
job_action => 'begin
for i in 1..200 loop
for j in 1..200 loop
null;
end loop;
end loop;
end;
',
start_date => systimestamp + interval '10' second ,
repeat_interval => 'FREQ=MINUTELY;INTERVAL=1',
enabled => True,
comments => '');
END;
/
Add Job Notifications to send emails :
Added the mail notification to the job, we have altered the value of the sender, so the default sender will not be used. Also we used job_all_events that will send mail notification for all events:
SQL> BEGIN
DBMS_SCHEDULER.ADD_JOB_EMAIL_NOTIFICATION (
job_name => 'TEST_MAIL',
recipients => 'arvind@organization.net',
sender => 'DBMS_NOTIFICATION@organization.net',
subject => 'Scheduler Job Notification-%job_owner%.%job_name%-%event_type%',
body => '%event_type% occurred at %event_timestamp%. %error_message%',
events => 'job_all_events');
END;
/
PL/SQL procedure successfully completed.
To review the scheduler email notifications setup, we will use the newly introduced views:
USER | ALL | DBA_SCHEDULER_NOTIFICATIONS
SQL> desc user_SCHEDULER_NOTIFICATIONS
Name Null? Type
----------------------------------------- -------- ----------------------------
JOB_NAME NOT NULL VARCHAR2(30)
JOB_SUBNAME VARCHAR2(30)
RECIPIENT NOT NULL VARCHAR2(4000)
SENDER VARCHAR2(4000)
SUBJECT VARCHAR2(4000)
BODY VARCHAR2(4000)
FILTER_CONDITION VARCHAR2(4000)
EVENT VARCHAR2(19)
EVENT_FLAG NOT NULL NUMBER
SQL> select EVENT from user_SCHEDULER_NOTIFICATIONS where job_name='TEST_MAIL';
EVENT
-------------------
JOB_STARTED
JOB_SUCCEEDED
JOB_FAILED
JOB_BROKEN
JOB_COMPLETED
JOB_STOPPED
JOB_SCH_LIM_REACHED
JOB_DISABLED
JOB_CHAIN_STALLED
JOB_OVER_MAX_DUR
10 rows selected
Finally, after all the testing is done we can now remove the (Just) email notification part for the job:
SQL> connect test_job/test_job
Connected.
SQL> exec DBMS_SCHEDULER.REMOVE_JOB_EMAIL_NOTIFICATION ('TEST_MAIL');
PL/SQL procedure successfully completed.
Drop scheduler job :
SQL> BEGIN
DBMS_SCHEDULER.DROP_JOB(job_name => 'TEST_MAIL',
defer => false,
force => false);
END;
/
Note : this has been tested on Oracle 11g Databases
excelent
ReplyDelete