Skip to Main Content
  • Questions
  • DBMS_SCHEDULER.Add_Job_Email_Notification - missing variable for database_name and host

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Dejan.

Asked: January 23, 2017 - 6:45 pm UTC

Last updated: December 13, 2017 - 2:10 am UTC

Version: 12.1.0.2

Viewed 1000+ times

You Asked

Hi,

I am testing scheduler JOB_EMAIL_NOTIFICATION ...
Like documented variables %job_owner%.%job_name%.%job_subname% etc ... I am missing variable for

- database name
- host name

Is it possible to put such information in subject or body of build-in job scheduler email notification ?
--
One way I see is to pass ... DATABASE@host .. into subject or body parameter.
This means different instalation script for each environment. Is this the only solution ?

Regards Dejan



BEGIN
DBMS_SCHEDULER.add_job_email_notification (
job_name => 'JOB1',
recipients => 'recipient@domain',
events => 'job_started, job_succeeded, job_failed',
subject => 'Oracle Scheduler Job Notification DATABASE@host - %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%
' );
END;
/


and Connor said...

When you setup the email notification, you simply nominate it then, ie

subject => 'Oracle Scheduler Job Notification '||
             sys.database_name||'@'||
            sys_context('USERENV','SERVER_HOST')||
           ' - %job_owner%.%job_name%.%job_subname% %event_type%',


You would need to take a little care if you regular datapump the database to another node etc.

Rating

  (3 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

A reader, January 24, 2017 - 9:27 am UTC

Thank You very much !!
I was so focused on trees (%variables%) so I didn't se the forest ..

select sys.database_name||'@'|| sys_context('USERENV','SERVER_HOST') from dual;
select sys_context('USERENV','DB_NAME')||'@'|| sys_context('USERENV','SERVER_HOST') from dual;
select ora_database_name||'@'|| sys_context('USERENV','SERVER_HOST') from dual;


I wish You good luck and also to whole team ..



Connor McDonald
January 24, 2017 - 9:35 am UTC

Glad we could help

Post import solution needed to update Notifications

Terry, November 13, 2017 - 5:58 pm UTC

We've been using the same method that you recommended here to add the SID/Service_name to our job Notifications. However as you alluded to, once we import the schema from another environment (such as a refresh from prod), all of our Notifications will show the source of the import and not the destination. Is the only solution to Drop/Create the Notifications for each job to reset the SID/Service_name to the correct values? It would be nice to be able to call a procedure to handle that like we do in our PL/SQL exception emails.
Connor McDonald
November 14, 2017 - 1:29 am UTC

Sorry, I can't see a way around drop/recreate of the notification.

e-mail notification from using dbms_scheduler vis a vis using utl_smtp

A reader, December 04, 2017 - 8:35 am UTC

Hi, Could you please clarify below-
- Can we send e-mail body as HTML table in case of DBMS_SCHEDULER.add_job_email_notification
- What is the pros and cons of sending e-mail using DBMS_SCHEDULER in stead of UTL_SMTP or UTL_MAIL.

Thanks.
Regards;
GK
Connor McDonald
December 13, 2017 - 2:10 am UTC

- Can we send e-mail body as HTML table in case of DBMS_SCHEDULER.add_job_email_notification

Not to my knowledge. Give it a try - your email server/client may be intelligent enough to pick up tags in the content.

- What is the pros and cons of sending e-mail using DBMS_SCHEDULER in stead of UTL_SMTP or UTL_MAIL.

Using the scheduler means you dont have to build your own capture/reporting solution to handle errors, but of course, you are then limited to whatever we offer rather than having total control.

Most sites I've worked at typically build their own.

More to Explore

Data Pump

All of the database utilities including Data Pump are explained in the Utilities guide.