oracle のバージョン:11g

調査の時にその場で作るSQLでは暗黙の型変換に頼ることがよくあるが、
カラムの型等が成功するものと変わらないのに何故か失敗するテーブルがいくつかあり、困惑することがあった。

暗黙の型変換を含むSQLは、検索対象に含まれるデータによってエラーになる恐れがある。

create table m_item{
 item_cd int,
 --省略
};

select * from m_item
Where item_cd = '2121032';

原因

検索対象のカラムにキャスト不可能な値が入っている。

解説

SQL上で型が違う2つの要素を比較するとき、暗黙の型変換が行われる
例えば以下のようなSQLの場合、

Where item_cd = '2121032'

内部的に以下の変換が行われている

Where item_cd = to_number('2121032')

変換が行われるのは必ず定数側とは限らない。
公式のSQLリファレンスによると
http://docs.oracle.com/cd/E16338_01/server.112/b56299/sql_elements002.htm#i46862

数値と文字値を比較する場合、Oracleは文字データを数値に変換します。

とある。ので列側の方がcharの場合

Where to_number(item_cd) = 2121032

のような変換が起こる。
※ちなみに列側を演算しているので、item_cdのインデックスが使われないため、パフォーマンス低下を引き起こす。

この時列側の値の中に、1件でもキャスト不可能な値、
数値型へのキャストであれば、空白や数字以外の文字等があれば、暗黙のキャストは失敗する。

正規表現を利用した条件指定で該当データを探す。
数値型へのキャスト失敗の場合

where not (regexp_like(item_cd, '[0-9]'))

で条件指定したSELECT文を発行することにより、
数字以外の項目を含む行を検索可能。
・大抵手作業でinsertした不正な項目を持つ行
・謎の空白を含む行
等がヒットするかと思う。そのデータが原因。

データによって処理が失敗してしまうので、暗黙の型変換には気を付けよう。