Oracle DBMS Scheduler Email Notifications Dynamically Send Server and Instance Name

Question

We have two Oracle database servers. One for production with one instance on it, and another one with multiple instances for Dev, Test, etc. I have some jobs that run on production and email me when they fail. I had originally set the sender to be something like [email protected], so that I knew what environment the error was coming from.

Problem is when we copy production to one of the test or dev environments to get them up to date, then the job errors out for whatever reason, but it says it is coming from [email protected] which is incorrect.

I would like the notification email to pick up the servername and instance name that it is sent from, so if it comes from test it would be [email protected]. If that is not possible, then it would be fine to get this info in the subject line or body of the email.

I can get the server and instance name pretty easy with

SELECT  host_name, instance_name
FROM    v$instance;

,but I can't figure out how to get this to be used in the job email notifications.

I set these jobs up using the sql developer wizard, so if you can show me how to change it with that would be fine, or with pl/sql code, I am ok with either.

If none of this is possible, then is there I way I can make the jobs to not send out once they get to the non production environments at least because I really don't care if they fail there.

Answer 1

SYS_CONTEXT can be used to extract the host name of the database server and the instance name.

You simply call dbms_scheduler.add_job_email_notification in order to add the email notification like the following:

BEGIN
DBMS_SCHEDULER.add_job_email_notification (
  job_name => <JOBNAME>,
  recipients => <EMAIL>,
  subject => 
    SYS_CONTEXT('USERENV','SERVER_HOST') || ' ' || 
    SYS_CONTEXT('USERENV','INSTANCE_NAME') ||
    ' - Scheduler Job Notification-%job_owner%.%job_name%-%event_type%', 
  events => 'job_failed');
END;

The 12c documention for all of the SYS_CONTEXT parameters can be found here.

Answer 2

when setting up job_notifications DO NOT set a value for the sender. instead set the scheduler attribute email_sender for your db.

the default email_sender will be the value provided for the email_sender as shown below. DBMS_SCHEDULER.SET_SCHEDULER_ATTRIBUTE('email_sender','[email protected]');