【Oracle】おしゃれな(動的)SPOOLを

はじめに

毎回SPOOLのファイル名を手修正している人が多いので、
少しでも効率化して欲しいという想いを込めて記事にしました。

動的なspoolを目指そう。

#目次

• 誰に向けたものか
• SPOOLについて
• おしゃれなSPOOLとは？
• 例題と解説
  • 【chapter1】DB名をログに出力したい
  • 【chapter2】時間をログに出力したい
  • 【chapter3】スキーマ名をログに出力したい
  • 【chapter4】その場で思いついたものをログに出力したい
• SYS_CONTEXTについて
• おわりに

#誰に向けたものか
・Oracle使う人
・Sqlplusを使う人
・Database 運用者

#SPOOLとは
SPOOL はSELECT結果やターミナルをログに出力するためのコマンド

--SPOOL開始
SPOOL <ファイル名>

--SPOOL終了
SPOOL OFF

#おしゃれなSPOOLとは？
手修正を加えることなく動的にログファイルが出力されること
(勝手に定義しました)

#例題と解説

###【chapter1】DB名をログに出力したい
複数のDBがある環境でそれぞれのDB名をログに出力したい場合

--DB名をログに出力したい

--1.DB_NAME 列を varDB_NAME という変数として宣言する。
COLUMN DB_NAME NEW_VALUE varDB_NAME

/******************************************************
 * 構文：COLUMN <列の別名> NEW_VALUE <変数名>
 * <列の別名>と<変数名>はなんでもいい。
 ******************************************************/

--2.DB名の情報をとってきて DB_NAME 列として扱う。
SELECT SYS_CONTEXT('USERENV','DB_UNIQUE_NAME') AS DB_NAME FROM DUAL;

/******************************************************
 * SYS_CONTEXT('USERENV','DB_UNIQUE_NAME')でDB名を取得し、DB_NAME 列として扱う。
 * ↓
 * このとき1.で 変数としても宣言しているので varDB_NAMEとしても扱うことができる。
 * 
 * SYS_CONTEXT：一言でいうとセッション情報が取得できる。使い方は後述。
 ******************************************************/

--3.SPOOLする
SPOOL object_check_&&varDB_NAME\.log

/******************************************************
 * &&：変数(置換変数)を使用するときに使うもの。
 * \ ：変数の区切り位置を意味する(これがないとどこまでが変数か分からないため)。
 ******************************************************/

コードを実行すると
object_check_.logというログファイルが出力される。

--実際の実行例
SQL> COLUMN DB_NAME NEW_VALUE varDB_NAME
SQL> SELECT SYS_CONTEXT('USERENV','DB_UNIQUE_NAME') AS DB_NAME FROM DUAL;

DB_NAME
--------------------------------------------------------------------------------
PROD1

SQL> SPOOL object_check_&&varDB_NAME\.log
SQL> 
SQL> SPOOL
現在object_check_PROD1.logでスプールしています。
SQL> SPOOL OFF
SQL> 
SQL> !ls -ltr *.log
-rw-r--r-- 1 oracle oinstall  94  1月 25 00:37 object_check_PROD1.log

###【chapter2】時間をログに出力したい

時間をログに出力したい場合

COLUMN NOW NEW_VALUE varNOW
SELECT TO_CHAR(SYSDATE,'YYYYMMDDHH24MISS') NOW FROM DUAL;
SPOOL object_check_&&varNOW\.log

--出力例：object_check_20200125004643.log

/******************************************************
 * YYYYMMDDHH24MISS
 * YYYY：年 MM:月 DD:日 HH24：時間 MI：分 SS:秒
 ******************************************************/

###【chapter3】スキーマ名をログに出力したい
スキーマ名毎に実施する作業でスキーマ名をログに出力したい場合

COLUMN USER_NAME NEW_VALUE varUSER_NAME
SELECT SYS_CONTEXT('USERENV','SESSION_USER') AS USER_NAME FROM DUAL;
SPOOL object_check_&&varUSER_NAME\.log

--出力例：object_check_SYS.log

###【chapter4】その場で思いついたものをログに出力したい
これはワンライナーで
(COLUMN <○○> NEW_VALUE <○○> は不要)

SPOOL object_check_&&1\.log

--実行例
SQL> SPOOL object_check_&&1\.log
1に値を入力してください: ああああ
SQL> 
SQL> SPOOL
現在object_check_ああああ.logでスプールしています。
SQL> 
SQL> !ls -ltr *.log
-rw-r--r-- 1 oracle oinstall   0  1月 25 01:27 object_check_ああああ.log

#SYS_CONTEXTについて

セッションの情報を取得できます。
他にもたくさん種類がありますので以下の資料をご参考ください。

Oracle® Database SQL言語リファレンス 11gリリース2 (11.2)
https://docs.oracle.com/cd/E16338_01/server.112/b56299/functions184.htm

#おわりに
SPOOL 1つ取ってみても奥が深いですね。
これを意識するだけで効率化を図ることができるので是非ご利用ください。