Autonomous Database(ADB, ADW, ATP)でUTL_SMTPを活用してメールを送付する

概要

Oracle Databaseでは提供されているUTL_SMTPというPL/SQLパッケージを利用することで、データベース上で実行させるPL/SQLプログラムからメールを送付することができます。Oracle CloudのAutonomous Database(ADB)は、リリースされた時点ではUTL_SMTPの利用ができませんでしたが、2019/12中旬より利用できるようになりました。
本記事では、UTL_SMTPを利用してADBで動作させるPL/SQLプログラムからメールを送付する手順を説明します。

ADBにはAutonomous Transaction Processing(ATP) と Autonomous Data Warehouse(ADW)があります。本記事の手順に関してATPとADWで違いはありません。本記事はADWで動作確認した内容をもとにしています。

ADB上でUTL_SMTPを活用する方法に関しては、下記の記事が参考になります。これを参考にしながら進めます。
How to Send an Email using UTL_SMTP in Autonomous Database

2020年1月ごろの情報・環境を元に本記事は記載しています。

(参考)Autonomous Database(ADB, ADW, ATP)の使い方に関しては、下記の記事で、さまざまな場面での詳細な手順が紹介されています。使いはじめのユーザーを意識した内容です。実際に実施したいことが下記の記事にないか確認いただくとよいです。(2020/1 追記)
Autonomous Database ハンズオンラボ（HOL）

1. 本記事の作業の前提

本記事で紹介している内容を実施するには次のものが必要です。

• Oracle Cloud Infrastructure コンソールにアクセスでき、どこかのコンパートメント(ルート・コンパートメントも可)に対して管理権限を持っていること
• 管理権限をもっているコンパートメント上に作成済みのADBインスタンスがあること
• ADBインスタンスに対して、ADMINユーザーでSQLを実行できる環境(SQL Developerなど)があること

2. 本記事の作業の流れ

ADB上でUTL_SMTPを活用にするには、大きく2つのステップがあります。

STEP1. Oracle Cloud Infrastructure側での配信環境構築
STEP2. ADB側でのメール送付

STEP1は、Oracle Cloud InfrastructureのEmail Deliveryサービスを使うための作業です。主にOracle Cloud Infrastructureのコンソール画面から実施します。
**通常のOracle Databaseで提供されるUTL_SMTPと異なり、ADB上で実行するUTL_SMTPはOracle Cloud InfrastructureのEmail Deliveryサービスを利用する必要があります。**この制限に関しては、下記に記載があります。
Oracle Cloud Using Oracle Autonomous Data Warehouse on Shared Exadata Infrastructure:Restrictions for Database PL/SQL Packages

STEP2は、ADBへSQL Developerなどから接続して、PL/SQLプログラムを実行します。その際STEP1で設定した項目をパラメータとして指定します。

2つのステップを順番に説明していきます。

3. Oracle Cloud Infrastructure側での作業（配信環境構築）

