日々常々

ふつうのプログラマがあたりまえにしたいこと。

NVLでエラー(ORA-06502: PL/SQL: 数値または値のエラー:)が発生する

環境はOracle10gXEです。
数値項目がNULLか判定し、NULLだった場合に「NULL」という文字列に置き換えて出力したい時に、単純にNVLを使うとエラーになります。以下は例。

SQL> DECLARE
  2     VNUM NUMBER;
  3  BEGIN
  4     DBMS_OUTPUT.PUT_LINE(NVL(VNUM, 'NULL'));
  5  END;
  6  /
DECLARE
*
行1でエラーが発生しました。:
ORA-06502: PL/SQL: 数値または値のエラー:
文字から数値への変換エラー。が発生しました
ORA-06512: 行4

これはNVLに渡されるパラメータの型の不一致が原因です。NVLはJavaで言うオーバーロードで実装されていて、第一引数の型で以降が決定するようです。例の場合には第一引数がNUMBER型なので、以下のFUNCTIONが選択されます。

FUNCTION NVL(NUMBER, NUMBER) RETURN NUMBER;

そんなわけで、例の場合は'NULL'をNUMBERに変換しようとしてエラーが発生しているのです。Oracleは勝手に型変換するので、意識していないと「原因不明のエラー」扱いされたりするので注意が必要です。

冒頭の要件を満たすためには、VARCHAR2のNVLを使うようにすればいけます。CASTでもTO_CHARでもこの場合は差はありません。CASTする例は以下になりますが、実際にはTO_CHAR関数を使ったほうがよさそうです。*1

DECLARE
	VNUM NUMBER;
BEGIN
	DBMS_OUTPUT.PUT_LINE(NVL(CAST(VNUM AS VARCHAR2), 'NULL'));
END;

NVL関数における暗黙の型変換の例

別にNVLに限った事ではなく、複数の型に対応するFUNCTIONなら同じ感じだと思います。
型の異なるのを入れたとしても、変換可能ならばエラーにはなりません。下記の例では、NVLの第一引数にCHARを使っているものについては、内部的ではNVL(VARCHAR2, VARCHAR2)が使われている模様です。第一引数と第二引数がVARCHAR2に暗黙的に型変換されてFUNCTIONを呼び出し、結果のVARCHAR2を出力しています。結果をvCHARに格納しているところ(※2)ではVARCHAR2からCHARへの変換が行われます。
また、下記例(※1)では第一引数にNUMBER、第二引数にCHAR型のパラメータを渡していますが、vCHARがNULLなのでNUMBERへの変換が可能なため、エラーとはなっていません。vCHARに数値に変換出来ない何らかの値を格納すればエラーとなります。'a'はエラーですが'1'はエラーとなりません。

DECLARE
    vCHAR CHAR(10);
    vCHAR2 VARCHAR2(10) := 'ABC';
    vNUM NUMBER(10) := 1;
BEGIN
    vCHAR := NULL;
    DBMS_OUTPUT.PUT_LINE('@' || vCHAR || '@');
    DBMS_OUTPUT.PUT_LINE('@' || NVL(vCHAR, vCHAR2) || '@');
    DBMS_OUTPUT.PUT_LINE('@' || NVL(vCHAR, vNUM) || '@');
    DBMS_OUTPUT.PUT_LINE('@' || NVL(vNUM, vCHAR) || '@');  --※1
    vCHAR := NVL(vCHAR, vCHAR2);
    DBMS_OUTPUT.PUT_LINE('@' || vCHAR || '@');  --※2
END;
/
@@
@ABC@
@1@
@1@
@ABC       @

*1:Oracleのリファレンスに同じく数値項目にNVLを使って文字列を返す例が書かれていました。http://otndnld.oracle.co.jp/document/products/oracle10g/102/doc_cd/server.102/B19201-02/functions.html#62085