Learn how to a build a cloud-first strategyRegister Now


Oracle schedule job email notification question

Posted on 2011-10-18
Medium Priority
Last Modified: 2012-05-12
I get incorrect email content from the Oracle (11gR2) schedule job email notification:

The email notification has shows incorrect value for the part of the "Subject".
SYS_CONTEXT('USERENV','SERVER_HOST') -- this part should shows another server name.

I separately tested:
select sys_context('userenv','server_host') from dual;
On the server and using the same user and I get the correct output (correct server name).

If I re-create a new job and add the email notification (via the same attached code) I get the correct "Subject".

In case the sys_context('userenv') parameter was changed since the scheduled job was created -- the scheduled job will show the old parameter value. I guess I could fix this by dropping and recreating the scheduled job. But that will erase the job history -- I guess so -- if, so that is not a choice.

Otherwise how can I let it show the correct server name in the current job itself....
    job_name   => 'MY_JOB',
    recipients => 'me@yahoo.com',
    sender     => 'me@yahoo.com',
    events     => 'job_failed',
    subject    => 'My Job Failed: %job_owner%.%job_name%'||' - '||SYS_CONTEXT('USERENV','SERVER_HOST'),
    body       => 'Job Failed: %job_owner%.%job_name% Event: %event_type% Date: %event_timestamp% Error code: %error_code% Error message: %error_message%'

Open in new window

Question by:toooki
  • 3
  • 2

Expert Comment

by:Chakravarthi Ayyala
ID: 36990722
May I ask you whether it is sending the servername of your mail server host?

Author Comment

ID: 36996145
Thank you.
No it is sending a server name that is unrelated to the server on which the scheduled job is running. It seems some parameter was hard coded somewhere -- causing this...

Wondering if drop/recreate a job with the same job name retains the history of the job..


Accepted Solution

Chakravarthi Ayyala earned 1900 total points
ID: 36996307
Wondering if drop/recreate a job with the same job name retains the history of the job..
  => Though we use object names(Job is also an object to oracle), for our understanding, Oracle keeps the details based on the ID that it generates for an object.
  => Hence, I strongly doubt that it maintains the history if you drop and recreate it.
  => But, if you are talking about the data in either of the tables, dba_Scheduler_job_LOG or dba_scheduler_jobs, you can save that information for a job in another table of your own.

Author Comment

ID: 36997538
Ok thank you for the explanation. I tried with a test job log and it seemed what you said about the history is right.

However, I found a solution to the problem above.

I just rerun the same dbms_scheduler.add_job_email_notification script keeping the main job as it is. Rerunning this fixed the issue.
I should have tested that before....
Thank you.

Expert Comment

by:Chakravarthi Ayyala
ID: 36998091
Glad that you found a solution.

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Creating a Cordova application which allow user to save to/load from his Dropbox account the application database.
MSSQL DB-maintenance also needs implementation of multiple activities. However, unprecedented errors can hamper the database management. In that case, deploying Stellar SQL Database Toolkit ensures fast and accurate database and backup repair as wel…
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…

810 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question