まずは、UTL_SMTPで利用するEmail Deliveryサービスのための作業です。下記記事が参考になります。
Email Deliveryを利用した外部へのメール送信(その1　配信環境構築編) - Oracle Cloud Infrastructureアドバンスド
この記事の**「1-1.メール送信用 IAMユーザの作成」「1-2.送信許可メールアドレスの登録」**を実施します。(本記事の内容では「1-3.送信ドメイン認証の設定(SPF)」は実施しません）内容が重なりますが、本記事でも作業の流れを紹介します。

3.1. メール送信用 IAMユーザの設定

Email Deliveryを利用してメールを送信するには、IAMユーザのSMTP資格情報が必要です。既存のユーザーを利用しても、新規ユーザーを作成しても構いません。ここで紹介する画面は、既存ユーザーを利用するイメージです。
利用するユーザーは、利用するコンパートメントでmanage approved-sendersとmanage suppressionsの権限を持っている必要があります(How to Send an Email using UTL_SMTP in Autonomous Database)。本記事の例では利用するユーザーが利用するコンパートメントの管理権限を持っています。
まず利用するユーザーの設定画面を表示させます。Oracle Cloud Infrastructureのコンソール画面から「アイデンティティ」-「ユーザー」と選択します。

作成済みのユーザーのリストが表示されます。利用するユーザーのユーザー名をクリックします。

選択したユーザーの詳細情報が表示されます。画面左下の「リソース」から「SMTP資格証明」を選択します。まだEmail Deliveryサービスを利用するためのクレデンシャル（資格証明）はありません。

「SMTP資格証明の生成」をクリックして、Email Deliveryサービスを利用するための資格証明を作成します。「説明」の入力を求められますが、任意の説明文（ここの例では「mail test」としています）を入力します。「SMTP資格証明の生成」をクリックします。

すると次のような画面で「ユーザー名」「パスワード」が表示されます。このセットをメール送付のプログラムの中で利用します。

ユーザー名はocid1.user....のように始まるとても長い文字列です。パスワードはこの画面でしか確認できないため、画面を閉じる前にコピペで保存しておいてください。Copy リンクをクリックすることにより文字列をコピーでき、テキストにペーストすることが可能です。
「閉じる」をクリックして「SMTP資格証明」の作成は終了です。「SMTP資格証明」パスワードは、上記の画面でしか確認できませんが、ユーザー名はコンソール画面から、「アイデンティティ」-「ユーザー」-「ユーザーの詳細」-「SMTP資格証明」と選択して表示される画面で確認できます。下記のような画面が表示されます。ユーザー名の記載で「SMTP資格証明」のユーザー名を確認できます。

3.2. 送信許可メールアドレスの登録

次にEmail Deliveryサービスを利用してメール送信するために、メール送信を許可するメールアドレスを登録します。ADBからメールを送付する際のFROMに指定するメールアドレスは事前に登録しておく必要があります。これはリージョンとコンパートメントの組み合わせ毎に登録します。

本記事を作成している時点では、リージョンのJapan East(Tokyo)においてはEmail Deliveryサービスがリリースされていなかったので、リリースされていたUS East(Ashburh)を利用しました。

次の作業の前に、コンソール画面でEmail Deliveryサービスを利用するリージョンを選択してください。リージョンは次のステップで利用するADWインスタンスと異なるリージョンでも動作します。コンパートメントは利用するコンパートメントを選択します。

コンソールメニューから 「電子メール配信」-「電子メールの承認済み送信者」を選択します。

Email Deliveryサービスがリリースされてないリージョンを選択していた場合、ここで「現在選択されているリージョンでは、電子メール配信を使用できません。」と表示されます。

次の画面でリージョンとコンパートメントに間違いないことを確認して、「承認済送信者の作成」をクリックします。

次に表示する画面で、ADBからメール送付する際にFROMに指定するメールアドレスを入力して、「承認済送信者の作成」をクリックします。

登録中のメールアドレスの情報が表示されます。メールアドレスをクリックします。

「承認済送信者の作成」をクリックしてから、５～１０分は「作成中」であることがわかります。

作成が完了すると次のように「アクティブ」と表示されます。

ここまでの作業で、Oracle Cloud Infrastructure側での作業（配信環境構築）は完了です。次のステップのために、利用するEmail Deliveryサービスの情報を確認します。「電子メール構成」をクリックします。

次のステップで、表示されたサーバー名とポート番号を指定します。
次のステップで指定する項目をまとめておきます。

作成したSMTP資格証明のユーザー名：ocid1.user....のように始まるとても長い文字列
作成したSMTP資格証明のパスワード：資格証明の作成の際に画面で表示されたもの
メールのFROMに指定するアドレス：承認済送信者として指定したもの
Email Deliveryサービスで利用するサーバー名：電子メール構成の画面で確認したもの
ポート番号：電子メール構成の画面で確認したもの

4. ADB側での操作（メール送付）

続いてADBにADMINユーザーで接続してPL/SQLプログラムを実行して、メールを送付します。

4.1. ADMINのSMTPアクセスを許可する

ADMINユーザーで特定のホストとポートのSMTPへアクセスさせるために、access control entry (ACE)というものに追加します。DBMS_NETWORK_ACL_ADMINパッケージを利用します。次のようなPL/SQLプログラムを実行します。
ここでhostで指定している引数（例ではsmtp.us-ashburn-1.oraclecloud.comです）は、利用している環境にあわせてください。ポート番号は587を利用します。

BEGIN
  -- Allow SMTP access for user ADMIN
  dbms_network_acl_admin.append_host_ace(
    host =>'smtp.us-ashburn-1.oraclecloud.com',
    lower_port => 587,
    upper_port => 587,
    ace => xs$ace_type(
      privilege_list => xs$name_list('SMTP'),
      principal_name => 'ADMIN',
      principal_type => xs_acl.ptype_db));
END;
/ 

4.2. 電子メールを送信するPL/SQLの実行

実際にメールを送付します。次のようなPL/SQLプログラムを実行します。まずメール送付のためのプロシージャを作成します。名前をSEND_MAILとしています。下記の例で「smtp.us-ashburn-1.oraclecloud.com」としている箇所は、利用している環境にあわせてください。ポート番号は587を利用します。

CREATE OR REPLACE PROCEDURE SEND_MAIL (
  msg_to varchar2,
  msg_subject varchar2,
  msg_text varchar2 ) 
IS

  mail_conn utl_smtp.connection;
  username varchar2(1000):= '（作成したSMTP資格証明のユーザー名：ocid1.user....）';
  passwd varchar2(50):= '（作成したSMTP資格証明のパスワード）';
  msg_from varchar2(50) := '（メールのFROMに指定するアドレス）';
  mailhost VARCHAR2(50) := 'smtp.us-ashburn-1.oraclecloud.com';
 
BEGIN
  mail_conn := UTL_SMTP.open_connection(mailhost, 587);
  UTL_SMTP.starttls(mail_conn);
   
  UTL_SMTP.auth(mail_conn, username, passwd, schemes => 'PLAIN');
   
  UTL_SMTP.mail(mail_conn, msg_from);
  UTL_SMTP.rcpt(mail_conn, msg_to);
   
  UTL_SMTP.open_data(mail_conn);
  
  UTL_SMTP.write_data(mail_conn, 'Date: ' || TO_CHAR(SYSDATE, 'DD-MON-YYYY HH24:MI:SS') || UTL_TCP.crlf);
  UTL_SMTP.write_data(mail_conn, 'To: ' || msg_to || UTL_TCP.crlf);
  UTL_SMTP.write_data(mail_conn, 'From: ' || msg_from || UTL_TCP.crlf);

  UTL_SMTP.write_data(mail_conn, 'Subject:=?UTF-8?B?');
  UTL_SMTP.write_raw_data(mail_conn,
    utl_encode.base64_encode(
      utl_raw.cast_to_raw(msg_subject)
    ));
  UTL_SMTP.write_data(mail_conn, '?=' || UTL_TCP.crlf);

  UTL_SMTP.write_data(mail_conn, 'MIME-Version: 1.0' || UTL_TCP.crlf);
  UTL_SMTP.write_data(mail_conn, 'Content-Type: text/plain; charset=UTF-8' || UTL_TCP.crlf);
  UTL_SMTP.write_data(mail_conn, 'Content-Transfer-Encoding: base64' || UTL_TCP.crlf);
  UTL_SMTP.write_data(mail_conn, UTL_TCP.crlf);
  UTL_SMTP.write_raw_data(mail_conn,
    UTL_ENCODE.base64_encode(
      UTL_RAW.cast_to_raw(msg_text)
    ));

  UTL_SMTP.close_data(mail_conn);
  UTL_SMTP.quit(mail_conn);
 
EXCEPTION
  WHEN UTL_SMTP.transient_error OR UTL_SMTP.permanent_error THEN
    UTL_SMTP.quit(mail_conn);
    dbms_output.put_line(sqlerrm);
  WHEN OTHERS THEN
    UTL_SMTP.quit(mail_conn);
    dbms_output.put_line(sqlerrm);
END;
/

（実行結果）
Procedure SEND_MAILがコンパイルされました

プロシージャsend_mailを作成したら、引数を指定して実行します。引数に送信先のメールアドレス、メールサブジェクト、メール本文を指定します。

DECLARE
  mailtext varchar2(2000) := 'TEST';
BEGIN
  mailtext := 'ADB UTL_SMTPから送付したサンプルのメールです。' || UTL_TCP.crlf;
  mailtext := mailtext ||'送信されてますか？' || UTL_TCP.crlf;
  mailtext := mailtext ||'大丈夫ですか？' ;

  send_mail('（送信先のメールアドレス）', 'テストメール', mailtext);
END;
/

（実行結果）
PL/SQLプロシージャが正常に完了しました。

メールが送信されたか確認しましょう。

送信されたようです。
本章で紹介したPL/SQLプログラムは、引数に何らか間違いがあってもエラーとならないケースが考えられます。メールが送付されなかった場合は、引数を丹念に見直してください。

5. 参考

UTL_SMTPパッケージの利用に関しては、Oracle Databaseでの利用のために、さまざまな利用例をネットで参照できます。
Oracle PL/SQL 編8 - SMTP メール送信 utl_smtp、utl_raw.cast_to_raw
Oracle11gR2 Expressで本文UTF8エンコードの添付ファイルつきメール送信用ヘルパをパッケージとして作ってみた。

ADBで動作させるアプリケーションからメールを送付させる方法として、アプリケーションをAPEXで動作させる場合は、下記の動画の情報も参考になると思います。本記事の方式(UTL_SMTPを使う方式)とは異なります。
Sending Email from your Oracle APEX App on Autonomous Database