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