Oracle9iだけNULLと''(空文字)の定義が変わっています。
8iから9iになった時に一度検証した事が有るんですが、それを10gでやってみたら仕様が元に戻っていて驚いた。
検証プログラム
DECLARE a VARCHAR2(8) := NULL; b CHAR(8) := NULL; BEGIN a := ''; b := ''; IF a IS NULL THEN DBMS_OUTPUT.PUT_LINE('VARCHAR2への空文字代入はNULL'); ELSE DBMS_OUTPUT.PUT_LINE('VARCHAR2への空文字代入は空文字'); END IF; IF b IS NULL THEN DBMS_OUTPUT.PUT_LINE('CHARへの空文字代入はNULL'); ELSE DBMS_OUTPUT.PUT_LINE('CHARへの空文字代入は空文字'); DBMS_OUTPUT.PUT_LINE('b=*' || b || '*'); END IF; a := REPLACE(' ',' ',''); b := REPLACE(' ',' ',''); IF a IS NULL THEN DBMS_OUTPUT.PUT_LINE('VARCHAR2へのREPLACE代入はNULL'); ELSE DBMS_OUTPUT.PUT_LINE('VARCHAR2へのREPLACE代入は空文字'); END IF; IF b IS NULL THEN DBMS_OUTPUT.PUT_LINE('CHARへのREPLACE代入はNULL'); ELSE DBMS_OUTPUT.PUT_LINE('CHARへのREPLACE代入は空文字'); DBMS_OUTPUT.PUT_LINE('b=*' || b || '*'); END IF; a := TRIM(''); b := TRIM(''); IF a IS NULL THEN DBMS_OUTPUT.PUT_LINE('VARCHAR2へのTRIM代入はNULL'); ELSE DBMS_OUTPUT.PUT_LINE('VARCHAR2へのTRIM代入は空文字'); END IF; IF b IS NULL THEN DBMS_OUTPUT.PUT_LINE('CHARへのTRIM代入はNULL'); ELSE DBMS_OUTPUT.PUT_LINE('CHARへのTRIM代入は空文字'); DBMS_OUTPUT.PUT_LINE('b=*' || b || '*'); END IF; a := NVL(NULL,''); b := NVL(NULL,''); IF a IS NULL THEN DBMS_OUTPUT.PUT_LINE('VARCHAR2へのNVL代入はNULL'); ELSE DBMS_OUTPUT.PUT_LINE('VARCHAR2へのNVL代入は空文字'); END IF; IF b IS NULL THEN DBMS_OUTPUT.PUT_LINE('CHARへのNVL代入はNULL'); ELSE DBMS_OUTPUT.PUT_LINE('CHARへのNVL代入は空文字'); DBMS_OUTPUT.PUT_LINE('b=*' || b || '*'); END IF; END;
これを各バージョンのOracleで動かすと
Oracle8i(8.1.6.0.0 HPUX)
VARCHAR2への空文字代入はNULL
CHARへの空文字代入は空文字
b=* *
VARCHAR2へのREPLACE代入はNULL
CHARへのREPLACE代入はNULL
VARCHAR2へのTRIM代入はNULL
CHARへのTRIM代入は空文字
b=* *
VARCHAR2へのNVL代入はNULL
CHARへのNVL代入は空文字
b=* *
Oracle9i (9.2.0 Linux)
VARCHAR2への空文字代入はNULL
CHARへの空文字代入は空文字
b=* *
VARCHAR2へのREPLACE代入はNULL
CHARへのREPLACE代入は空文字
b=* *
VARCHAR2へのTRIM代入はNULL
CHARへのTRIM代入は空文字
b=* *
VARCHAR2へのNVL代入はNULL
CHARへのNVL代入は空文字
b=* *
ここだけ動作が変わっておりCHARへのREPLACE代入が空文字になっている。
Oracle10g (10.2.0.1.0 WINXP)
VARCHAR2への空文字代入はNULL
CHARへの空文字代入は空文字
b=* *
VARCHAR2へのREPLACE代入はNULL
CHARへのREPLACE代入はNULL
VARCHAR2へのTRIM代入はNULL
CHARへのTRIM代入は空文字
b=* *
VARCHAR2へのNVL代入はNULL
CHARへのNVL代入は空文字
b=* *
対策
REPLACEしたりTRIMした変数をCHAR型に突っ込むときぐらいしか問題がはかっくしませんが、NULLに変換したい場合には明示的にNULLをセットするようにしましょう。