Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> Re: to_number question

Re: to_number question

From: Jonathan Gennick <jonathan_at_gennick.com>
Date: Wed, 14 Jul 2004 20:34:30 -0400
Message-ID: <134937051198.20040714203430@gennick.com>


Wednesday, July 14, 2004, 7:56:06 PM, Stephen.Lee_at_DTAG.Com (Stephen.Lee_at_DTAG.Com) wrote:

SLDC> But if I try something like
SLDC> select yadayada from table where 123 < {any of the above number
SLDC> conversions)

SLDC> I get ORA-01722: invalid number

Almost certainly somewhere in your column you have at least one value that fails to convert to a number. Offhand, one way that comes to mind to find that one value is to write a loop inside a PL/SQL block that goes through your table row-by-row doing the TO_NUMBER conversion. Wrap that conversion in its own block, with an exception handler, and you should be able to trap on the specific row having the non-numeric value.

Best regards,

Jonathan Gennick --- Brighten the corner where you are http://Gennick.com * 906.387.1698 * mailto:jonathan@gennick.com

Join the Oracle-article list and receive one article on Oracle technologies per month by email. To join, visit http://five.pairlist.net/mailman/listinfo/oracle-article, or send email to Oracle-article-request_at_gennick.com and include the word "subscribe" in either the subject or body.



Please see the official ORACLE-L FAQ: http://www.orafaq.com

To unsubscribe send email to: oracle-l-request_at_freelists.org put 'unsubscribe' in the subject line.
--

Archives are at http://www.freelists.org/archives/oracle-l/ FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
Received on Wed Jul 14 2004 - 19:31:21 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US