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@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.