Oracle schedule job email notification question

Posted on 2011-10-18
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 => '',
    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

Question by:toooki
    LVL 8

    Expert Comment

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

    Author Comment

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

    LVL 8

    Accepted Solution

    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

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

    Expert Comment

    Glad that you found a solution.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How to run any project with ease

    Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
    - Combine task lists, docs, spreadsheets, and chat in one
    - View and edit from mobile/offline
    - Cut down on emails

    Entity Framework is a powerful tool to help you interact with the DataBase but still doesn't help much when we have a Stored Procedure that returns more than one resultset. The solution takes some of out-of-the-box thinking; read on!
    Entering a date in Microsoft Access can be tricky. A typo can cause month and day to be shuffled, entering the day only causes an error, as does entering, say, day 31 in June. This article shows how an inputmask supported by code can help the user a…
    This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
    This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

    737 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

    Need Help in Real-Time?

    Connect with top rated Experts

    18 Experts available now in Live!

    Get 1:1 Help Now