Re: More on ORA-01722

From: joel garry <joel-garry_at_home.com>
Date: Thu, 14 Mar 2013 14:50:11 -0700 (PDT)
Message-ID: <fe996901-ae66-4922-99b3-e0bd384c460a_at_8g2000pbm.googlegroups.com>

On Mar 14, 1:55 pm, vsevolod afanassiev
<vsevolod.afanass..._at_gmail.com> 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

"During SELECT FROM operations, Oracle converts the data from the column to the type of the target variable." In other words, your v=1 makes it convert the character column to numeric.

http://docs.oracle.com/cd/E11882_01/server.112/e26088/sql_elements002.htm#SQLRF51046

jg

--
_at_home.com is bogus.
http://www.computerworlduk.com/news/applications/3435161/unit4-chief-oracle-sap-bring-it-on/

Received on Thu Mar 14 2013 - 22:50:11 CET