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@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of Andre van Winssen
Sent: 11 July 2007 18:22
To: srinivasanram2004@xxxxxxxxx
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@xxxxxxxxx>: 


        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. 


Other related posts: