バッチファイルの中でsqlplusで何か実行して、その実行結果で処理を分岐したい場合がある。 成功したら処理を継続、エラーだったらエラーメッセージを出して終了とか。
これを実装するのに、sqlplusで実行するsqlファイルを
として、返してきた値をerrorlevelで受け取って処理を分岐したりする。 例えばこんな感じ。
まずはsqlのファイル：test01.sql
whenever sqlerror exit sql.sqlcode rollback
select 1 from dual;
exit 0
そしてこれを実行するバッチファイル：test01.bat
sqlplus scott/tiger@orcl @test01
if %errorlevel% == 0 goto :OK
:NG
echo NG:%errorlevel%
exit
:OK
echo OK
exit
処理らしい処理は何もなくて、これじゃエラーの発生しようもないのだが、このまま実行すると正常終了の0を拾って 「OK」 と出力する。
sqlファイルの中の dual を dula に変えて実行すると 「ORA-00942:表またはビューが存在しません」 の942を拾って 「NG:942」 と表示する。
もうちょっと複雑なことをした処理の結果や検索した結果を返したい場合は、変数を定義してこれに値を設定して返すのだが、このときにちょっとモヤモヤしてしまうのだ。
簡単な例として、今日の日付を指定した数で割った商を返すことを考える。
今日の日付を引数で割った結果を返すsqlのファイル：test02.sql
whenever sqlerror exit sql.sqlcode rollback
set serveroutput on
var rtn number;
declare
dno number ;
begin
dno := &1 ;
select to_number(to_char(sysdate, 'dd')) / dno
into :rtn
from dual ;
exception
when others then
dbms_output.put_line(sqlerrm);
:rtn := sqlcode ;
end ;
/
exit :rtn
正確にはpl/sqlなのだが、面倒なのでsqlで統一。
これを引数を指定して呼び出し、結果を表示するだけのバッチファイル：test02.bat
@echo off
sqlplus -s scott/tiger@orcl @test02 %1
echo result:%errorlevel%
で、何がモヤモヤするかというと、エラーの取り扱い。 エラーが発生する可能性は、
と、大きく3種に切り分けることができるのだが、このそれぞれでエラー発生時の挙動が違うのだ。
1番目のsqlの解析で発生するエラーの例が、最初のと同じ dual を dula と間違えたような場合。
実際にやってみる。
D:\test>test02 1
旧 4: dno := &1 ;
新 4: dno := 1 ;
from dula ;
*
行7でエラーが発生しました。:
ORA-06550: 行7、列8:
PL/SQL: ORA-00942: 表またはビューが存在しません。
ORA-06550: 行5、列3:
PL/SQL: SQL Statement ignored
result:6550
問題の状況が同じなので、最初の例と同じく 「ORA-00942:表またはビューが存在しません」 が発生するのだが、それはエラーの詳細として、表向き返してくるのは 「ORA-6550: エラー発生位置」 なんだよな。 詳細が分かっているのに、何故それを返さないのか。
sqlの解析で問題がなければ、解析結果のsqlを実行する。 ここで発生するのが、2番目のエラー。 例えば0による除算。
これも実際にやってみる。
D:\test>test02 0
旧 4: dno := &1 ;
新 4: dno := 0 ;
ORA-01476: 除数がゼロです。
PL/SQLプロシージャが正常に完了しました。
result:-1476
1番目が構文のエラーで最初の行によってエラーが返されるのに対して、2番目は文法上の問題は無く、発生した例外は例外ブロックで処理されて、プロシージャとしては正常終了扱い。
エラーを扱う場所が違うからか、返される値の扱いも違う。 1番目のはエラーコードがそのまま返されるのに対して、2番目はエラーコードにマイナスを付けて返すのだ。 「ORA-01476: 除数がゼロです」 の場合、エラーコード 1476 ではなくて -1476 となる。 何故こちらはマイナスなのか。
まあ、マイナスで返ってきた方がいろいろ便利な気がするので、俺としては、合わせるなら1番目の場合もマイナスで返すようにして欲しいのだが。 今回の例なんかが典型的だが、大抵の場合、返す値が
のようになっているのは自然だし扱いやすいと思う。 が、現実は逆の方向。
whenever sqlerror exit sql.sqlcode rollback
set serveroutput on
var rtn number;
declare
dno number ;
begin
dno := &1 ;
select to_number(to_char(sysdate, 'dd')) / dno
into :rtn
from dual ;
end ;
/
exit :rtn
このように、test02.sqlから例外処理ブロックを削除して実行すると、
D:\test>test02 0
旧 4: dno := &1 ;
新 4: dno := 0 ;
declare
*
行1でエラーが発生しました。:
ORA-01476: 除数がゼロです。
ORA-06512: 行5
result:1476
と、先頭行でエラー処理されて、エラーコードがそのまま正の数として返される。 しかもこの場合、 「ORA-06512: エラー発生位置」 は出力されてはいるけど、1番目と違って返されるのは本来のエラーの方。
マイナスを返す方に無理やり合わせてやろうと、 test02.sql の先頭行を
whenever sqlerror exit -1*sql.sqlcode rollback
のようにすると、演算子の後ろが無視されて、エラーコードが何であれ -1 が返ってくる。 演算子の前後を入れ替えたり、0からエラーコードを引くとか書いたりするとsqlplusのコマンドとしてのエラー。 つまりこの位置に式は書けなくて、こっちの方向は駄目ってことなのだな。
3番目の変数置換は文字通りで、整数であるべきsqlplusが返す値に、そうでない値を設定しようとすると発生する。
これまた実際にやってみる。
D:\test>test02 100
旧 4: dno := &1 ;
新 4: dno := 100 ;
PL/SQLプロシージャが正常に完了しました。
SP2-0670: 内部数値変換が失敗しました。
使用方法: { EXIT | QUIT } [ SUCCESS | FAILURE | WARNING | n |
変数 | :バインド変数 ] [ COMMIT | ROLLBACK ]
result:1
プロシージャだけを見れば、文法的にはなんの問題も無く、実行時の例外も発生せず、正常に終了している。 しかし返す値は小数値。 これをsqlplusの戻り値に設定するために整数に型変換しようとしてエラーが発生しているのだな。
特に指定しなければ、sqlplusは
を返す。 しかし、正常終了の判定はsqlplus自身が実行できたかどうかで、sqlplus上で実行したsqlの成否ではない。 なので、大抵の場合は0。 DBへのアクセスに失敗した場合でも、sqlplusの実行自体は成功しているので0。 今回はsqlplusがOSに返す値を設定しようとして失敗しているので1が返されるのだが、実はこれはレアケースなのだ。 まあ、普通はこんな使い方はしないのだが。
ま、それはそれとして。 こんな使い方をした場合、返された値だけを見たら、うまく行ったのか失敗したのか判らないんだよな。 正解が1だった場合とエラー発生を区別しようと思うと、途中で出力するメッセージまで含めて判定しなきゃいけなくなって、かなり面倒そう。
とまあ、エラーが発生する局面によって挙動が違うことにモヤモヤし、そのそれぞれの挙動でまたモヤモヤするのだが、しかし謙虚に振り返ってみれば、sqlの解析で発生するエラーはデバッグレベルだし、sqlplusの変数置換で発生するエラーは仕様上の問題だし、どっちも運用開始前に解決すべき問題なんだよな。 実行時のエラーを例外ブロックで処理した場合とエラー発生時の挙動が違うと文句を言うのではなく、そうしたエラーが起きないようにプログラムをちゃんと考えて作っとけという話なのだ。 というか、ツールの仕様を把握し、一般的に想定される範囲から外れる無茶な使い方はするなと。 ここで文句を言っている俺自身、他の人からこんな愚痴を聞かされたら、きっと そうならないように作るのもお前の仕事のうち なんて偉そうに言うんだろう。
偉そうに言われて、それでもあえてやるとしたら、今回の例なら、
ぐらいだろうか。 かぶらない負数をとりあえず -99999 として、正常終了なら0以上、何であれエラーなら負数を返すように test02.sql を変更したのが下記。
whenever sqlerror exit -99999 rollback
set serveroutput on
var rtn number;
declare
dno number ;
begin
dno := &1 ;
select trunc(to_number(to_char(sysdate, 'dd')) / dno)
into :rtn
from dual ;
exception
when others then
dbms_output.put_line(sqlerrm);
:rtn := sqlcode ;
end ;
/
exit :rtn
面倒だと言いたかったのに、例が簡単なので修正も簡単になってしまった。 このぐらいなら最初からやっておけと、自分で思ってしまうとは…。 まあ、修正の後のこれはこれで微妙な気もするが、眠くなってきたので今日はここまで。