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@example.com, 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 firstname.lastname@example.org 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@example.com. 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.