Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Oralce 11g Scheduled Job Email notification

Posted on 2011-02-18
12
Medium Priority
?
5,153 Views
Last Modified: 2013-11-11
I have a created a scheduled job to run on Oracle 11g database everyday night at  8PM. I could I create an email notification so that it will automatically send me an email when the Job finishes (completes its run)?
I attached my scheduled job, which calls an Oracle stored procedure.
name => 'TestJOB', force => TRUE);
DBMS_SCHEDULER.CREATE_JOB(job_name        => 'MyJobName',
                            job_type        => 'STORED_PROCEDURE',
                            job_action      => 'procedurename',
                            start_date      => to_timestamp_tz('01/10/2011 20:00:00 US/Central',
                                                               'mm/dd/yyyy hh24:mi:ss tzr'),
                            repeat_interval => 'FREQ=hourly; INTERVAL=6',
                            enabled         => true,
                            auto_drop       => false,
                            comments        => 'Refreshes SoF tables');

Open in new window

0
Comment
Question by:toooki
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 5
12 Comments
 
LVL 74

Accepted Solution

by:
sdstuber earned 1800 total points
ID: 34926618
after creating your job,  add a notification for it with

DBMS_SCHEDULER.ADD_JOB_EMAIL_NOTIFICATION

something like this...


BEGIN
 DBMS_SCHEDULER.add_job_email_notification (
  job_name   =>  'MyJobName',
  recipients =>  'youraddress@yourcompany.com',
  events     =>  'job_started, job_succeeded');
END;


http://download.oracle.com/docs/cd/E11882_01/appdev.112/e16760/d_sched.htm#BABBFBGI
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 34926684
if you don't want email when the job starts, only when it finishes

 events     =>  'job_succeeded');

you can also get emailed for errors, then

events     =>  'job_failed');

you can include specific errors to check for with the filter_condition parameter

examples and syntax are in the link above
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 34926712
also note,  the notification procedure is only available in 11gR2 and above.

10g and 11gR1 don't have it, so you would have to implement the email some other way
either as an exception handler within a job, or as a separate job that monitors the other job and emails based on the results of previous executions
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 

Author Comment

by:toooki
ID: 34934158
Thank you. I am a bit stuck as I could not figure out if my database in 11gR2 (or 11gR1). I do not have DBA privilege.
SQL>SELECT version FROM v$instance;
Says I do not have access to the table.
Is there any other way to find the DB version? However, I know it is of course 11g.
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 34934326
select * from v$version
0
 

Author Comment

by:toooki
ID: 34934385
Thank you. Shows 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production.
So will follow your previous directions.
0
 

Author Comment

by:toooki
ID: 35174136
I get the attached error when I try to create this notification job

ORA-00900: Invalid SQL statement
DBMS_SCHEDULER.ADD_JOB_EMAIL_NOTIFICATION (
    job_name       =>      MY_LOAD_DATA_JOB,
    recipients      =>     'myemail@yahoo.com',
    --sender               IN VARCHAR2 DEFAULT NULL,
    --subject              IN VARCHAR2 DEFAULT DBMS_SCHEDULER.DEFAULT_NOTIFICATION_SUBJECT,
    --body                 IN VARCHAR2 DEFAULT DBMS_SCHEDULER.DEFAULT_NOTIFICATION_BODY,
    events         =>       VARCHAR2 DEFAULT 'JOB_SUCCEEDED,JOB_FAILED,JOB_STARTED',
    --filter_condition     IN VARCHAR2 DEFAULT NULL
    );

Open in new window

0
 

Author Comment

by:toooki
ID: 35174149
Sorry this is the query I used and getting the same error:

   DBMS_SCHEDULER.ADD_JOB_EMAIL_NOTIFICATION (
    job_name       =>     'MY_LOAD_DATA_JOB',
    recipients      =>     'myemail@yahoo.com',
    events         =>     'job_started, job_succeeded');
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 35176614
you must run the pl/sql procedure from within a pl/sql block


begin
   DBMS_SCHEDULER.ADD_JOB_EMAIL_NOTIFICATION .....
end;
/
0
 

Author Comment

by:toooki
ID: 35177674
Thank you. I forgot that.

This time I get an error when I compile the above email notification code:
ORA-24098: invalid value <NULL> for EMAIL_SERVER.

How do I specify this value?

I know similar other database in which this EMAIL_SERVER value might be specified as that runs email notification. Is there any query that could let me find out the specified EMAIL_SERVER value in the other database...?
0
 

Author Comment

by:toooki
ID: 35182869
I got this syntax and tried:

BEGIN
DBMS_SCHEDULER.SET_SCHEDULER_ATTRIBUTE (
attribute => 'EMAIL_SERVER',
value => 'remotesmtp.myco.com');
END;

Than it worked.

Thank you all for all your help.
1

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

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

This article started out as an Experts-Exchange question, which then grew into a quick tip to go along with an IOUG presentation for the Collaborate confernce and then later grew again into a full blown article with expanded functionality and legacy…
Truncate is a DDL Command where as Delete is a DML Command. Both will delete data from table, but what is the difference between these below statements truncate table <table_name> ?? delete from <table_name> ?? The first command cannot be …
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.

609 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