Re: Avoiding ORA-1722 casting from VARCHAR2
- From: "Rich Jesse" <rjoralist2@xxxxxxxxxxxxxxxxxxxxx>
- To: oracle-l@xxxxxxxxxxxxx
- Date: Fri, 16 Dec 2011 13:35:42 -0600 (CST)
Hey Stephane,
> What about
>
> where value > chr(ascii('0') - 1) and value < chr(ascii('9') + 1)
> and rownum > 0
>
> in a subquery and the to_number() at the outer level? It's ugly but it
> might do the job. If, that is, no value starting with a digit contains
> something else than a digit.
> Otherwise you can try
> where length(trim(replace(value, '0123456789', '
> '))) = 0
Success! But not quite what I was expecting:
For the "length(trim(replace", I think you meant to use TRANSLATE and then
to compare the TRIM of that to NULL. But I got the same ORA-1722. And the
explain plan shows the same filter predicate.
HOWEVER, the "chr(ascii" comparisons worked! Well, not them per se, but the
"and rownum > 0" alone in the subquery does the trick. "Trick" is the key
word here, though. I wonder if stats changes on the underlying tables could
cause an explain plan that still falters.
But I'm good for now. Thanks much for your help, Stephane! And a nice
reminder for me to revisit to your Youtube channel... :)
Rich
--
//www.freelists.org/webpage/oracle-l
Other related posts:
