Re: More on ORA-01722

From: ddf <oratune_at_msn.com>
Date: Fri, 15 Mar 2013 06:50:17 -0700 (PDT)
Message-ID: <0e41dfb2-75d9-419d-860e-589e7d5788cf_at_googlegroups.com>

On Thursday, March 14, 2013 2:55:56 PM UTC-6, vsevolod afanassiev wrote:
> 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

It isn't the '1' causing your problem here, it's the 'b':

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

Table created.

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

1 row created.

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

1 row created.

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

no rows selected

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

no rows selected

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

V

---

1

SQL> If I truncate the table and reload with data which can correctly convert from character to number:

SQL> truncate table test1722;

Table truncated.

SQL>
SQL> insert all
  2 into test1722
  3 values('1')
  4 into test1722
  5 values('17')
  6 into test1722
  7 values('09')
  8 select * from dual;

3 rows created.

SQL>
SQL> select * from test1722 where v = 1;

V

---

1

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

V

---

1

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

V

---

1

SQL> All three queries work. Oracle cannot convert the 'b' to a number therefore it throws the ORA-01722 error. It isn't on the '1', it comes from having one or more values in that column that won't convert to a number.

David Fitzjarrell Received on Fri Mar 15 2013 - 14:50:17 CET