[OCI]Autonomous Database：DBMS_CLOUD_NOTIFICATIONパッケージを使用してクエリの実行結果をメールで送信してみた

はじめに

こちらの記事にあるように、従来からUTL_SMTPパッケージを使用してAutonomous Databaseからメールを送信することが可能でしたが、DBMS_CLOUD_NOTIFICATIONパッケージが追加され、より簡単にクエリの実行結果をメールで送信することができるようになったので検証してみました。

1. 事前準備

こちらの記事を参考にして、電子メール配信サービスの準備とSMTP資格証明の作成を行います。

2. SMTP接続のためのクレデンシャルの作成

SQL*PlusからAutonomous Databaseに接続します。

[opc@tools ~]$ sqlplus admin/************@(description= (retry_count=20)(retry_delay=3)(address=(protocol=tcps)(port=1521)(host=adb.ap-tokyo-1.oraclecloud.com))(connect_data=(service_name=***********_medium.adb.oraclecloud.com))(security=(ssl_server_dn_match=yes)))

SQL*Plus: Release 21.0.0.0.0 - Production on 月 4月 17 05:40:32 2023
Version 21.9.0.0.0

Copyright (c) 1982, 2022, Oracle.  All rights reserved.

最終正常ログイン時間: 木 4月  13 2023 14:40:52 +00:00


Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.19.0.1.0
に接続されました。
SQL> 

DBMS_CLOUD.CREATE_CREDENTIALプロシージャを使用して、SMTP接続のためのクレデンシャルを作成します。
1.のSMTP資格証明の生成で取得したユーザ名とパスワードを使用します。

パラメータ	値
credential_name	作成するクレデンシャルの名前
username	SMTP資格証明の生成で取得したユーザ名
password	SMTP資格証明の生成で取得したパスワード

SQL> BEGIN
  2    DBMS_CLOUD.CREATE_CREDENTIAL(
  3      credential_name => 'DBMS_CLOUD_NOTIFICATION_CRED',
  4      username        => 'ocid1.user.oc1..aa**************************************:',
  5      password        => 'v****************c'
  6     );
  7  END;
  8  /

PL/SQLプロシージャが正常に完了しました。

SQL> 

3.DBMS_CLOUD_NOTIFICATIONパッケージを使用したメールの送信

Autonomous Databaseからクエリの結果をメールで送信するにはDBMS_CLOUD_NOTIFICATIONパッケージのSEND_DATAプロシージャを使用します。
DBMS_CLOUD_NOTIFICATION.SEND_DATAプロシージャを実行する際は、以下のようなパラメータを指定します。

パラメータ	値
provider	メール送信の場合は「email」
credential_name	作成したSMTP接続用クレデンシャルの名前
query	実行するクエリ
recipient	メールの送信先（TO）
to_cc	メールの送信先（CC）
to_bcc	メールの送信先（BCC）
subject	メールのタイトル（サブジェクト）
type	添付ファイル（検索結果）のフォーマット（csvまたはjson）
title	添付ファイルのファイル名
message	メール本文
smtp_host	使用するSMTP接続のエンドポイント
sender	送信者のメールアドレス（承認済送信者）

まず、表領域毎のサイズ（データファイルサイズの合計）を取得するクエリの実行結果をCSV形式でメールで送信してみます。

SQL> BEGIN
  2    DBMS_CLOUD_NOTIFICATION.SEND_DATA(
  3  	 provider	 => 'email',
  4  	 credential_name => 'DBMS_CLOUD_NOTIFICATION_CRED',
  5  	 query		 => 'SELECT a.tablespace_name, ROUND(SUM(b.bytes/1024/1024/1024),2) GB
  6  			         FROM dba_tablespaces a, dba_data_files b
  7  			         WHERE a.tablespace_name = b. tablespace_name
  8  			         GROUP BY a.tablespace_name',
  9  	 params 	 => json_object('recipient' value 'aaaaa.aaaaaaaa@******.com',
 10  					            'to_cc'     value 'bbbbb.bbbbbbbb@******.com',
 11  					            'to_bcc'    value 'ccccc.cccccccc@******.com',
 12  					            'subject'   value 'Tablespace Info from Autonomous Database',
 13  					            'type'      value 'csv',
 14  					            'title'     value 'tablespace_info.csv',
 15  					            'message'   value 'This is a message from Autonomous Database.',
 16  					            'smtp_host' value 'smtp.email.ap-tokyo-1.oci.oraclecloud.com',
 17  					            'sender'    value '[e-mail]' )
 18    );
 19  END;
 20  /

PL/SQLプロシージャが正常に完了しました。

SQL>

少し待ってメールボックスを確認すると以下のようなメールが届きました。

添付ファイル「tablespace_info.csv」の内容を確認します。

tablespace_info.csv

SYSTEM,1.05
SYSAUX,3.42
UNDOTBS1,1.17
DBFS_DATA,.1
DATA,1.1
SAMPLESCHEMA,200
UNDO_7,.09

クエリの実行結果が添付ファイルにCSV形式で格納されていることが確認できました。

次に、表領域毎のサイズ（データファイルサイズの合計）を取得するクエリの実行結果をJSON形式でメールで送信してみます。

SQL> BEGIN
  2    DBMS_CLOUD_NOTIFICATION.SEND_DATA(
  3  	 provider	 => 'email',
  4  	 credential_name => 'DBMS_CLOUD_NOTIFICATION_CRED',
  5  	 query		 => 'SELECT a.tablespace_name, ROUND(SUM(b.bytes/1024/1024/1024),2) GB
  6  			         FROM dba_tablespaces a, dba_data_files b
  7  			         WHERE a.tablespace_name = b. tablespace_name
  8  			         GROUP BY a.tablespace_name',
  9  	 params 	 => json_object('recipient' value 'aaaaa.aaaaaaaa@******.com',
 10  					            'to_cc'     value 'bbbbb.bbbbbbbb@******.com',
 11  					            'to_bcc'    value 'ccccc.cccccccc@******.com',
 12  					            'subject'   value 'Tablespace Info from Autonomous Database',
 13  					            'type'      value 'json',
 14  					            'title'     value 'tablespace_info.json',
 15  					            'message'   value 'This is a message from Autonomous Database.',
 16  					            'smtp_host' value 'smtp.email.ap-tokyo-1.oci.oraclecloud.com',
 17  					            'sender'    value '[e-mail]' )
 18    );
 19  END;
 20  /

PL/SQLプロシージャが正常に完了しました。

SQL> 

少し待ってメールボックスを確認すると以下のようなメールが届きました。

添付ファイル「tablespace_info.csv」の内容を確認します。

tablespace_info.json

{"TABLESPACE_NAME":"SYSTEM","GB":1.05}
{"TABLESPACE_NAME":"SYSAUX","GB":3.42}
{"TABLESPACE_NAME":"UNDOTBS1","GB":1.17}
{"TABLESPACE_NAME":"DBFS_DATA","GB":0.1}
{"TABLESPACE_NAME":"DATA","GB":1.1}
{"TABLESPACE_NAME":"SAMPLESCHEMA","GB":200}
{"TABLESPACE_NAME":"UNDO_7","GB":0.09}

クエリの実行結果が添付ファイルにJSON形式で格納されていることが確認できました。

参考情報

・[OCI]Autonomous Databaseが毎日決まった時間に1日分のアラートログをメールで自動送信するようにしてみた
・DBMS_CLOUD_NOTIFICATIONパッケージ