SQL> explain plan for select * from dual where rownum is infinite;

Explained.

Sou@sou749>; @plan

PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------
Plan hash value: 3968617506

----------------------------------------------------------------------------
| Id  | Operation           | Name | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |      |     1 |     2 |     2   (0)| 00:00:01 |
|   1 |  COUNT              |      |       |       |            |          |
|*  2 |   FILTER            |      |       |       |            |          |
|   3 |    TABLE ACCESS FULL| DUAL |     1 |     2 |     2   (0)| 00:00:01 |
----------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter(ROWNUM IS INFINITE)

  function 'IS NAN' (N NUMBER) RETURN BOOLEAN;
…
  function 'IS INFINITE' (N NUMBER) RETURN BOOLEAN;
…
  function 'IS NOT NAN' (N NUMBER) RETURN BOOLEAN;
…
  function 'IS NOT INFINITE' (N NUMBER) RETURN BOOLEAN;

  function 'IS NAN' (N NUMBER) RETURN BOOLEAN;
  function 'IS NAN' (F BINARY_FLOAT) RETURN BOOLEAN;
  function 'IS NAN' (D BINARY_DOUBLE) RETURN BOOLEAN;
  function 'IS INFINITE' (N NUMBER) RETURN BOOLEAN;
  function 'IS INFINITE' (F BINARY_FLOAT) RETURN BOOLEAN;
  function 'IS INFINITE' (D BINARY_DOUBLE) RETURN BOOLEAN;
  function 'IS NOT NAN' (N NUMBER) RETURN BOOLEAN;
  function 'IS NOT NAN' (F BINARY_FLOAT) RETURN BOOLEAN;
  function 'IS NOT NAN' (D BINARY_DOUBLE) RETURN BOOLEAN;
  function 'IS NOT INFINITE' (N NUMBER) RETURN BOOLEAN;
  function 'IS NOT INFINITE' (F BINARY_FLOAT) RETURN BOOLEAN;
  function 'IS NOT INFINITE' (D BINARY_DOUBLE) RETURN BOOLEAN;

SELECT * FROM (SELECT cast(utl_raw.cast_to_number('FF65') AS  binary_float) val FROM dual) WHERE val IS INFINITE;

SELECT * FROM (SELECT cast(utl_raw.cast_to_number('FF65') AS  binary_double) val FROM dual ) WHERE val IS INFINITE;

SELECT * FROM (SELECT cast(utl_raw.cast_to_number('FF65') AS  FLOAT) val FROM dual ) WHERE val IS INFINITE; -- oops

SELECT * FROM (SELECT cast(123 AS NUMBER) val FROM dual ) WHERE val IS INFINITE; -- 'IS NAN(N NUMBER) is here

SELECT * FROM (SELECT cast(utl_raw.cast_to_number('FF65') AS  binary_double) val FROM dual ) WHERE val IS INFINITE;
SELECT * FROM (SELECT utl_raw.cast_to_number('FF65') val FROM dual ) WHERE val IS INFINITE; -- oops

BEGIN
  IF utl_raw.cast_to_number('FF65') IS INFINITE THEN 
    dbms_output.put_line('isn''t it too lame?');
  END IF;
END;
/

SQL> CREATE TABLE inf_TEST AS
     SELECT utl_raw.cast_to_number('FF65') val FROM dual
     UNION ALL
     SELECT utl_raw.cast_to_number('00') FROM dual;
Table created

SQL> ANALYZE TABLE inf_test COMPUTE STATISTICS;
Table analyzed

SQL> BEGIN
    dbms_stats.gather_table_stats(USER, 'inf_test');
    END;
    /
PL/SQL procedure successfully completed

SQL> SELECT MIN(val) || '', MAX(val) || '' FROM inf_test;
MIN(VAL)||''                             MAX(VAL)||''
---------------------------------------- ----------------------------------------
-~                                       ~

SQL>

SELECT 
-- AVG(val), -- try this
-- SUM(val), --  try that
MIN(val)||'', 
MAX(val)||'' 
FROM 
(
SELECT CAST(val AS binary_double) val FROM (
 SELECT utl_raw.cast_to_number('FF65') val FROM dual
 UNION ALL 
 SELECT utl_raw.cast_to_number('00') FROM dual
)
)

SELECT 
-- AVG(val), -- try this
-- SUM(val), --  try that
MIN(val)||'', 
MAX(val)||''
FROM 
(
 SELECT BINARY_DOUBLE_INFINITY val FROM dual
 UNION ALL 
 SELECT -BINARY_DOUBLE_INFINITY FROM dual
 UNION ALL 
 SELECT BINARY_DOUBLE_NAN FROM dual
)