Skip to Main Content

Database Software

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

email notification using Oracle Job

Ed CharterJul 31 2014 — edited Aug 1 2014

Hi,

I am trying to setup a email notification job using Oracle 11gR2. Per online documnetation I have done the following setup, but the email notification job is failing. If you guys have any ideas, please assist.

Step 1:

BEGIN

  DBMS_SCHEDULER.set_scheduler_attribute('email_server', 'oracle.kotaise.com:25');

  DBMS_SCHEDULER.set_scheduler_attribute('email_sender', 'no_reply@kotaise.com');

END;

/

Step2:

BEGIN

  DBMS_SCHEDULER.create_job (

    job_name        => 'email_notification_job',

    job_type        => 'PLSQL_BLOCK',

    job_action      => 'BEGIN NULL; END;',

    start_date      => SYSTIMESTAMP,

    repeat_interval => 'freq=minutely; bysecond=0',   

    enabled         => TRUE);

END;

/

Step 3:

BEGIN

DBMS_SCHEDULER.ADD_JOB_EMAIL_NOTIFICATION (

  job_name   =>  'email_notification_job',

  recipients =>  'chartere@kotaise.com',

  sender     =>  'no_reply@kotaise.com',

  subject    =>  'Scheduler Job Notification-%job_owner%.%job_name%-%event_type%',

  body       =>   '%event_type% occurred at %event_timestamp%. %error_message%',

  events     =>  'JOB_FAILED, JOB_BROKEN, JOB_DISABLED, JOB_SCH_LIM_REACHED');

END;

/

Step 4:

I have created the following  job that executes a package which executes a package "test_package.pdata". This package is not created yet, becuase I wanted to see whether the email notification is working or not. As expected the "TEST_JOB" is failed becuase of missing package. But the email notification job "'email_notification_job'", which runs every minute, failed to send an email notification.

BEGIN

  SYS.DBMS_SCHEDULER.CREATE_JOB

    (

       job_name        => 'test_job'

      ,start_date      => SYSTIMESTAMP

      ,repeat_interval => 'freq=minutely; bysecond=0'

      ,end_date        => NULL

      ,job_class       => 'DEFAULT_JOB_CLASS'

      ,job_type        => 'PLSQL_BLOCK'

      ,job_action      => 'BEGIN test_package.pdata; END;'

      ,comments        => NULL

      ,enabled         => TRUE

    );

end;

/

I have queried the DBA_SCHEDULER_NOTIFICATIONS table, and I see that the email notification job did not started.

JOB_NAME                                      RECIPIENT                     EVENT

EMAIL_NOTIFICATION_JOB      chartere@kotaise.com      JOB_FAILED

EMAIL_NOTIFICATION_JOB      chartere@kotaise.com      JOB_BROKEN

EMAIL_NOTIFICATION_JOB      chartere@kotaise.com      JOB_SCH_LIM_REACHED

EMAIL_NOTIFICATION_JOB      chartere@kotaise.com      JOB_DISABLED

Thanks

This post has been answered by GregV on Aug 1 2014
Jump to Answer

Comments

Locked Post
New comments cannot be posted to this locked post.

Post Details

Locked on Aug 29 2014
Added on Jul 31 2014
8 comments
2,596 views