oracleからメールを送る

こんな感じで。重い処理の最後にメール送信するようにしとくとか。

declare
  type ADDRESSES IS TABLE OF VARCHAR2(40);
  procedure SendMail(mail_from    in varchar2,
                     mail_to      in ADDRESSES,
                     mail_subject in varchar2,
                     mail_text    in varchar2) is
    smtp  utl_smtp.connection;
    MAIL_SERVER     constant varchar2(40)   := 'MAIL_SERVER';     -- メールサーバ名、または、IP
    SMTP_PORT       constant binary_integer := 25;
    CRLF            constant varchar2(4)    := UTL_TCP.CRLF;
    TAB             constant varchar2(2)    := chr(9);
    MIME_CHARSET    constant varchar2(250)  := 'ISO-2022-JP';
    CONV_CHARSET    constant varchar2(250)  := 'ISO2022-JP';
    SUBJECT_LENGTH  constant number         := 14; -- Subjectは14文字ずつ区切って送信

    len number;
    pos number;
  begin
  
    /* メール送信 */
    smtp := utl_smtp.open_connection(MAIL_SERVER, 25);
    utl_smtp.helo(smtp, MAIL_SERVER);
    utl_smtp.mail(smtp, mail_from);
  
    for idx in mail_to.FIRST .. mail_to.LAST loop
      utl_smtp.rcpt(smtp, mail_to(idx));
    end loop;
    utl_smtp.open_data(smtp);
  
    utl_smtp.write_data(smtp, 'To:');
    for idx in mail_to.FIRST .. mail_to.LAST loop
      utl_smtp.write_data(smtp, mail_to(idx));
      utl_smtp.write_data(smtp, ', ');
      utl_smtp.write_data(smtp, CRLF);
      utl_smtp.write_data(smtp, TAB);
    end loop;
    utl_smtp.write_data(smtp, CRLF);
  
    utl_smtp.write_data(smtp, 'From:' || mail_from || CRLF);
    utl_smtp.write_data(smtp, 'Subject:');
    -- Subjectはある程度区切って送信しないと文字化けする
    pos := 1;
    len := length(mail_subject);
    while (pos <= len) loop
      if (pos > 1) then 
        utl_smtp.write_data(smtp, CRLF||TAB);
      end if;
      utl_smtp.write_data(smtp, '=?'||MIME_CHARSET||'?B?');
      utl_smtp.write_raw_data(smtp,
        utl_encode.base64_encode(
          utl_raw.cast_to_raw(convert(substr(mail_subject, pos, SUBJECT_LENGTH), CONV_CHARSET))
        )
      );
      utl_smtp.write_data(smtp, '?=');
      pos := pos + SUBJECT_LENGTH;
    end loop;
    utl_smtp.write_data(smtp, CRLF);
    utl_smtp.write_data(smtp, 'MIME-Version: 1.0' || CRLF);
    utl_smtp.write_data(smtp, 'Content-Type: text/plain;' || CRLF);
    utl_smtp.write_data(smtp, TAB || 'charset: ' || MIME_CHARSET || CRLF);
    utl_smtp.write_data(smtp, 'Content-Transfer-Encoding: base64' || CRLF);
    utl_smtp.write_data(smtp, CRLF);
    utl_smtp.write_raw_data(smtp,
      utl_encode.BASE64_ENCODE(
        utl_raw.cast_to_raw(mail_text)
      ));
    utl_smtp.close_data(smtp);
    utl_smtp.quit(smtp);
  
  end SendMail;
begin
  SendMail('sender', ADDRESSES('mail@example.com'), 'Subject', 'Hello,World.');
end;
/