RE: ORA-01722 error in 10g

Or, because it is a varchar2 column enclose the characters (alpha or numeric ) in single quotes  

John

From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Andre van Winssen Sent: 11 July 2007 18:22
To: srinivasanram2004_at_gmail.com
Cc: oracle-l
Subject: 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