How do send e-mails through Oracle DBMS_SCHEDULER



/*Settings*/

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

exec  dbms_scheduler.set_scheduler_attribute('email_sender','Scheduler@domain.com');

/* 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;

No comments:

Post a Comment