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

Breadcrumb

May 4th

Question and Answer

Connor McDonald

Thanks for the question, Dejan.

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

Last updated: August 31, 2023 - 2:56 am UTC

Version: 12.1.0.2

Viewed 10K+ times! This question is

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

  (4 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.

A reader, August 30, 2023 - 8:30 am UTC

I'm trying to do the same thing in my ADB db, once we create a clone DB from the production the email subject picks the prod db name. Do we have any way to dynamically pick the db name or we have drop and recreate the email notification?

SELECT
CLOUD_IDENTITY INTO L_OCI_METADATA
FROM
DBA_PDBS;

L_OCI_JSON_OBJ := JSON_OBJECT_T.PARSE(L_OCI_METADATA);
L_ADB_NAME := L_OCI_JSON_OBJ.GET_STRING('DATABASE_NAME');
L_ADB_TYPE := L_OCI_JSON_OBJ.GET_STRING('SERVICE');
L_ADB_INFO := L_ADB_NAME
||'-'
||L_ADB_TYPE;

BEGIN
DBMS_SCHEDULER.ADD_JOB_EMAIL_NOTIFICATION(
JOB_NAME => J.JOB_NAME,
RECIPIENTS => L_ADB_RECIPIENT,
SENDER => L_ADB_SENDER,
SUBJECT => 'Oracle Scheduler Job Notification: '
||L_ADB_INFO
||' :%job_name%-%event_type%',
BODY => 'Job: %job_owner%.%job_name%.%job_subname%
Event: %event_type%
Date: %event_timestamp%
Run count: %run_count%
Failure count: %failure_count%
Error code: %error_code%
Error message: %error_message%',
EVENTS => 'job_disabled, job_failed, job_over_max_dur'
);
DBMS_OUTPUT.PUT_LINE('Added Email Notification to '
|| J.JOB_NAME);
EXCEPTION WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('Failed to set notification for job: '||J.JOB_NAME);
END;
Connor McDonald
August 31, 2023 - 2:56 am UTC

Sorry, I think you might be out of luck there.

To my knowledge, the only dynamic thing is the "percent" variables, and the database name is not one of those options.

Everything else is evaluated statically at the moment you create the notification.

As I mentioned

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.