memo
#!/bin/bash
################################################################################
#
# runsql
#
# DESCRIPTION
# SQL*Plus を使用してのSQLファイル実行を以下の機能により支援する。
# (1) SQL*Plusの実行結果を評価する(ORA-エラーの発生有無確認など)。
# (2) SQLファイルの実行開始・完了の確認を確実に行う(ログイン失敗・途中終了の検知)。
# (3) 置換変数の定義をコマンドのオプションから行うことで汎用的なSQLファイルを作成しやすくする。
# (4) SQLファイルの実行時間を計測する。
#
# SYNTAX
# runsql [-h]
# runsql [-S|-s] {-d NAME=VALUE} [-o <logfile>] [-l <linesize>]
# <login> @<sqlfile> {<param>}
#
# OPTIONS AND PARAMETERS
# -h : Usageを表示する。
# -S : SQL*Plus を SILENT モードで実行する(デフォルト)。
# -s : SQL*Plus を 非SILENT モードで実行する。
# -d : SQL*Plus 実行時に DEFINE にて事前定義する置換変数名とその値を指定。
# (1) -d オプションは複数回指定できる。
# (2) -d NAME1=VALUE1 -d NAME2=VALUE2 は SQL*Plus 上で次の通り実行される。
# DEFINE NAME1=VALUE1
# DEFINE NAME2=VALUE2
# -o : SQL*Plus 実行時の標準出力・標準エラー出力先のファイルを指定。
# (1) デフォルトの出力先は $HOME/sqlplus.out
# (2) 実行結果の評価に出力したファイルを使用するため、本ファイルの書き込み
# に失敗した場合は実行結果を正しく評価できなくなる。
# -l : SQL*Plus 起動時の LINESIZE 定義を指定。
# (1) デフォルトは 80
# (2) この指定幅に基づいて開始・ステップ・終了のセパレータの長さが決まる。
#
# <login> : 接続ユーザ/パスワード@接続記述子を指定。
# (1) 詳細は SQL*Plus の <login> を参照(sqlplus -H)
# (2) / as sysdba の指定には対応可能。
# <sqlfile> : 実行するSQLファイルを指定。
# (1) SQL*Plusと異なり指定が必須。
# <param> : SQLファイルの引数を指定(複数指定可能)。
#
# RETURNED VALUES
# 正常終了 : 0
# 以下の条件を全て満たした場合を正常終了とする。
# (1) SQL*Plusの標準出力・標準エラー出力のファイルへの保存。
# (2) SQL*Plusの終了コードが 0
# (3) SQLファイルの最後まで実行された(途中でEXITしていない)。
# (4) SQL実行時にエラーが発生していない。
# 警告終了 : 1
# 以下の条件を全て満たした場合を警告終了とする。
# (1) SQL*Plusの標準出力・標準エラー出力のファイルへの保存。
# (2) SQL*Plusの終了コードが 0
# (3) SQLファイルの最後まで実行された(途中でEXITしていない)。
# (4) SQL実行時にエラーが発生している。
# 異常終了 : 2
# 正常終了・警告終了条件に該当しない場合は全て異常終了とする。
#
# COMMENTS
# (1) エラーはSQL*Plusの出力から次のキーワードで検索する。
# ORA- : SQL実行時の問題
# SP2- : SQL*Plusが検知した問題
# PLS- : PL/SQL実行時の問題
# O/S : OSレベルで発生した問題
# 未定義の置換変数への値入力要求(NLS_LANGに依存):
# American: Enter value for .*:
# Japanese: .*に値を入力してください:
# (2) SQLファイルの実行前に次の指定を暗黙に行っている。
# 動作を変更する場合はSQLファイルの冒頭などで変更すること。
# WHENEVER SQLERROR EXIT 1 ROLLBACK
# WHENEVER OSERROR EXIT 1 ROLLBACK
# SET SERVEROUTPUT ON FORMAT WRAPPED
# SET LINESIZE &LINESIZE
# SET PAGESIZE 50000
# (3) SQL*Plus 実行時の標準出力・標準エラー出力先のファイルはコマンド内
# では削除しない。
# (4) runsql で実行する SQLファイル内では次の記述でステップを明示する。
# @&STEP <message>
# (5) SQLの実行結果中で 'PICKUP! '(スペースまで) で開始する行は
# PICKUP MSG としてレポートに表示される。
#
# ENVIRONMENT
# (1) sqlplus にパスが通っていること。
# (2) runsql と同一ディレクトリ内に runsql.STEP.sql が配置されていること。
#
# CHANGE LOG
# 2012/08/12 (ikeyanagi) 新規作成
#
################################################################################
################################################################################
# 設定
################################################################################
USAGE="Usage: runsql [-h] [-S|-s] {-d NAME=VALUE} [-o <logfile>] [-l <linesize>] <login> @<sqlfile> {<param>}"
LINESIZE=80
SC1='#'
SC2='*'
SQLPLUSOUTFILE=$HOME/sqlplus.out
STEPSQL=$(dirname $0)/runsql.STEP.sql
ERRORREGEX="^(ORA-|SP2-|PLS-|O/S|Enter value for .*:|.*に値を入力してください:)"
PICKUPREGEX="^PICKUP! "
EXIT_SUCCESS=0
EXIT_WARNING=1
EXIT_FAILURE=2
# STEP用のSQLが見つからない(異常)
if [ ! -r $STEPSQL ]; then
echo "Required file $STEPSQL is NOT found."
exit $EXIT_FAILURE
fi
################################################################################
# 引数処理
################################################################################
if [ $# -eq 0 ]; then
echo "$USAGE" 1>&2
exit $EXIT_FAILURE
fi
# runsql用のオプションを解析
optionSilent="-S"
optionDefine=
optionSqlplusOutfile=
optionLinesize=$LINESIZE
while getopts :Ssd:l:o:h opt; do
case $opt in
S) optionSilent="-S";;
s) optionSilent="";;
d) optionDefine="$optionDefine \n DEFINE $OPTARG";;
l) optionLinesize="$OPTARG";;
o) optionSqlplusOutfile="$OPTARG";;
h) echo "$USAGE" 1>&2; exit $EXIT_FAILURE;;
*) echo "Unrecognizable option -$OPTARG is specified. Exit..." 1>&2; exit $EXIT_FAILURE;;
esac
done
shift $(($OPTIND - 1))
# SQL*Plus向けの引数を解析
sqlplusArgs=
sqlplusSqlfile=
sqlplusSqlfileArgs=
while [ $# -gt 0 ]; do
if [ -n "$sqlplusSqlfile" ]; then
sqlplusSqlfileArgs="$sqlplusSqlfileArgs $1"
elif [[ "$1" =~ ^@ ]]; then
sqlplusSqlfile="${1#@}"
else
sqlplusArgs="$sqlplusArgs $1"
fi
shift
done
################################################################################
# 実行準備
################################################################################
# SQLファイル指定がない/読み取れない場合は打ち切り
if [ -z "$sqlplusSqlfile" ]; then
echo "NO SQL file is specified. Exit..." 1>&2
exit $EXIT_FAILURE
elif [ ! -r "$sqlplusSqlfile" ]; then
echo "Canno read from $sqlplusSqlfile" 1>&2
exit $EXIT_FAILURE
fi
# SQL*Plus出力先決定
sqlplusOutfile=$SQLPLUSOUTFILE
[ -n "$optionSqlplusOutfile" ] && sqlplusOutfile="$optionSqlplusOutfile"
# SQL*Plus出力先への書き込みができない場合は打ち切り
if ! touch "$sqlplusOutfile" 2>/dev/null || [ ! -w "$sqlplusOutfile" ]; then
echo "Cannot write to $sqlplusOutfile. Exit..."
exit $EXIT_FAILURE
fi
# DEFINE
optionDefine=$(echo -e "$optionDefine")
# セパレータ生成
SL1=$(printf "%${optionLinesize}s" '' | tr ' ' "$SC1")
SL2=$(printf "%${optionLinesize}s" '' | tr ' ' "$SC2")
################################################################################
# 実行
################################################################################
startTime=$(date '+%Y/%m/%d %H:%M:%S'); startTimeEpoch=$(date '+%s')
cat << SQLPLUS | sqlplus $optionSilent $sqlplusArgs 2>&1 | tee $sqlplusOutfile
SET SQLPROMPT ''
-- 置換変数定義
DEFINE STEP=$STEPSQL
DEFINE LINESIZE=$optionLinesize
DEFINE SC1=$SC1
DEFINE SC2=$SC2
DEFINE SL1=$SL1
DEFINE SL2=$SL2
$optionDefine
-- 環境設定
WHENEVER SQLERROR EXIT 1 ROLLBACK
WHENEVER OSERROR EXIT 1 ROLLBACK
SET SERVEROUTPUT ON FORMAT WRAPPED
SET LINESIZE &LINESIZE
SET PAGESIZE 50000
-- 処理開始マーカ出力
prompt
prompt &SL1
prompt &SC1 ADMINSCRIPTS RUNSQL STARTED
prompt &SL1
SET SQLPROMPT 'SQL> '
@$sqlplusSqlfile $sqlplusSqlfileArgs
SET SQLPROMPT ''
-- 処理終了マーカ出力
prompt
prompt &SL1
prompt &SC1 ADMINSCRIPTS RUNSQL COMPLETED
prompt &SL1
EXIT 0
SQLPLUS
rcs=("${PIPESTATUS[@]}")
endTime=$(date '+%Y/%m/%d %H:%M:%S'); endTimeEpoch=$(date '+%s')
################################################################################
# 実行結果の評価
################################################################################
# SQL*Plus終了コード
rcSqlplus=${rcs[1]}
# tee終了コード
rcTee=${rcs[2]}
# SQLファイルに含まれていたステップ数
sqlfileSteps=$(egrep "^@&STEP " $sqlplusSqlfile | wc -l)
# ログ解析
if [ $rcTee -ne 0 -o ! -r $sqlplusOutfile ]; then
isCorrectlyLogged="NG (Following report maybe UNusable)"
isStarted="(undetermined)"
isCompleted="(undetermined)"
executedSteps="(undetermined)"
lastExecutedStep="(undetermined)"
detectedErrors="(undetermined)"
detectedErrorDetails=""
pickedups="(none)"
else
# SQL*Plusの出力を保存できたか
isCorrectlyLogged="OK"
# 正常にログインして開始したか(ADMINSCRIPTS RUNSQL STARTEDの出力)
isStarted="NG (FAILED)"
egrep -q "^$SC1 ADMINSCRIPTS RUNSQL STARTED" $sqlplusOutfile && isStarted="OK (STARTED)"
# 最後まで処理を完了したか(ADMINSCRIPTS RUNSQL COMPLETEDの出力)
isCompleted="NG (INCOMPLETE)"
egrep -q "^$SC1 ADMINSCRIPTS RUNSQL COMPLETED" $sqlplusOutfile && isCompleted="OK (COMPLETED)"
# 実行できたステップ数
executedSteps=$(egrep "^$SC2 STEP: " $sqlplusOutfile | wc -l)
# 最後に実行していたステップ
lastExecutedStep=$(egrep "^$SC2 STEP: " $sqlplusOutfile | tail -n 1 | cut -c 9-)
[ -z "$lastExecutedStep" ] && lastExecutedStep="(none)"
# 検知されたエラー数
detectedErrors=$(egrep "$ERRORREGEX" $sqlplusOutfile | wc -l)
if [ $detectedErrors -eq 0 ]; then
detectedErrorDetails="(NO ERRORS)"
else
detectedErrorDetails=$(echo; egrep "$ERRORREGEX" $sqlplusOutfile | sort | uniq -c | sed 's/^/ /')
fi
pickedups="(none)"
if egrep -q "$PICKUPREGEX" $sqlplusOutfile; then
pickedups=$(echo; egrep "$PICKUPREGEX" $sqlplusOutfile | sed "s/$PICKUPREGEX/ /")
fi
fi
# runsqlの終了コード決定
if [ $rcSqlplus -eq 0 -a $rcTee -eq 0 -a "$isCompleted" = "OK (COMPLETED)" ]; then
if [ $detectedErrors -eq 0 ]; then
rcRunsql=$EXIT_SUCCESS; rcRunsqlDesc=Success
else
rcRunsql=$EXIT_WARNING; rcRunsqlDesc=Warning
fi
else
rcRunsql=$EXIT_FAILURE; rcRunsqlDesc=ERROR
fi
################################################################################
# レポート出力
################################################################################
sqlplusSqlfileArgsPrintable="$(echo $sqlplusSqlfileArgs)"
[ -z "$sqlplusSqlfileArgsPrintable" ] && sqlplusSqlfileArgsPrintable="(none)"
cat <<EOR | tee -a $sqlplusOutfile 1>&2
********************************************************************************
********************************************************************************
RUNSQL REPORTS
SQLFILE : $sqlplusSqlfile
ARGS : $sqlplusSqlfileArgsPrintable
NLS_LANG : $NLS_LANG
OUTPUT : $sqlplusOutfile
SQL*Plus : $rcSqlplus
TIME STATS : $startTime - $endTime ($((endTimeEpoch - startTimeEpoch)) sec)
LOGGING : $isCorrectlyLogged
STARTED : $isStarted
COMPLETED : $isCompleted
EXECUTED/PLANNED STEPS : $executedSteps / $sqlfileSteps
LAST EXECUTED STEP : $lastExecutedStep
ERRORS : $detectedErrors $detectedErrorDetails
PICKUP MSG : $pickedups
EXIT CODE : $rcRunsql ($rcRunsqlDesc)
********************************************************************************
EOR
exit $rcRunsql
SET ECHO OFF
SET HEADING OFF
CLEAR COLUMNS
PROMPT
PROMPT &SL2
PROMPT &SC2 STEP: &1
PROMPT &SL2
HOST date '+%Y/%m/%d %H:%M:%S'
