ablog

不器用で落着きのない技術者のメモ

SQLスクリプトを実行して成功か否かを戻り値で判定する

WHENEVER SQLERROR EXIT SQL.SQLCODE
SHUTDOWN IMMEDIATE
EXIT 0

こんな感じで良かったはず。

$ sqlplus / as sysdba @db_shutdown.sql

SQL*Plus: Release 10.2.0.4.0 - Production on Fri Dec 11 20:30:39 2009

Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.

Connected to an idle instance.

ORA-01034: ORACLE not available
ORA-27101: shared memory realm does not exist
Linux Error: 2: No such file or directory
Disconnected
$ echo $?
0

なぬ?

$ sqlplus / as sysdba 

SQL*Plus: Release 10.2.0.4.0 - Production on Fri Dec 11 20:33:04 2009

Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.

Connected to an idle instance.

SQL> shutdown immediate
ORA-01034: ORACLE not available
ORA-27101: shared memory realm does not exist
Linux Error: 2: No such file or directory
SQL> show sqlcode
sqlcode 0

なんと、sqlcode が「0」。

WHENEVER SQLERROR EXIT 1
SHUTDOWN IMMEDIATE
EXIT 0

SQL.SQLCODE を使うのをやめて 1 の決め打ちにしてみた。

$ sqlplus / as sysdba @db_shutdown.sql

SQL*Plus: Release 10.2.0.4.0 - Production on Fri Dec 11 20:34:47 2009

Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.

Connected to an idle instance.

ORA-01034: ORACLE not available
ORA-27101: shared memory realm does not exist
Linux Error: 2: No such file or directory
Disconnected
$ echo $?
1

OK!
成功したか失敗したかだけ知りたい場合は SQL.SQLCODE を使うのではなく決め打ちの数値にしたほうが良さそうだ。