Avoiding ORA-1722 casting from VARCHAR2
Date: Thu, 15 Dec 2011 14:09:02 -0600 (CST)
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:
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 /*+ 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! RichReceived on Thu Dec 15 2011 - 14:09:02 CST