実現したかったこと

JP1やタスクスケジューラなどよりバッチ処理を行う。
バッチの内容はSQL実行。
SQLの処理結果によって終了コード（エラーコード）を返し、起動元でエラー判定を行えるようにしたい。

ファイル構成

sh　フォルダ
├ db_env.sh　　←DBの接続情報等を保持する
├ exec_sql.sh　←SQLを実行するシェル
└ sql　フォルダ
  ├ hoge.sql
  ├ 他にも色々SQLファイルを置くことができる

コード

DB接続情報のファイル

設定情報は各自の情報に変更してください。

db_env.sh

export db_user=DBユーザー
export db_pass=パスワード
export db_host=192.168.XXX.XXX:1521
export db_service=orcl

SQL実行バッチ

バッチ引数に、実行したいSQLのファイル名（例えばhoge.sql）を与えることで、該当のSQLを実行できます。
SQLの実行においては引数は指定しない想定。

exec_sql.sh

#!/bin/sh
# このファイルが置かれているディレクトリを変数に取得
SCRIPT_DIR=$(cd $(dirname $0); pwd)
SQL_FILE=$1

# set Oracle environment
. $SCRIPT_DIR/db_env.sh

if [ "$SQL_FILE" = "" ]; then
    # NO arguments
    exit 1
else
    if [[ -f $SCRIPT_DIR/sql/$SQL_FILE ]]; then
        sqlplus $db_user/$db_pass@$db_host/$db_service @$SCRIPT_DIR/sql/$SQL_FILE;
    else
        # NOT exists such file
        exit 2
    fi
fi
# return SQL Result
exit $?

SQLスクリプトのファイル

これが曲者でした。
連続するSQLの処理を行いたいし、例外やロジカルエラー（例えば処理件数が0件だったらエラーとしたい等）はキャッチしたいし、それらの処理結果を終了コードとして返したかったけど、事情があってストアド定義はできないので無名ブロックを利用することにしたらRETURNが使えなかったり、無名ブロックの外でEXITしたいけどブロック内の変数は外側で使えないし・・・

hoge.sql

-- SQLエラーが発生した場合はエラーコードを返す
WHENEVER SQLERROR EXIT sql.sqlcode;
-- 独自エラーコードを変数定義
VARIABLE V_ERROR_CD number;

DECLARE
    -- 影響件数を格納する変数
    RESULT_COUNT NUMBER;
    -- ユーザー定義例外の宣言
    USER_EXCEPTION EXCEPTION;
BEGIN
    -- oracle12c以降なら、変数宣言時に初期値をセットできます。
    -- 今回の環境が11gだったのでここで初期化しています。
    :V_ERROR_CD := 0;

    -- SQL処理（サンプル）
    DELETE FROM hoge_table
    ;
    -- 上記処理の影響件数を取得
    RESULT_COUNT := SQL%ROWCOUNT;

    -- 条件によりエラーコードを指定し、ユーザー定義例外へ
    IF RESULT_COUNT <> 0 THEN
        :V_ERROR_CD := 1;
        RAISE USER_EXCEPTION;
    END IF;

    -- 他にもこの辺にSQL処理を書けます。
EXCEPTION
    WHEN USER_EXCEPTION THEN
        -- ユーザー定義例外発生時の処理
        ROLLBACK;
    WHEN OTHERS THEN
        -- SQL例外発生時の処理
        ROLLBACK;
END;
/
-- 呼び出し元にエラーコードを返す
EXIT :V_ERROR_CD;

実行方法

JP1とかタスクスケジューラから実行って書いてたんですが、手元に該当ツールを用意できていないので、コマンド上でシェルを直接実行してます。

バッチシェルからSQLを実行して終了コードを返す