Link to home
Start Free TrialLog in
Avatar of toooki
toooki

asked on

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....
thanks!
BEGIN
DBMS_SCHEDULER.ADD_JOB_EMAIL_NOTIFICATION (
    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

Avatar of Chakravarthi Ayyala
Chakravarthi Ayyala
Flag of United States of America image

May I ask you whether it is sending the servername of your mail server host?
Avatar of toooki
toooki

ASKER

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

Thanks!
ASKER CERTIFIED SOLUTION
Avatar of Chakravarthi Ayyala
Chakravarthi Ayyala
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of toooki

ASKER

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.
Glad that you found a solution.