RE: ORA-01722 error in 10g
- From: "Hallas, John (EXP N-ARM)" <john.hallas@xxxxxxxx>
- To: dreveewee@xxxxxxxxx, srinivasanram2004@xxxxxxxxx
- Date: Thu, 12 Jul 2007 10:10:54 +0100
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.