Dear All,
We have problem recently regarding one of our query that using to_
number function. It worked smoothly for 4-5 years until recently our client complaints that the query is returning error
ORA-01722: invalid number.
This is the SQL Script -> SELECT * FROM <table_name> WHERE to_number(b) between :param1 and :param2;
* Column b is defined as VARCHAR2(20)
First thing that come to my mind is this error is due to invalid value entered by user. Using the solutions provided by users in this forum and internet, I created SQL a function that checks whether the value is numeric or not.
So, when I run the query (+SELECT b from <table_name> WHERE is_numeric(b) = 0 AND ROWNUM < 20+) this is the result that I get.
b
-----------
251567
251568
251569
251570
251571
(Up to 11 record; value from 251567 - 251577)
Is there any limitation on the maximum value that can be converted using to_number* function?*
I have search the solutions over the internet and I accept the solution/recommendation that we should not store numeric value on varchar/char column; but I hope somebody can explain why I can't use to_number to convert the above figures to numeric, because when I execute SELECT to_number('251567') FROM DUAL it doesn't returned any error)
Thank you in advance for any help or clarification on this issue.
Edited by: user5535734 on Apr 16, 2012 10:46 PM