1 Reply Latest reply: Dec 18, 2012 7:19 AM by Mihael RSS

    DBMS_SCHEDULER.ADD_EMAIL_NOTIFICATION

    690951
      I have faced some problem with emails.

      SQL> select * from v$version;

      BANNER
      --------------------------------------------------------------------------------
      Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
      PL/SQL Release 11.2.0.3.0 - Production
      CORE 11.2.0.3.0 Production
      TNS for Solaris: Version 11.2.0.3.0 - Production
      NLSRTL Version 11.2.0.3.0 - Production





      Next params are set:

      BEGIN
      DBMS_SCHEDULER.set_scheduler_attribute('email_server', '192.168.100.111:25');
      END;


      JOB has been created:

      BEGIN
      SYS.DBMS_SCHEDULER.CREATE_JOB
      (
      job_name => 'SYS.TESTNOTIF'
      ,start_date => TO_TIMESTAMP_TZ('2012/12/10 10:49:12.396470 Europe/Samara','yyyy/mm/dd hh24:mi:ss.ff tzr')
      ,repeat_interval => 'FREQ=MINUTELY;INTERVAL=1'
      ,end_date => NULL
      ,job_class => 'DEFAULT_JOB_CLASS'
      ,job_type => 'PLSQL_BLOCK'
      ,job_action => 'begin
      null;
      end;'
      ,comments => NULL
      );



      Notificator has been created aslo:

      BEGIN
      DBMS_SCHEDULER.ADD_JOB_EMAIL_NOTIFICATION (
      job_name => 'testnotif',
      recipients => 'e.sirenko@odnookno.info',
      sender => 'e.sirenko@odnookno.info',
      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;

      Related table isn`t empty:

      SELECT job_name, recipient, event, filter_condition
      FROM user_scheduler_notifications;

      JOB_NAME RECIPIENT EVENT FILTER_CONDITION
      ------------------------- -------------------- ------------------- ------------------------------
      TESTNOTIF e.sirenko@odnookno.i JOB_FAILED
      nfo

      TESTNOTIF e.sirenko@odnookno.i JOB_BROKEN
      nfo

      TESTNOTIF e.sirenko@odnookno.i JOB_SCH_LIM_REACHED
      nfo

      TESTNOTIF e.sirenko@odnookno.i JOB_DISABLED
      nfo





      ==================================\
      BUT there is nothing in my mailbox (i`ve tried different ones).....


      smtp server works fine, EM sends messages with `Notification Schedule` through it.


      What is wrong? where is my mistake? should i provide password for "sender"???

      Please help!
        • 1. Re: DBMS_SCHEDULER.ADD_EMAIL_NOTIFICATION
          Mihael
          JOB has been created:

          BEGIN
          SYS.DBMS_SCHEDULER.CREATE_JOB
          (
          job_name => 'SYS.TESTNOTIF'
          ,start_date => TO_TIMESTAMP_TZ('2012/12/10 10:49:12.396470 Europe/Samara','yyyy/mm/dd hh24:mi:ss.ff tzr')
          ,repeat_interval => 'FREQ=MINUTELY;INTERVAL=1'
          ,end_date => NULL
          ,job_class => 'DEFAULT_JOB_CLASS'
          ,job_type => 'PLSQL_BLOCK'
          ,job_action => 'begin
          null;
          end;'
          ,comments => NULL
          );
          may be, your job did not run, because by default, jobs are created with "disabled" status