Re: More on ORA-01722

From: Kay Kanekowski <kay.kanekowski_at_web.de>
Date: Mon, 18 Mar 2013 01:51:26 -0700 (PDT)
Message-ID: <28e254d2-0430-4e8f-a91f-ddd05efc9d8c_at_googlegroups.com>

Am Donnerstag, 14. März 2013 21:55:56 UTC+1 schrieb vsevolod afanassiev:
> 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);

Hi,
what do you expect that Oracle has to do for you ?

You're asking for rows that contains the number 1. So Oracle does its best to do the query for you. And if your data doesn't fit the error is thrown.

If you expect the opposite way you expect an artifical intelligence. That isn't the query that you have invoked. You expect that Oracle knows that you do not really seek number 1. But Oracle has to know that you want to look for the character '1'. Perhaps Larry will give you a parameter like "_rewrite_query_to_what_i_want_not_what_i_coded" in one of the next dbms versions ;-)

regards
Kay Received on Mon Mar 18 2013 - 09:51:26 CET