The clue should be in the execution plan. I'm sure 9i is using an Index on test1, where as the 10g is doing a full table scan. Naveen ________________________________ From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of David Boyd Sent: Tuesday, July 03, 2007 3:25 PM To: Elliott, Patrick; oracle-l@xxxxxxxxxxxxx Subject: RE: data type conversion in 10g We have mix of number values and char values in the column. Once I delete all char values in the column, 'where test1<88' works in 10g, too. David ________________________________ Subject: RE: data type conversion in 10g Date: Tue, 3 Jul 2007 13:49:08 -0500 From: patrick.elliott@xxxxxxxxxxxxx To: davidb158@xxxxxxxxxxx; oracle-l@xxxxxxxxxxxxx I am extremely curious. What is the data that is causing this? Pat ________________________________ From: David Boyd [mailto:davidb158@xxxxxxxxxxx] Sent: Tuesday, July 03, 2007 1:40 PM To: Elliott, Patrick; oracle-l@xxxxxxxxxxxxx Subject: RE: data type conversion in 10g First thanks for all of you who replyed to my mail. Our NLS parameters are the same in both 9i (188.8.131.52) and 10g. As I replyed to Tom's mail, the data in 9i and 10g are the exactly same. David ________________________________ Subject: RE: data type conversion in 10g Date: Tue, 3 Jul 2007 13:24:58 -0500 From: patrick.elliott@xxxxxxxxxxxxx To: davidb158@xxxxxxxxxxx; oracle-l@xxxxxxxxxxxxx The problem isn't with 10g, it is with your NLS parameters and/or the data. The error comes from the values in the column not from your 88. The 88 converts fine to a number, but the values in your column do not. Any non-numeric values in the column will cause this. ________________________________ See what you're getting into...before you go there. Check it out! <http://newlivehotmail.com> The enclosed information is STRICTLY CONFIDENTIAL and is intended for the use of the addressee only. University Hospitals and its affiliates disclaim any responsibility for unauthorized disclosure of this information to anyone other than the addressee.<BR><BR>Federal and Ohio law protect patient medical information, including psychiatric_disorders, (H.I.V) test results, A.I.Ds-related conditions, alcohol, and/or drug_dependence or abuse disclosed in this email. Federal regulation (42 CFR Part 2) and Ohio Revised Code section 5122.31 and 3701.243 prohibit disclosure of this information without the specific written consent of the person to whom it pertains, or as otherwise permitted by law.