Avoiding ORA-1722 casting from VARCHAR2
- From: "Rich Jesse" <rjoralist2@xxxxxxxxxxxxxxxxxxxxx>
- To: oracle-l@xxxxxxxxxxxxx
- Date: Thu, 15 Dec 2011 14:09:02 -0600 (CST)
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
--
//www.freelists.org/webpage/oracle-l
Other related posts:
