【PL/SQL】メール送信を自動化する方法(UTL_MAIL / UTL_SMTP活用)

【PL/SQL】メール送信を自動化する方法(UTL_MAIL / UTL_SMTP活用) PL/SQL

社内バッチの完了通知やエラーログの送信、問い合わせ受付の自動応答など、メール送信はPL/SQLでの自動化と相性が良い機能です。Oracleには高レベルなUTL_MAILと、より細かく制御できるUTL_SMTPという2つの標準パッケージがあり、要件に応じて使い分けられます。ここでは環境準備から、UTF-8対応のテキスト/HTML送信、添付ファイル、マルチパート、低レベル制御が必要な場合のUTL_SMTP実装まで、実務でそのまま使える形で解説します。

環境準備と前提設定(ACL・パッケージ・SMTPサーバ)

データベースから外部ネットワークへ接続できるように、ACL(アクセス制御リスト)を設定します。11g以降ではDBMS_NETWORK_ACL_ADMINを用い、SMTPホストとポートへの接続権を与えるのが前提です。合わせてUTL_MAILのインストール(DBAがutlmail.sqlprvtmail.plbを実行)と、初期化パラメータSMTP_OUT_SERVERの設定、実行スキーマへのEXECUTE権限付与を行います。

-- 例:SMTPサーバ smtp.example.com:587 への接続を許可(DBA権限で)
BEGIN
  DBMS_NETWORK_ACL_ADMIN.APPEND_HOST_ACE(
    host => 'smtp.example.com',
    lower_port => 587, upper_port => 587,
    ace => xs$ace_type(
      privilege_list => xs$name_list('connect','resolve'),
      principal_name => 'APP_USER',
      principal_type => xs_acl.ptype_db));
END;
/

-- UTL_MAILを使う際の宛先SMTP。スコープは環境に合わせる
ALTER SYSTEM SET smtp_out_server='smtp.example.com:587' SCOPE=BOTH;

-- 実行権限(スキーマに合わせて付与)
GRANT EXECUTE ON UTL_MAIL  TO APP_USER;
GRANT EXECUTE ON UTL_SMTP TO APP_USER;

プロキシ経由や認証、TLSが必要な場合は、DB側にリレー用の内部SMTPを用意するか、UTL_SMTP+Oracle WalletでSTARTTLSを構成します。まずは信頼できる中継サーバを前提に動かすと運用が安定します。

UTL_MAILでシンプルに送る(テキスト/HTML/UTF-8)

最短経路でメールを出すならUTL_MAIL.SEND/SEND_ATTACH_*が便利です。件名と本文はUTF-8で扱い、MIMEタイプにcharsetを明示します。改行は基本的にCHR(10)でも配信側がCRLFに整形しますが、気になる場合はCHR(13)||CHR(10)を使います。

CREATE OR REPLACE PROCEDURE mail_text_sample IS
BEGIN
  UTL_MAIL.SEND(
    sender     => 'noreply@example.com',
    recipients => 'user1@example.com,user2@example.com', -- カンマ区切り可
    subject    => 'バッチ完了通知',
    message    => '処理が正常終了しました。' || CHR(10) || '詳細はログを参照してください。',
    mime_type  => 'text/plain; charset=UTF-8'
  );
END;
/

HTMLメールを使うとスタイルやリンクが表現できます。XSSやメールクライアント差異に注意しつつ、シンプルなHTMLに留めるのが安全です。

CREATE OR REPLACE PROCEDURE mail_html_sample IS
  v_body CLOB :=
    '<!DOCTYPE html><html><body>'||
    '<h3>ジョブ完了</h3>'||
    '<p>集計が完了しました。<a href="https://intra.example.com/report">レポート</a>をご確認ください。</p>'||
    '</body></html>';
BEGIN
  UTL_MAIL.SEND(
    sender     => 'noreply@example.com',
    recipients => 'user@example.com',
    subject    => '集計レポート完了',
    message    => v_body,
    mime_type  => 'text/html; charset=UTF-8'
  );
END;
/

UTL_MAILで添付ファイルを付ける(CSV/テキストの例)

テキスト系の添付はSEND_ATTACH_VARCHAR2が簡単です。バイナリや大きなデータはSEND_ATTACH_RAWまたはSEND_ATTACH_CLOBを使います。

CREATE OR REPLACE PROCEDURE mail_with_csv IS
  v_csv VARCHAR2(32767) :=
    'id,name,amount' || CHR(10) ||
    '1,佐藤,1200'    || CHR(10) ||
    '2,鈴木,980';
BEGIN
  UTL_MAIL.SEND_ATTACH_VARCHAR2(
    sender        => 'noreply@example.com',
    recipients    => 'acct@example.com',
    subject       => '日次売上サマリ',
    message       => '添付のCSVをご確認ください。',
    mime_type     => 'text/plain; charset=UTF-8',
    attachment    => v_csv,
    att_inline    => FALSE,
    att_mime_type => 'text/csv; charset=UTF-8',
    att_filename  => 'sales_daily.csv'
  );
END;
/

テンプレート化して再利用できるAPIにまとめる

案件ごとに同じ書き方を繰り返さないよう、シンプルなラッパーを用意しておくと楽になります。送信元や既定の署名、デフォルト宛先などを外出しにしておくと保守性が上がります。

CREATE OR REPLACE PACKAGE mailer AS
  PROCEDURE send_text(p_to VARCHAR2, p_subj VARCHAR2, p_body CLOB, p_cc VARCHAR2 := NULL);
  PROCEDURE send_html(p_to VARCHAR2, p_subj VARCHAR2, p_html CLOB, p_cc VARCHAR2 := NULL);
END;
/
CREATE OR REPLACE PACKAGE BODY mailer AS
  g_from CONSTANT VARCHAR2(128) := 'noreply@example.com';
  PROCEDURE send_text(p_to VARCHAR2, p_subj VARCHAR2, p_body CLOB, p_cc VARCHAR2) IS
  BEGIN
    UTL_MAIL.SEND(g_from, p_to, p_cc, NULL, p_subj, p_body, 'text/plain; charset=UTF-8');
  END;
  PROCEDURE send_html(p_to VARCHAR2, p_subj VARCHAR2, p_html CLOB, p_cc VARCHAR2) IS
  BEGIN
    UTL_MAIL.SEND(g_from, p_to, p_cc, NULL, p_subj, p_html, 'text/html; charset=UTF-8');
  END;
END;
/

低レベル制御が必要なときのUTL_SMTP(ヘッダ・エンコード・マルチパート)

細かいヘッダ制御やSMTP認証、独自マルチパート構成が必要ならUTL_SMTPを使います。件名の日本語はMIMEエンコード(RFC 2047)で =?UTF-8?B?<base64>?= にするのが確実です。本文や添付のBase64はUTL_ENCODEで生成します。

CREATE OR REPLACE PROCEDURE smtp_multipart_sample IS
  conn   UTL_SMTP.connection;
  bndry  VARCHAR2(64) := '----=_Boundary_'||TO_CHAR(DBMS_RANDOM.VALUE(1e6,9e6),'FM9999990');
  subj_b VARCHAR2(4000) := '=?UTF-8?B?'||
                           UTL_RAW.CAST_TO_VARCHAR2(
                             UTL_ENCODE.BASE64_ENCODE(UTL_RAW.CAST_TO_RAW('レポート配信'))) || '?=';
  body_t VARCHAR2(32767) := 'テキスト版の本文です。'||CHR(13)||CHR(10)||'詳細はHTML版をご覧ください。';
  body_h CLOB := '<html><body><h3>レポート</h3><p>HTML本文です。</p></body></html>';
