PL/SQL カーソルバグ

職場では、例によって総合開発演習が続いているのだが、実際にPL/SQLでプログラム組んで単体テストしてる中でいろいろエラーやバグに遭遇したので、備忘録としてまとめておく。

なんだかカーソル絡みのバグに出会いがち。

一応、カーソルとはなんぞやという話をざっくりすると、表（テーブル）の中から検索条件にあった行（レコード）だけを１行ずつ取得してくれる機能のことだす。

例えば、こんなテーブルがあったとして・・・

id	name	age	flag
1	指原	27	1
2	横山	27	0
3	峯岸	28	0
4	川栄	25	1
5	渡辺	26	1

flagが1になっている行だけからnameとageを取り出して処理したいと思ったら

CURSOR c_akb IS
SELECT name, age
FROM t_akb
WHERE flag = 1;

こんな感じでカーソルを定義してやると、カーソルは初め指原の行をとって来てくれて、次に川栄、渡辺と１行ずつ中身を変えていく。あとは該当するデータがないので空の状態。

これだけだとなんの有り難みも感じないと思うが、例えば、上のテーブルでflagが1の人の名前と年齢を、下のgraduatedテーブルに登録したいとする。

	name	age
1	前田	29
2	篠田	34

そしたら、さっき定義したカーソルを使って

FOR r_akb IN c_akb
LOOP
  INSERT INTO t_graduated (name, age)
  VALUES (r_akb.name, r_akb.age)
END LOOP;

というコードを組んでやるわけだな。すると、t_akbの中にflag=1のデータが存在する限り自動で繰り返し処理をして、t_graduatedの方に名前と年齢を新規登録してくれる。テーブル内のメンバーが48人だとか100人になったとしても大丈夫！

このカーソルループこそが、データベース特化型であるPLSQL言語の肝と言ってもいい。

前置きが長くなりすぎた。

とにかく、今日このカーソルを使って組んでたコードを、簡略化して再現するとこんな感じ。

CURSOR c_invoices IS
SELECT invoice_num, amount, invoice_date
FROM ap_invoice_if
WHERE 
NVL(TO_DATE(pi_month_from, 'YYYYMM'), invoice_date -1)<= invoice_date AND
invoice_date < NVL(ADD_MONTHS(TO_DATE(pi_month_to, 'YYYYMM'),1), invoice_date +1);

ごちゃごちゃしてるが、大事なのはWHERE句以降だけだ。pi_month_fromとpi_month_toは処理対象の請求書日付(invoice_date)の範囲を指定するためのパラメータだす。要は、pi_month_fromに’202001’、pi_month_toに’202010’を引数として渡せば、検索条件は
2020/1/1 <= 請求書日付 < 2020/11/1
になって、今年の1月から10月の請求書データをカーソルが拾ってくれるということ。

ここにコーディング的な間違いはない、はず。だってスクリプト上では動いたし。

が、テストデータを用意して、OracleのEBS上でコンカレント実行すると、複数件データが処理されるはずが、処理対象データがないというルートを通って終了してしまう。システムエラーも起きてない。

？？？

これがなかなか妙だ。真っ先に思いつくのは、パラメータの文字列（202001とか）がうまく渡せてないとかなんだが、上記のコードは、NVL関数を使って、どちらかに値が入っていない（NULL）の状態でも動くように設計している。ちなみにどちらもNULLだと検索条件は、
請求書日付-1 <= 請求書日付 < 請求書日付 +1
になって、全件処理されることになるっす。だから、処理件数0で終わるってことはパラメータがNULLになってるわけじゃないんだよな。

じゃあ、何が入ってるんだろ？ってことで、デバッグのつもりで、プログラムの最後に一文追加してみた。

FND_FILE.PUT_LINE(FND_FILE.LOG, pi_month_from||pi_month_to);

ただパラメータの中身をログ出力して確認するためだけの文。

すると、何故か今度は想定どおりに指定範囲のデータが処理されて、デバッグ文の部分も202001 202010と表示されている。

？？？

あれ、動いたぞ？さっきは何か間違えていたのかしらと、デバッグ文を消して再トライ。するとまた処理対象なしで終了。

その後、何度試しても、デバッグ文をつけると機能し、コメントアウトすると機能しない。これ何なん？こーれなーんなん？（by浜ちゃん）

結論、明確な理由は不明だが、臭いというか、原因に絡んでるかもと思う点はある。

１、パラメータ(pi_month_from, pi_month_end)はカーソル定義の部分にしか使っていない。かつ、カーソル定義はプロシージャの定義部分で行っている。

つまり、パラメータはプロシージャの実行部分で直接登場していない。単なる仮説でしかないが、コンカレントでパラメータに入れた値は、実行部で呼び出されて初めて有効化する、みたいな仕様があるとすれば、デバッグ文を差し込んだ時だけ機能したのもなんとなく説明がつく気はする。
ただ、さっきも触れたようにNULLとして扱われてるわけでもないし、エラーになるわけでもないから、一体どんな処理が行われていたのかという疑問は解決できないが…

２、パラメータを直接検索条件に使っている。

なんでダメなの？というは全く説明できないが、結果論として、件のカーソル定義の前にパラメータの値を変数に入れてやったらこの問題は解決できた。こんな感じ。

lv_month_from  VARCHAR2(6) := pi_month_from;
lv_month_to      VARCHAR2(6) := pi_month_to

CURSOR c_invoices IS
SELECT invoice_num, amount, invoice_date
FROM ap_invoice_if
WHERE
NVL(TO_DATE(lv_month_from, 'YYYYMM'), invoice_date -1)<= invoice_date AND
invoice_date < NVL(ADD_MONTHS(TO_DATE(lv_month_to, 'YYYYMM'),1), invoice_date +1);

カーソル定義文内で直接パラメータを使わず、値を一度違う変数に移し変えてやっただけだが、これでうまく機能するようになった。理由は分からん。前に組んだプログラムは直接パラメータで定義してもうまく動いてたからな。やはり、１と２の状態の複合でなんらかの内部変換ミスが起こるのかもしれん。

というわけでまぁ、まだちょっとモヤモヤした部分は残るが、今日の結論は

INパラメータ値は一度ローカル変数で受け取ってから使った方が安全！！

ということでした。お疲れっした。