Oracle

Job Notification in 10g

From long time i was planning to setup e-mail notification for jobs scheduled using DBMS_SCHEDULAR. Finally, today i got the chance of doing it and i was successful 🙂
Downloaded the Job Notification pkg from http://www.oracle.com/technology/products/database/scheduler/index.html.Its very simple to use.Made one change in the .sql script as per my requirement where i needed JOB_SUCCEEDED to be in the default list of the event.So, just added JOB_SUCCEEDED in events column of procedure sys.add_job_email_notification.
Once, the changes were done, performed the below steps:-
1.Connect as SYS and run the job_notification script with 2 parameters, an outgoing e-mail SMTP server and port
sql>@job_notification 123.45.67.89 55
where 123.45.67.89 and 55 represents the email server host ip and the email server port number respectively.
After the script has run successfully,
2. Connect as the owner of the job and execute the below procedure
begin
add_job_email_notification
(job_name => ‘BACKUP’,
recepient_address =>’[email protected]’,
sender_address =>’[email protected]’,
email_server_host =>’123.45.67.89′,
email_server_port => 55);
end;
/
For example:-
22:13:18 TEST>sho user
USER is “SYSTEM”
22:13:20 TEST >
22:13:30 TEST >begin
22:13:58   2   add_job_email_notification
22:13:58   3   (job_name  => ‘FULL_EXPORT’,
22:13:58   4   recipient_address => ‘[email protected]’,
22:13:58   5   sender_address => ‘[email protected]’,
22:13:59   6    email_server_host =>’123.45.67.89′,
22:14:00   7   email_server_port => 55);
22:14:01   8   end;
22:14:02   9  /
PL/SQL procedure successfully completed.
As, the procedure has been successfully completed 🙂 , i received a mail in my mailbox after the job ran, as:-
Subject:-Oracle Scheduler Job Notification – SYSTEM.FULL_EXPORT JOB_SUCCEEDED
Body Content:-
Job: “SYSTEM”.”FULL_EXPORT”
Event: JOB_SUCCEEDED
Date: 23-SEP-09 08.00.31 PM +05:30
Log ID: 38203

One thought on “Job Notification in 10g

Leave a Reply