Oracle schedule job email notification question

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 => '',
    sender     => '',
    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

Who is Participating?
Chakravarthi AyyalaDatabase AdministratorCommented:
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.
Chakravarthi AyyalaDatabase AdministratorCommented:
May I ask you whether it is sending the servername of your mail server host?
toookiAuthor Commented:
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..

toookiAuthor Commented:
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.
Chakravarthi AyyalaDatabase AdministratorCommented:
Glad that you found a solution.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.