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', '          

(assuming integer values - add whatever decimal separator you need otherwise)

HTH, SF

On 12/15/2011 09:09 PM, Rich Jesse wrote:
> Hey all,
>
> I just upgraded Grid Control from 10.2.0.3 to 10.2.0.5 and apparently
> there's now some non-numeric data in the VALUE (why use a reserved word?)
> column of the SYSMAN.MGMT$METRIC_DETAILS view. So my query below now fails
> with ORA-1722 invalid number:
>
> SELECT
> key_value2 table_name,
> ROUND (86400 * (collection_timestamp - TO_DATE ('19700101000000',
> 'YYYYMMDDHH24MISS'))) + (3600 * 5) collection_timestamp,
> mmd."VALUE" size_mb
> FROM
> SYSMAN.MGMT$METRIC_DETAILS mmd
> WHERE
> target_guid = 'some huge guid of my database'
> AND target_type = 'oracle_database'
> AND metric_name = 'SQLUDMNUM'
> AND column_label = 'My Cached Objects'
> AND collection_timestamp BETWEEN SYSDATE-(8/24) AND SYSDATE
> AND value> 400
> ORDER BY
> 1, 2;

-- 
Stephane Faroult
RoughSea Ltd <http://www.roughsea.com>
Konagora <http://www.konagora.com>
RoughSea Channel on Youtube <http://www.youtube.com/user/roughsealtd>


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