シェルスクリプト と sqlplus は Oracle Database を扱うエンジニアには欠かせないですよね？
今回の記事では シェルスクリプト から SQL を実行する時のお作法(※ワイ個人の)を整理してみますやね。
彡(^)(^)
本記事は JPOUG Advent Calendar 2022 および Oracle Cloud Infrastructure Advent Calendar 2022 の参加記事となります。
JPOUG Advent Calendar 2022
https://adventar.org/calendars/7680Oracle Cloud Infrastructure Advent Calendar 2022
https://qiita.com/advent-calendar/2022/oci
1. 前提条件／おことわり
本記事に記載したお作法は個人的にまとめたもので Oracle社 からベスプラや White Paper が整理されているようなものではありません。プラットフォーム(OS)は Linux(Oracle Linux7以上) を想定しています。
2. サンプルプログラム
シェル(sqlplus_helloworld.sh)、SQL(sqlplus_helloworld.sql)、環境変数定義ファイル(AYSHIBAT_ORCL.env, ただしファイル名は任意)、パスワードの暗号化＆復号シェル(encrypt_password.sh と decrypt_password.sh)の5点構成です。crontab を複数行記述して環境変数定義ファイルを変更しつつ多環境で実行するイメージです。
#!/bin/bash
################################################################
#
# Overview: This shell outputs "Hello, world!" by the sqlplus.
# Pre-requirement1: Put sqlplus_helloworld.sql and decrypt_password.sh in the same directory.
# Pre-requirement2: Write the encrypted password by encrypt_password.sh in the env file.
# Usage: sqlplus_helloworld.sh $1
# $1 ... Environment variable file.
#
# Return Value ... 0(Success) or 1(Error)
#
################################################################
#Enable errexit(-e) and nounset(-u)
set -eu
# Set enviroment variable
. "$1"
mypath=$(dirname "${0}")
logname=$(date +"sqlplus_helloworld_sql_%Y%m%d_%H%M%S_$$.log")
logfile="${mypath}/${logname}"
# Start message.
date +"%Y-%m-%dT%H:%M:%S%:z Script start. This script is logging to ${logfile}."
date +"%Y-%m-%dT%H:%M:%S%:z Script start. This script is logging to ${logfile}." >> "${logfile}"
# Decrypt password
. "${mypath}"/decrypt_password.sh
DB_PASSWORD_ENC=$(decrypt_password "${DB_PASSWORD}")
# Run the SQL with sqlplus.
sqlplus /nolog << EOF >> "${logfile}" 2>&1 && ret=0 || ret=$?
WHENEVER SQLERROR EXIT FAILURE ROLLBACK
WHENEVER OSERROR EXIT FAILURE ROLLBACK
CONNECT ${DB_USER}/${DB_PASSWORD_ENC}@${DB_CONNECT_STR}
@${mypath}/sqlplus_helloworld.sql
EXIT SUCCESS;
EOF
# Error check
error_cnt=$(grep -Eic "^ORA-[0-9]+|SP2-[0-9]+" "${logfile}") || :
if [ "${ret}" -eq 0 ] && [ "${error_cnt}" -eq 0 ]; then
date +'%Y-%m-%dT%H:%M:%S%:z SQL completed successfully.'
date +'%Y-%m-%dT%H:%M:%S%:z SQL completed successfully.' >> "${logfile}"
exit 0
else
date +'%Y-%m-%dT%H:%M:%S%:z SQL terminated abnormally.'
date +'%Y-%m-%dT%H:%M:%S%:z SQL terminated abnormally.' >> "${logfile}"
exit 1
fi
SET ECHO ON;
SET TIME ON;
SET TIMING ON;
SET LINESIZE 32767;
SET PAGESIZE 1000;
SET TRIMSPOOL ON;
SET LONG 1000000;
SET LONGC 1000000;
SET SERVEROUTPUT ON SIZE 1000000;
EXEC DBMS_OUTPUT.PUT_LINE('Hello, World!');
export ORACLE_HOME=/u01/app/oracle/product/version/db_1
export LD_LIBRARY_PATH=/u01/app/oracle/product/version/db_1/lib
export PATH=/home/oracle/Desktop/Database_Track/coffeeshop:/home/oracle/java/jdk1.8.0_201/bin:/home/oracle/bin:/home/oracle/sqlcl/bin:/home/oracle/sqldeveloper:/home/oracle/datamodeler:/usr/local/bin:/usr/bin:/usr/local/sbin:/usr/sbin:/home/oracle/sqlcl/bin:/home/oracle/sqldeveloper:/home/oracle/bin:/home/oracle/.local/bin:/home/oracle/bin:/u01/app/oracle/product/version/db_1/bin
export NLS_LANG=American_America.AL32UTF8
export LANG=C
export DB_USER=AYSHIBAT
export DB_PASSWORD="xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx"
export DB_CONNECT_STR=ORCL
#!/bin/sh
# This script encrypt strings by /etc/machine-id.
# Set the string to be encrypted as the 1st parameter of this shell.
# To prevent the pre-encrypt strings from remaining in the history,
# it is recommended to put the strings in a separate file and run as follows.
# encrypt_password.sh `cat xxxxxxxx.txt`
# This script was referenced in the following article. Thanks!
# https://qiita.com/kazuhidet/items/122c9986ca0edd5284ff
#Encrypt Fnction
encrypt_password() {
plain_password="$1"
system_uuid=$(cat /etc/machine-id)
encrypted_password=$(echo "${plain_password}" | openssl enc -e -des -base64 -k "${system_uuid}")
echo "${encrypted_password}"
}
# Usage
echo "$(encrypt_password "$1")"
#!/bin/sh
# This script decrypt the encrypted password by /etc/machine-id.
# The machine-id is unique for each virtual machine, and only that virtual machine can correctly decrypt the password.
# Therefore, even if encrypted password is compromised, the original password cannot be decrypted.
# Store the encrypted password in a env file, environment variable, or others...
# This script was referenced in the following article. Thanks!
# https://qiita.com/kazuhidet/items/122c9986ca0edd5284ff
# Decrypt Function
decrypt_password() {
encrypted_password="$1"
system_uuid=$(cat /etc/machine-id)
plain_password=$(echo "${encrypted_password}" | openssl enc -d -des -base64 -k "${system_uuid}")
echo "${plain_password}"
}
# Usage:
# echo $(decrypt_password "$1")
3. 各ファイルの解説
まずシバン(shebang)、書いておきましょう。機動刑事やないで彡(^)(^)
#!/bin/bash
エラー時の即時終了(-e) と 未定義変数の不使用(-u) を有効化しています。
#Enable errexit(-e) and nounset(-u)
set -eu
引数について、環境定義ファイルを受け取って読み込むようにしています。
環境定義ファイルを引数で受け取るようにしているのは、その方が複数環境での実行に対応し易いからです。
ログファイル名には $$(シェル自身のプロセスID) を入れて同時実行しても同一ファイルに書き込まないようにします。
:
# Usage: sqlplus_helloworld.sh $1
# $1 ... Environment variable file.
:
# Set enviroment variable
. "$1"
mypath=$(dirname "${0}")
logname=$(date +"sqlplus_helloworld_sql_%Y%m%d_%H%M%S_$$.log")
logfile="${mypath}/${logname}"
:
環境定義ファイルは ORACLE_HOME や PATH, NLS_LANG等の環境変数を組み込んでいます。
PATHはじめ環境変数をハードコードしているのは crontab で実行する際の対策となります。
手で実行すると正常動作するのに cron では上手く行かない……はだいたい環境変数が違ってたりするので。
DB_PASSWORD には encrypt_password.sh で暗号化したパスワードを記述しておきます。
export ORACLE_HOME=/u01/app/oracle/product/version/db_1
export LD_LIBRARY_PATH=/u01/app/oracle/product/version/db_1/lib
export PATH=/home/oracle/Desktop/Database_Track/coffeeshop:/home/oracle/java/jdk1.8.0_201/bin:/home/oracle/bin:/home/oracle/sqlcl/bin:/home/oracle/sqldeveloper:/home/oracle/datamodeler:/usr/local/bin:/usr/bin:/usr/local/sbin:/usr/sbin:/home/oracle/sqlcl/bin:/home/oracle/sqldeveloper:/home/oracle/bin:/home/oracle/.local/bin:/home/oracle/bin:/u01/app/oracle/product/version/db_1/bin
export NLS_LANG=American_America.AL32UTF8
export LANG=C
export DB_USER=AYSHIBAT
export DB_PASSWORD="xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx"
export DB_CONNECT_STR=ORCL
開始時のメッセージとログファイル名を出力するようにしました。
日付の書式を ISO 8601形式(%Y-%m-%dT%H:%M:%S%:z) としています。
# Start message.
date +"%Y-%m-%dT%H:%M:%S%:z Script start. This script is logging to ${logfile}."
date +"%Y-%m-%dT%H:%M:%S%:z Script start. This script is logging to ${logfile}." >> "${logfile}"
パスワードの暗号化(難読化)処理を組み込んでいます。encrypt_password.sh で暗号化したパスワードを envファイルに記述しておきます。
パスワードはOS毎にユニークな /etc/machine-id で暗号化/復号しているので、暗号化したパスワードを正しく復号できるのはシェルを動作させるOSのみとなります。
従って envファイル が漏洩した場合でもセキュアです。
# Decrypt password
. "${mypath}"/decrypt_password.sh
DB_PASSWORD_ENC=$(decrypt_password "${DB_PASSWORD}")
sqlplus は ヒアドキュメント で実行する SQL を記述しています。終端文字列は EOF としていますが何でも良いです。
エラー出力を標準出力にマージ(2>&1)しつつ、標準出力をログファイルにリダイレクトしています。
set -e でエラー時の即時終了を有効化しているため、返り値は ret=0 || ret=$? で受け取っています。
sqlplus /nolog << EOF >> "${logfile}" 2>&1 && ret=0 || ret=$?
:
EOF
ヒアドキュメント部分について、初めに sqlplus の WHENEVER句 でエラー時にロールバックしつつエラー終了するようにしています。
この記述により CONNECT の接続エラーでエラー終了できるようになります。
CONNECT を SQL に記述しないのは、この後SQL本体で設定する SET ECHO ON での表示を防ぐためでもあります。
:
WHENEVER SQLERROR EXIT FAILURE ROLLBACK
WHENEVER OSERROR EXIT FAILURE ROLLBACK
CONNECT ${DB_USER}/${DB_PASSWORD_ENC}@${DB_CONNECT_STR}
@${mypath}/sqlplus_helloworld.sql
EXIT SUCCESS;
:
SQLファイルの解説は行コメントで。
SET ECHO ON; -- SQLの内容を標準出力に表示します。SQLファイルのみ有効でSQLを直接入力すると効果無し
SET TIME ON; -- プロンプトに時刻を表示します。
SET TIMING ON; -- SQL の経過時間を出力します。
SET LINESIZE 32767; -- SELECT結果の行長を指定します。途中改行されたくないので最大値
SET PAGESIZE 1000; -- SELECT結果の行数指定、ヘッダーを出力したくない場合は 0 に
SET TRIMSPOOL ON; -- SPOOL結果の後ろ空白を除去します。忘れると SPOOL結果のサイズが異常に。今回の SQL では効果無いですが。
SET LONG 1000000; -- CLOB列 や LONG列 の表示長を制御します。今回の SQL では効果無いですが。
SET LONGC 1000000; -- CLOB列 や LONG列 の表示長を制御します。今回の SQL では効果無いですが。
SET SERVEROUTPUT ON SIZE 1000000; -- DBMS_OUTPUT を表示できるようにします。SIZE は UNLIMITED でも OK
EXEC DBMS_OUTPUT.PUT_LINE('Hello, World!'); -- Hello, World!
エラーのチェック、sqlplus の戻り値($?) と 標準出力のエラー(ORA-xxxxx or SP2-xxxxx)カウントの両方をチェックしています。
エラーコードの数値は5桁とは限らず、SP2-は行頭でないケースもあるため正規表現は "^ORA-[0-9]+|SP2-[0-9]+" としています。
# Error check
error_cnt=$(grep -Eic "^ORA-[0-9]+|SP2-[0-9]+" "${logfile}") || :
if [ "${ret}" -eq 0 ] && [ "${error_cnt}" -eq 0 ]; then
date +'%Y-%m-%dT%H:%M:%S%:z SQL completed successfully.'
date +'%Y-%m-%dT%H:%M:%S%:z SQL completed successfully.' >> "${logfile}"
exit 0
else
date +'%Y-%m-%dT%H:%M:%S%:z SQL terminated abnormally.'
date +'%Y-%m-%dT%H:%M:%S%:z SQL terminated abnormally.' >> "${logfile}"
exit 1
fi
encrypt_password.sh と decrypt_password.sh については下記記事を参照して下さい。
本記事のスクリプトでは暗号化に /etc/machine-id を使用していますが、
プラットフォーム(OS)に依っては別のユニーク値が必要かもしれません。
シェルスクリプトの中に安全にパスワードを埋め込む
https://qiita.com/kazuhidet/items/122c9986ca0edd5284ff
4. 実行結果(正常系)
以下が正常系の実行結果となります。
[oracle@localhost helloworld]$ /home/oracle/work/helloworld/sqlplus_helloworld.sh /home/oracle/work/helloworld/AYSHIBAT_ORCL.env
2022-12-27T00:42:08-05:00 Script start. This script is logging to /home/oracle/work/helloworld/sqlplus_helloworld_sql_20221227_004208_14273.log.
2022-12-27T00:42:09-05:00 SQL completed successfully.
[oracle@localhost helloworld]$ echo $?
0
[oracle@localhost helloworld]$ cat /home/oracle/work/helloworld/sqlplus_helloworld_sql_20221227_004208_14273.log
2022-12-27T00:42:08-05:00 Script start. This script is logging to /home/oracle/work/helloworld/sqlplus_helloworld_sql_20221227_004208_14273.log.
SQL*Plus: Release 19.0.0.0.0 - Production on Tue Dec 27 00:42:08 2022
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle. All rights reserved.
SQL> SQL> SQL> Connected.
SQL> SQL> SET TIME ON;
00:42:08 SQL> SET TIMING ON;
00:42:08 SQL> SET LINESIZE 32767;
00:42:08 SQL> SET PAGESIZE 1000;
00:42:08 SQL> SET TRIMSPOOL ON;
00:42:08 SQL> SET LONG 1000000;
00:42:08 SQL> SET LONGC 1000000;
00:42:08 SQL> SET SERVEROUTPUT ON SIZE 1000000;
00:42:08 SQL> EXEC DBMS_OUTPUT.PUT_LINE('Hello, World!');
Hello, World!
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.00
00:42:08 SQL> Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
2022-12-27T00:42:09-05:00 SQL completed successfully.
[oracle@localhost helloworld]$
5. 実行結果(異常系)
以下が異常系の実行結果となります。パスワード間違い。
[oracle@localhost helloworld]$ /home/oracle/work/helloworld/sqlplus_helloworld.sh /home/oracle/work/helloworld/AYSHIBAT_ORCL.env
2022-12-27T00:45:01-05:00 Script start. This script is logging to /home/oracle/work/helloworld/sqlplus_helloworld_sql_20221227_004501_14465.log.
2022-12-27T00:45:03-05:00 SQL terminated abnormally.
[oracle@localhost helloworld]$ echo $?
1
[oracle@localhost helloworld]$ cat /home/oracle/work/helloworld/sqlplus_helloworld_sql_20221227_004501_14465.log
2022-12-27T00:45:01-05:00 Script start. This script is logging to /home/oracle/work/helloworld/sqlplus_helloworld_sql_20221227_004501_14465.log.
SQL*Plus: Release 19.0.0.0.0 - Production on Tue Dec 27 00:45:01 2022
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle. All rights reserved.
SQL> SQL> SQL> ERROR:
ORA-01017: invalid username/password; logon denied
2022-12-27T00:45:03-05:00 SQL terminated abnormally.
[oracle@localhost helloworld]$
6. まとめ
シェルスクリプトで sqlplus から SQL を実行する時のお作法をまとめてみました。
皆さんのお作法も有れば本記事や Twitter でコメント下さいね。
彡(^)(^)
7. 参考ドキュメント, 参考サイト, GitHub
SQL*Plus については下記マニュアルを参照して下さい。
SQL*Plusユーザーズ・ガイドおよびリファレンス 19c
https://docs.oracle.com/cd/F19136_01/sqpug/index.html
サンプルプログラムは GitHub に格納しておきました。
https://github.com/gonsuke777/OracleDatabase/tree/master/sqlplusHW
パスワードの暗号化(難読化)は下記記事を参考に(というかほぼコピー)させて頂きました。
シェルスクリプトの中に安全にパスワードを埋め込む
https://qiita.com/kazuhidet/items/122c9986ca0edd5284ff
下記サイトを活用して作成したシェルをチェックさせて頂きました。
ShellCheck
https://www.shellcheck.net/
8. いろいろ反映(2022/12/26-27追記＆修正)
コメント頂いたので諸々を反映しました。ありがとうございます！彡(^)(^)
(1). set -euコマンド追記で exiterr と nounset を有効化、それに伴う各種書き換え
(2). バッククォート` のコマンド実行を $() に書き換え
(3). dateコマンドで文字列も出力、シングルクォートだと変数が展開されなかったのでダブルクォートで囲ってます。
(4). パスワードの暗号化(難読化)処理を適用(decrypt_password.sh, encrypt_password.sh)、OS毎にユニークな /etc/machine-id で暗号化しているのでenvファイルが漏洩してもセキュア
(5). 日付の書式を ISO 8601形式(%Y-%m-%dT%H:%M:%S%:z) としました。
(6). ログファイル名を標準出力するようにしました。
(7). sqlplus の EXIT を SUCCESS に変更
(8). egrep -i | wc -l を grep -Eic に変更
(9). エラーコードの数値は5桁に限らない、SP2-は行頭とは限らないので正規表現の条件を修正
(10). ShellCheck(https://www.shellcheck.net/ )の指摘を可能な限り反映
(11). 同時実行を考慮してログファイル名に $$(プロセスID) を入れました。