Avoiding ORA-1722 casting from VARCHAR2

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;

OK, no problem, I'll just move the "VALUE" filter to an outer query and use
the NO_MERGE hint, right?

SELECT *
FROM
(
        SELECT /*+ no_merge */
        [insert rest of query minus the "value" filter here]
)
WHERE size_mb > 400;

Still ORA-1722.  I've been playing with this for a few hours now, trying
various explicit and implicit castings (e.g. TO_NUMBER, ROUND) in the outer
and inner queries on "VALUE", but the explain plan still shows a filter on
the "VALUE" column of the underlying table in the SYSMAN view, which flags
the error.

I'm baffled.  The only thing I can think of is that it's possibly due to the
fact that I'm attempting this on a view instead of tables, but then how do I
work around this?  Am I going to have to create a silly "TO_MY_NUMBER"
function with error checking?

TIA!

Rich

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

Other related posts: