シェルスクリプトでsqlplusを叩く時のメモ

いつも忘れるのでメモしておく
対象のOSはsolarisなんだけど、たぶん他の*nix系OSでも似たような方法は使えると思う
こういう感じで、実際には例外処理やsyslog書き込みなどいろいろ考える点は多いと思うが

#!/bin/sh

# ORACLE_HOMEなんかの環境変数の準備
ORACLE_HOME=*******
ORACLE_SID=********
NLS_LANG=Japanese_japan.JA16EUCTILDE

# sqlplusにPATHを通しておく（好みの問題だと思うが）
PATH=$ORACLE_HOME/bin:PATH

RESULT=`(sqlplus -S scott/tiger@name )<<_EOF_
set serveroutput on
set pages 0
set lin 1000
set trims on
spool result_text.log
declare
  cursor c_tmp is 
    select tname from tab;
  tmp tab%rowtype;
begin
  # selectの内容を表示するだけの例
  FOR vRec in c_tmp LOOP
    dbms_output.putline('tname ->' || vRec.tname);
  END LOOP;
end;
/
spool off
exit
_EOF_
`
if [ `echo $RESULT | grep -i 'ora\-[0-9]' | wc -l` -eq 1 ]; then
  # エラー発生時の処理
else
  # 正常終了時の処理
fi
exit

最近、この手の処理ばっかり書いてる気がする。
pl/sql部分に変数を渡したい場合は、シェルの変数で${変数名}で渡すことになる。
つまり、v$sessionなどを見ようとするとシェル変数で展開されてv${session} → v と解釈され（sessionという変数が未定義の場合）、そんな名前のテーブルはない、というエラーに苦しむことになる。回避するには v\\$session という書き方をしておこう


windowsでこういうことをやろうとすると、

  • バッチだと、
    • ヒアドキュメントできなくてsqlだけ別ファイルで書いて＠で読ませる
    • 実行結果が変数で取れないので、spoolした結果を読む処理が面倒
  • wshだと
    • sqlplusの呼び出しよりは、adoやoo4oなどCOMで触ることになる（となると、set serveroutput onなどなどpl/sql中の出力が取れない）
    • 結局ストアド化されたファンクションを叩く方法を考えることになる（その方が意外と簡潔になったりもする）