Oracle® 非公式 SQL, PL/SQL & DBA's リファレンス

開発時に発生するエラー

ORA-01722: 数値が無効です。

ORA-01722: 〜エラーは 暗黙変換 の存在を知らずに使用している。暗黙変換のルールを誤用している。仕様やテスト段階などで気付かなかった。などの場合に発生することが多い。

仮に 例えば、以下の例では演算子からも容易にどちらの方向に暗黙変換してるかを判断できるのでエラーになったとしても、すぐに正しく対処できる。

1  +  '2'  ⇒  3
1  -  '-1' ⇒  2
1  -   -1  ⇒  -2
1  --1     ⇒  エラー (1 - -1 とは違う結果に…)
1  || '2'  ⇒  '12'

では

1 = '1' は　.... ？
...  1  =  1  （'1' を 数値に変換して比較）
... '1' = '1' （ 1  を 文字に変換して比較）

のどちらに暗黙変換されるかまで覚えているだろうか？
これは、

SELECT * FROM DUAL WHERE 1 = 'n'

を実行をしてみればよい。

SQL> SELECT * FROM DUAL WHERE 1 = 'n'
SELECT * FROM DUAL WHERE 1 = 'n'
                             *
行1でエラーが発生しました。:
ORA-01722: 数値が無効です。

'n' を数値に変換しようとしているのがわかる。 ちなみに条件の記述の順番で評価方法が変わることを心配するのは無用である 'n' = 1 としていても ORA-01722 エラーになる。これは暗黙変換における現在のルール(※1)である。

(※1) ルールには数値と文字値を比較する場合、文字データを数値に変換すると書かれている。 なお、暗黙変換のルールは変更される可能性がある。

• SQL リファレンスマニュアルより

暗黙的な変換のアルゴリズムは、ソフトウェア・リリースや Oracle 製品の変更によって
変更されることがあります。
明示的な変換を指定しておくと、その動作は将来的にも確実になります。

バインド変数使用時の ORA-01722 エラー

CREATE OR REPLACE PROCEDURE RIVUS.IMPLICIT_CONV
IS
	vSQL	VARCHAR2(2000);
	vDummy	VARCHAR2(1);
	vX	VARCHAR2(5);
BEGIN
	vSQL := 'SELECT DUMMY FROM DUAL WHERE 1 = :x';
--	vX := 1;		-- その１
--	vX := '1';		-- その２
	vX := '''1''';		-- その３
	EXECUTE IMMEDIATE vSQL INTO vDummy USING IN vX;
	DBMS_OUTPUT.PUT_LINE(vDummy);
END;
/

この例は 数値の 1 と VARCHAR2 の変数 vX を比較している悪い例といえる。しかし、vX の値が 「その１」、「その２」の場合にはエラーにならない。
「その１」では代入時に暗黙変換が実行される。
「その２」では 1 と '1'(1文字) をSQL の実行時に暗黙変換後に比較されていると思われる。(※2)
「その３」は 1 と '1' という文字（3文字) を比べている。

(※2) イベントトレースを取って '1' がバインドされていることを確認

DECODE 使用時の ORA-01722 エラー

DECODE 関数は第3パラメータによって戻り値の型を決定する(※3)が、第5パラメータで異なる型を戻しても構文エラーにならない。

SELECT DECODE(1,   1,100,   0) FROM DUAL ;
SELECT DECODE(2,   1,100,   2,NULL,   0) FROM DUAL ;
SELECT DECODE(3,   1,100,   2,NULL,   3,'300',   0) FROM DUAL ;
SELECT DECODE(4,   1,100,   2,NULL,   3,'300',   4,'XXX',   0) FROM DUAL ;
--                   ↑↑ 戻り値の型を決定するのは 100 の部分（数値）

以上のすべての例において、入力値が 1 〜 3 まではすべてエラーにならない。 しかし、

SELECT DECODE(4,   1,100,   2,NULL,   3,'300',   4,'XXX',   0) FROM DUAL ;

の例において入力値が 4 になる場合にだけに ORA-01722「実行時エラー」となる。

(※3) 同じような記述を CASE 式で記述すると入力値に関係なく

SQL> SELECT CASE 1 WHEN 1 THEN 100 WHEN 4 THEN 'XXX' END FROM DUAL;
SELECT CASE 1 WHEN 1 THEN 100 WHEN 4 THEN 'XXX' END FROM DUAL
                                          *
行1でエラーが発生しました。:
ORA-00932: データ型が一致しません: NUMBERが予想されましたがCHARです。

のように入力値に関係なくエラーとなる。 CASE 式 の方が潜在的なバグを残さない親切な構文であるといえるだろう。

---

 

関連事項

• CASE 式 ／ DECODE 関数
• CASE 式 と DECODE 関数の違い

一覧ページへ戻る

OTN (Oracle Technology Network)によるエラーメッセージによる情報だけでは、対処に困ったエラーについてのプラクティスです。
ベスト・プラクティスというわけではないので、書いてあることに固執しないで広い視野でエラー対応してください。