Ran into a strange problem today. Somehow the exectution plan on a 9I box was different then before and it started to trow ORA-01722: invalid number errors.... This was caused by the fact that a to_number conversion was done after a the where instead of before. The workaround is to force the database to do the conversion first. This can be done by using a DECODE statement:
DECODE ((REPLACE (TRANSLATE (TRIM (number_as_string), '0123456789', '0000000000'
), '0', NULL ) ), NULL, TO_NUMBER (TRIM (number_as_string)) ) string_as_number.
Till Next Time
Subscribe to:
Post Comments (Atom)
No comments:
Post a Comment