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

# はじめに
[こちら](https://qiita.com/500InternalServerError/items/006b6b2ed44971e99bef)の記事にあるように、従来からUTL_SMTPパッケージを使用してAutonomous Databaseからメールを送信することが可能でしたが、DBMS_CLOUD_NOTIFICATIONパッケージが追加され、より簡単にクエリの実行結果をメールで送信することができるようになったので検証してみました。

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

https://qiita.com/500InternalServerError/items/006b6b2ed44971e99bef
# 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:
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:
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 'mail@500internalservererror.ga' )
 18    );
 19  END;
 20  /

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

SQL>
```
少し待ってメールボックスを確認すると以下のようなメールが届きました。
![スクリーンショット 2023-04-17 16.25.53.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/784824/719bdb09-780c-0114-a0d0-abd4b71e309b.png)
添付ファイル「tablespace_info.csv」の内容を確認します。
```text: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:
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 'mail@500internalservererror.ga' )
 18    );
 19  END;
 20  /

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

SQL> 
```
少し待ってメールボックスを確認すると以下のようなメールが届きました。
![スクリーンショット 2023-04-17 16.27.09.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/784824/28b5dc14-9e57-4801-92cd-6f2cea272ae2.png)
添付ファイル「tablespace_info.csv」の内容を確認します。
```json: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日分のアラートログをメールで自動送信するようにしてみた](https://qiita.com/500InternalServerError/items/006b6b2ed44971e99bef)
・[DBMS_CLOUD_NOTIFICATIONパッケージ](https://docs.oracle.com/ja-jp/iaas/autonomous-database-shared/doc/autonomous-dbms-cloud-notification.html)