Re: Avoiding ORA-1722 casting from VARCHAR2
- From: Stephane Faroult <sfaroult@xxxxxxxxxxxx>
- To: rjoralist2@xxxxxxxxxxxxxxxxxxxxx
- Date: Fri, 16 Dec 2011 19:34:02 +0100
Rich,
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
(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>
--
//www.freelists.org/webpage/oracle-l
Other related posts:
