Avoiding ORA-1722 casting from VARCHAR2

From: Rich Jesse <rjoralist2_at_society.servebeer.com>
Date: Thu, 15 Dec 2011 14:09:02 -0600 (CST)
Message-ID: <62dc270f0b42263c71fdb2aaf659f230.squirrel_at_society.servebeer.com>

Hey all,

I just upgraded Grid Control from to 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
	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
	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! Rich

Received on Thu Dec 15 2011 - 14:09:02 CST

Original text of this message