Oracle 18c

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

create table t(col1,col2) as (
  select 0,1 from dual union all
  select 2,null from dual union all
  select null,3 from dual union all
  select null,null from dual
  );
4 rows affected

select 
  col1,col2,
  coalesce(col1+col2,col1,col2) sum_cols,
  (select sum(column_value) from table(sys.odcinumberlist(col1,col2))) sum_cols2
from t;


COL1
COL2
SUM_COLS
SUM_COLS2


0
1
1
1

2
null
2
2

null
3
3
3

null
null
null
null


create table t_5(col1,col2,col3,col4,col5) as (
  select 0,1,2,3,4 from dual union all
  select 1,2,null,4,5 from dual union all
  select null,3,4,5,6 from dual union all
  select null,null,null,null,null from dual
  );
4 rows affected

select 
  t_5.*,
  (select sum(column_value) from table(sys.odcinumberlist(col1,col2,col3,col4,col5))) sum_cols2
from t_5;


COL1
COL2
COL3
COL4
COL5
SUM_COLS2


0
1
2
3
4
10

1
2
null
4
5
12

null
3
4
5
6
18

null
null
null
null
null
null

COL1	COL2	SUM_COLS	SUM_COLS2
0	1	1	1
2	null	2	2
null	3	3	3
null	null	null	null