How do I send e-mails through Oracle DBMS_SCHEDULER?

oracle oracle11g dbms-scheduler

Ewanw · Oct 1, 2014 · Viewed 7.2k times · Source

I'm having difficulty sending email notifications through DBMS_SCHEDULER.

I've gone through instructions here on adding email notifications and here on configuring the mail server but I still don't get any e-mails sent. I'm using the same mail server settings on UTL_MAIL (to send mail using procedures) and Oracle Enterprise Manager (To get backup and availability email and they work fine.) I would rather not use UTL_MAIL instead as it doesn't offer the simplicity and flexibility I'm hoping to achieve with scheduler emails.

Below we have what server settings I've configured and how I'm trying to create a job that will send notifications.

/*Settings*/

exec  dbms_scheduler.set_scheduler_attribute('email_server','xxx');

exec  dbms_scheduler.set_scheduler_attribute('email_sender','[email protected]');

/* Dont think this is needed but added anyway, this allows UTL_MAIL to work*/

alter system set smtp_out_server = 'xxx';
begin
  dbms_network_acl_admin.create_acl (
    acl         => 'dbms_scheduler.xml',
    description => 'Allow mail to be send',
    principal   => 'SCHEMA',
    is_grant    => TRUE,
    privilege   => 'connect'
    );
    commit;
end;

begin
  dbms_network_acl_admin.add_privilege (
  acl       => 'dbms_scheduler.xml',
  principal => 'SCHEMA',
  is_grant  => TRUE,
  privilege => 'resolve'
  );
  commit;
end;

begin
  dbms_network_acl_admin.assign_acl(
  acl  => 'dbms_scheduler.xml',
  host => 'xxx'
  );
  commit;
end;

/*Create a quick, simple job*/

BEGIN
    DBMS_SCHEDULER.CREATE_JOB (
            job_name => '"SCHEMA"."EMAIL_TEST"',
            job_type => 'STORED_PROCEDURE',
            job_action => 'SCHEMA.DO_MOD',
            number_of_arguments => 0,
            start_date => NULL,
            repeat_interval => NULL,
            end_date => NULL,
            enabled => FALSE,
            auto_drop => FALSE,
            comments => '');

    DBMS_SCHEDULER.SET_ATTRIBUTE( 
             name => '"SCHEMA"."EMAIL_TEST"', 
             attribute => 'restartable', value => TRUE);


    DBMS_SCHEDULER.SET_ATTRIBUTE( 
             name => '"SCHEMA"."EMAIL_TEST"', 
             attribute => 'logging_level', value => DBMS_SCHEDULER.LOGGING_RUNS);
    DBMS_SCHEDULER.SET_ATTRIBUTE( 
             name => '"SCHEMA"."EMAIL_TEST"', 
             attribute => 'raise_events', value => '511');
    DBMS_SCHEDULER.ADD_JOB_EMAIL_NOTIFICATION (    
             job_name => '"SCHEMA"."EMAIL_TEST"', 
             recipients => 'user@xxx',
             sender => 'oracle@xxx',
             subject => 'Oracle Scheduler Job Notification - %job_owner%.%job_name%.%job_subname% %event_type%',
             body => 'Job: %job_owner%.%job_name%.%job_subname%
Event: %event_type%
Date: %event_timestamp%
Log id: %log_id%
Job class: %job_class_name%
Run count: %run_count%
Failure count: %failure_count%
Retry count: %retry_count%
Error code: %error_code
%Error message: %error_message%',
             events => 'job_started, job_broken, job_chain_stalled, job_completed, job_disabled, job_failed, job_over_max_dur, job_run_completed, job_sch_lim_reached, job_stopped, job_succeeded',
                filter_condition => NULL 
             );
    DBMS_SCHEDULER.enable(
             name => '"SCHEMA"."EMAIL_TEST"');
END;

This code all completes successfully but I get no emails. I can also see the entries in user_scheduler_notifications but receive nothing.

How do I send e-mails through Oracle DBMS_SCHEDULER?

Answer

Related questions