ORA-01722がエラーメッセージマニュアルに記載されていない怪 Tweet

2014/5/12追記
Oracle11g R2以降のエラーメッセージマニュアルには、ORA-01722が記載されています。

---

さて、随分間が空いてしまいましたがしばらくはこんなベースでしょうおそらく。

先日、珍しいオラクルエラーに遭遇している方をフォローしたこともあり案外ハマる方も多いのかなぁ。と思いそんな方々の為の備忘録ということで書いておきましょう。

Oracle11gより前のリリースでも同じ結果になるのでOracleのバージョンはどうでもいいのですが、とりあえず最新ですよ。ということで。

SCOTT> select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
PL/SQL Release 11.1.0.6.0 - Production
CORE    11.1.0.6.0      Production
TNS for Linux: Version 11.1.0.6.0 - Production
NLSRTL Version 11.1.0.6.0 - Production

ある方がプロシージャの実行時に以下のようなエラーで悩んでいた。

SCOTT> exec sample1722(10);
Department Name : ACCOUNTING
BEGIN sample1722(10); END;

*
行1でエラーが発生しました。:
ORA-01722: 数値が無効です。
ORA-06512: "SCOTT.SAMPLE1722", 行6
ORA-06512: 行1

本人は自分のプログラムのどこが悪いのか、ORA-01722が何故起きているか理解できていない模様。。。
実は、ORA-01722ってエラーメッセージはシンプルなんですが、オラクルのマニュアルには記載されていないのですよ。（ず〜〜〜〜〜と昔から。。）
unix系の環境でOracleをお使いになっているのなら以下のようにして確認してもちゃんと返ってくる。しかし解決方法も理由も記載されていない。。。www

SCOTT> !oerr ora 1722
01722, 00000, "invalid number"
// *Cause:
// *Action:

以下、Oracle Database Error Messages 11g Release 1 (11.1) より引用

実はこのエラー、そのまんまのエラーなのです。
コードは簡単にしてありますが、以下の６行目のような間違いを犯していたのです。

SCOTT> l
  1  create or replace procedure sample1722(in_deptno number)
  2  is
  3  begin
  4    for dept_rec in (select * from dept where deptno = in_deptno) loop
  5      dbms_output.put_line('Department Name : '||dept_rec.dname);
  6        for emp_rec in (select * from emp where deptno = dept_rec.dname order by sal desc) loop
  7          dbms_output.put_line('===>'||emp_rec.ename||':'||to_char(emp_rec.sal));
  8        end loop;
  9    end loop;
 10* end;
SCOTT> list 6
  6*       for emp_rec in (select * from emp where deptno = dept_rec.dname order by sal desc) loop
SCOTT> 
SCOTT> 

もうお分かりですよね。emp表のdeptno列はNUMBER型、dept表のdname列はVARCHAR2型、ということでVARCHAR2=>NUMBERへの暗黙の型変換が発生し数字文字以外の文字を数値へ変換しようとしてエラーが発生しているわけです。

SCOTT> desc emp
 名前                                    NULL?    型
 ----------------------------------------- -------- ----------------------------
 EMPNO                                     NOT NULL NUMBER(4)
 ENAME                                              VARCHAR2(10)
 JOB                                                VARCHAR2(9)
 MGR                                                NUMBER(4)
 HIREDATE                                           DATE
 SAL                                                NUMBER(7,2)
 COMM                                               NUMBER(7,2)
 DEPTNO                                             NUMBER(2)

SCOTT> desc dept
 名前                                    NULL?    型
 ----------------------------------------- -------- ----------------------------
 DEPTNO                                    NOT NULL NUMBER(2)
 DNAME                                              VARCHAR2(14)
 LOC                                                VARCHAR2(13)

SCOTT> 
SCOTT> 

以下の例を見れば一目瞭然ですよね。

SCOTT> select * from emp where deptno = 'AA';
select * from emp where deptno = 'AA'
                                *
行1でエラーが発生しました。:
ORA-01722: 数値が無効です。

もっと分かり易くすると。。。

SCOTT> select * from emp where deptno = to_number('AA');
select * from emp where deptno = to_number('AA');
                                           *
行1でエラーが発生しました。:
ORA-01722: 数値が無効です。

という単純なミスだったわけです。正しく修正して実行すれば以下の通り。

SCOTT> l
  1  create or replace procedure sample1722(in_deptno number)
  2  is
  3  begin
  4    for dept_rec in (select * from dept where deptno = in_deptno) loop
  5      dbms_output.put_line('Department Name : '||dept_rec.dname);
  6        for emp_rec in (select * from emp where  deptno = dept_rec.deptno order by sal desc) loop
  7          dbms_output.put_line('===>'||emp_rec.ename||':'||to_char(emp_rec.sal));
  8        end loop;
  9    end loop;
 10* end;
SCOTT> /

プロシージャが作成されました。

SCOTT> exec sample1722(10);
Department Name : ACCOUNTING
===>KING:5000
===>CLARK:2450
===>MILLER:1300

PL/SQLプロシージャが正常に完了しました。

SCOTT> 

暗黙の型変換にはご注意を。
黙の型変換はしないように！　とコーディング規約で定めているプロジェクトがほとんどだと思うけど、目視オンリーのレビューだとこの手のはすり抜けることも多いだろうね。今回のように変換エラーになる場合ならテストで発見し易いと思うけど、はっきり現れない嫌らしい暗黙の型変換もあるからね。;)