Why SUM(null) is not 0 in Oracle?

Question

It would be appreciated explaining the internal functionality of SUM function in Oracle, when encountering null values:
The result of

select sum(null) from dual;
is null

But when a null value is in a sequence of values (like sum of a null-able column), the calculated value of null value will be 0

select sum(value) from
(
select case when mod(level , 2) = 0 then null else level end as value from dual
connect by level <= 10
)
is 25

This will be more interesting when seeing the result of

select (1 + null) from dual
is null

As any operation with null will result null (except is null operator).

==========================
Some update due to comments:

create table odd_table as select sum(null) as some_name from dual;

Will result:

create table ODD_TABLE
(
  some_name NUMBER
)

Why some_name column is of type number?

Answer 1

If you are looking for a rationale for this behaviour, then it is to be found in the ANSI SQL standards which dictate that aggregate operators ignore NULL values.

If you wanted to override that behaviour then you're free to:

Sum(Coalesce(<expression>,0))

... although it would make more sense with Sum() to ...

Coalesce(Sum(<expression>),0)

You might more meaningfully:

Avg(Coalesce(<expression>,0))

... or ...

Min(Coalesce(<expression,0))

Other ANSI aggregation quirks:

1. Count() never returns null (or negative, of course)
2. Selecting only aggregation functions without a Group By will always return a single row, even if there is no data from which to select.

So ...

Coalesce(Count(<expression>),0)

... is a waste of a good coalesce.