More on ORA-01722

Giganews Newsgroups
Subject: More on ORA-01722
Posted by:  vsevolod afanassiev (vsevolod.afanassi…@gmail.com)
Date: Thu, 14 Mar 2013

Simple test:

SQL> create table test1722(v varchar2(20));

Table created.

SQL> insert into  test1722 values ('1');

1 row created.

SQL> insert into  test1722 values ('b');

1 row created.

SQL> select * from test1722 where v = 1;
ERROR:
ORA-01722: invalid number

It appears that Oracle converts query into:

SQL> select * from test1722 where to_number(v) = 1;
ERROR:
ORA-01722: invalid number

Why? One would expect it to do the opposite:

SQL> select * from test1722 where v = to_char(1);

V
--------------------
1

Replies