Re: Avoiding ORA-1722 casting from VARCHAR2

> 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

--
http://www.freelists.org/webpage/oracle-l