Re: ORA-01722 error in 10g

Ram,

Algorithms for implicit conversion are subject to change across software releases and among Oracle products. In this case Oracle's implicit conversion rule is to convert varchar to number in an expression with a number. So to speak it does a TO_NUMBER() of the value before it compares the result with a number. Obviously this fails with ORA-1722 when a non-numeric character is encountered.
If you want to be sure then use explicit conversion with TO_NUMBER(), TO_DATE(), TO_CHAR() etc

Regards,
Andre

2007/7/11, Ram Srinivasan <srinivasanram2004_at_gmail.com>:
>
>
> *
>
> create
> **table* test1 (
>
> numbertest
> varchar2(2 ));*
>
> insert
> **into* test1*
>
> values
> *('12'); *commit*;*
>
> select
> ***
>
> from
> *test1*
>
> where
> *numbertest = 12; *-- no error. output is 12* *
>
> insert
> **into* test1*
>
> values
> *('g2'); *commit*;*
>
> select
> ***
>
> from
> *test1*
>
> where
> *numbertest = 12; *-- ORA-01722: invalid number . *
>
> This *test* (in 10g) proves that *as* long *as* *the* varchar2 *or* char
> column does *not* have *a* character *in* *any* *of* *the* *rows*, ORA-01722
> *error* does *not* occur.
>
> *If* *the* column has *any* *row* containg *a* character other *than* *a*
> number, you get this ORA- 01722 *error*.
> Ram Srinivasan
> Charlottesville, VA.

--
http://www.freelists.org/webpage/oracle-l