BEGIN
  conn := UTL_SMTP.OPEN_CONNECTION('smtp.example.com', 587);
  UTL_SMTP.EHLO(conn, 'dbhost.example.com');
  -- STARTTLSやAUTHが必要ならここで実施(Wallet設定が前提)
  -- UTL_SMTP.STARTTLS(conn);  -- 必要時
  -- UTL_SMTP.COMMAND(conn, 'AUTH LOGIN');
  -- UTL_SMTP.COMMAND(conn, UTL_RAW.CAST_TO_VARCHAR2(UTL_ENCODE.BASE64_ENCODE(UTL_RAW.CAST_TO_RAW('smtp_user'))));
  -- UTL_SMTP.COMMAND(conn, UTL_RAW.CAST_TO_VARCHAR2(UTL_ENCODE.BASE64_ENCODE(UTL_RAW.CAST_TO_RAW('smtp_pass'))));

  UTL_SMTP.MAIL(conn, 'noreply@example.com');
  UTL_SMTP.RCPT(conn, 'user@example.com');

  UTL_SMTP.OPEN_DATA(conn);

  UTL_SMTP.WRITE_DATA(conn, 'From: noreply@example.com' || CHR(13)||CHR(10));
  UTL_SMTP.WRITE_DATA(conn, 'To: user@example.com'       || CHR(13)||CHR(10));
  UTL_SMTP.WRITE_DATA(conn, 'Subject: '||subj_b          || CHR(13)||CHR(10));
  UTL_SMTP.WRITE_DATA(conn, 'MIME-Version: 1.0'          || CHR(13)||CHR(10));
  UTL_SMTP.WRITE_DATA(conn, 'Content-Type: multipart/alternative; boundary='||bndry||CHR(13)||CHR(10)||CHR(13)||CHR(10));

  UTL_SMTP.WRITE_DATA(conn, '--'||bndry||CHR(13)||CHR(10)||
                               'Content-Type: text/plain; charset=UTF-8'||CHR(13)||CHR(10)||
                               'Content-Transfer-Encoding: 8bit'||CHR(13)||CHR(10)||CHR(13)||CHR(10)||
                               body_t||CHR(13)||CHR(10));

  UTL_SMTP.WRITE_DATA(conn, '--'||bndry||CHR(13)||CHR(10)||
                               'Content-Type: text/html; charset=UTF-8'||CHR(13)||CHR(10)||
                               'Content-Transfer-Encoding: 8bit'||CHR(13)||CHR(10)||CHR(13)||CHR(10)||
                               DBMS_LOB.SUBSTR(body_h, 32767, 1) || CHR(13)||CHR(10));

  UTL_SMTP.WRITE_DATA(conn, '--'||bndry||'--'||CHR(13)||CHR(10));

  UTL_SMTP.CLOSE_DATA(conn);
  UTL_SMTP.QUIT(conn);
END;
/

CSV添付を付ける場合はmultipart/mixedにし、本文パートに続けて添付パートをBase64で書き込みます。大きなCLOB/BLOBはチャンク分割し、行末はCRLFで区切ります。

よくあるエラーと対処の勘所

ACL未設定や対象外ポートへの接続はORA-24247で拒否されます。SMTPサーバからの拒否はORA-29278(Permanent Error)やORA-29279(Timeout)として現れるため、EHLOの応答やAUTH可否、リレー許可を運用側に確認します。UTL_MAILで宛先が多いと一部のMTAが拒否することがあるため、RCPT TOを分けられるUTL_SMTPに切り替えると制御しやすくなります。日本語件名が文字化けする場合はUTL_SMTPを使い、件名をRFC 2047方式でBase64化すると解消します。

運用設計のポイントとベストプラクティス

送信元アドレスは実在するリプライ不可の専用アドレスに統一し、SPFやDKIMはメール基盤側で整えます。本文は監視で機械処理しやすい先頭行にステータスを入れ、詳細は添付やリンクで補完します。例外時は再送制御と抑止(サプレッション)を設け、連続失敗でアラートに切り替える運用が現実的です。開発や検証では実SMTPに出さず、社内のテストリレーや「ブラックホール」サーバを用意して誤送信を防ぎます。

まとめ

簡易通知や軽い添付までならUTL_MAILで十分に実用的です。件名やマルチパート、認証などの細かな制御が必要ならUTL_SMTPに切り替え、ヘッダとエンコードを正しく組み立てます。どちらの場合もACLとSMTP_OUT_SERVERを整えること、UTF-8とMIMEの指定を明確にすること、再利用できるラッパーを用意して例外処理とログ出力を一元化することが、安定運用への近道です。