X-Received: by 10.224.17.140 with SMTP id s12mr11067159qaa.3.1363596687096; Mon, 18 Mar 2013 01:51:27 -0700 (PDT) X-Received: by 10.49.29.99 with SMTP id j3mr1137593qeh.38.1363596687032; Mon, 18 Mar 2013 01:51:27 -0700 (PDT) Path: news.cambrium.nl!textnews.cambrium.nl!feeder1.cambriumusenet.nl!feed.tweaknews.nl!209.85.216.88.MISMATCH!dd2no2250711qab.0!news-out.google.com!k8ni188qas.0!nntp.google.com!dd2no2250710qab.0!postnews.google.com!glegroupsg2000goo.googlegroups.com!not-for-mail Newsgroups: comp.databases.oracle.server Date: Mon, 18 Mar 2013 01:51:26 -0700 (PDT) In-Reply-To: Complaints-To: groups-abuse@google.com Injection-Info: glegroupsg2000goo.googlegroups.com; posting-host=217.111.83.124; posting-account=6SvW4wkAAADPPCbVx1A9ptpZGH3iVQ74 NNTP-Posting-Host: 217.111.83.124 References: User-Agent: G2/1.0 MIME-Version: 1.0 Message-ID: <28e254d2-0430-4e8f-a91f-ddd05efc9d8c@googlegroups.com> Subject: Re: More on ORA-01722 From: Kay Kanekowski Injection-Date: Mon, 18 Mar 2013 08:51:27 +0000 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable Xref: news.cambrium.nl Am Donnerstag, 14. M=E4rz 2013 21:55:56 UTC+1 schrieb vsevolod afanassiev: > SQL> select * from test1722 where v =3D 1; > ERROR: > ORA-01722: invalid number >=20 > It appears that Oracle converts query into: >=20 > SQL> select * from test1722 where to_number(v) =3D 1; > ERROR: > ORA-01722: invalid number >=20 > Why? One would expect it to do the opposite: >=20 > SQL> select * from test1722 where v =3D to_char(1); Hi, what do you expect that Oracle has to do for you ? You're asking for rows that contains the number 1. So Oracle does its best = to do the query for you. And if your data doesn't fit the error is thrown. If you expect the opposite way you expect an artifical intelligence. That i= sn't the query that you have invoked. You expect that Oracle knows that you= do not really seek number 1. But Oracle has to know that you want to look = for the character '1'. Perhaps Larry will give you a parameter like "_rewrite_query_to_what_i_want= _not_what_i_coded" in one of the next dbms versions ;-) regards Kay