SELECT to_char(val), val - val FROM (
  SELECT utl_raw.cast_to_number('ff65') val FROM dual
)

TO_CHAR(VAL)                                VAL-VAL
---------------------------------------- ----------
~                                                 0

    dbms_output.put_line(x + 2 * y);

    x := x + 2 * y;

    dbms_output.put_line(x);
    
    dbms_output.put_line(utl_raw.cast_from_number(x));
  end;

DECLARE
  x NUMBER;
  f binary_double;
BEGIN
  f := BINARY_DOUBLE_NAN;

  IF f IS NAN THEN
    dbms_output.put_line('ook');
  END IF;


  BEGIN
    x := f; -- ORA-06502 is here

    IF x IS NAN THEN
      dbms_output.put_line('oops');
    END IF;
  EXCEPTION
    WHEN OTHERS THEN dbms_output.put_line(SQLERRM);
  END;

  x := utl_raw.cast_to_number('FF65');


  IF x IS INFINITE THEN
    dbms_output.put_line('ook');
  END IF;

  f := x;

  IF f IS INFINITE THEN
    dbms_output.put_line('ook');
  END IF;
END;
/

ook
ORA-06502: PL/SQL: numeric or value error
ook
ook

SQL> var n number
SQL> begin
  2   :n:=
  3    9.999999999999999999999999999999999999999e125
  4   +0.000000000000000000000000000000000000001e125;
  5  end;
  6  /

PL/SQL procedure successfully completed.

SQL> print n;

         N
----------
         ~

SQL> select
  2    9.999999999999999999999999999999999999999e125
  3   +0.000000000000000000000000000000000000001e125
  4  from dual
  5  /
 +0.000000000000000000000000000000000000001e125
 *
ERROR at line 3:
ORA-01426: numeric overflow

SELECT CAST('+INF' AS binary_float)  ||'' FROM dual;
SELECT cast(CAST('+INF' AS binary_float) AS NUMBER) ||'' FROM dual; --oops is here
SELECT CAST(utl_raw.cast_to_number('FF65') AS binary_float) ||'' FROM dual; 

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
)