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 ..
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.
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
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;
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.