Search notes:

Oracle: SYS_OP_MAP_NONNULL

sys_op_map_nonnull is an undocumented function that allows to compare two expressions even if they invovle null values.

The following example compares col1 with col2 and returns two records, the one where both, col1 and col2 are null and the one where both have the value B:

create table tq84_sys_op_map_nonnull (
       col1  varchar2(1),
       col2  varchar2(1),
       col3  number
);

insert into tq84_sys_op_map_nonnull values (null, null,  1);
insert into tq84_sys_op_map_nonnull values (null,  'A',  2);
insert into tq84_sys_op_map_nonnull values ( 'B',  'B',  3);
insert into tq84_sys_op_map_nonnull values ( 'B',  'C',  4);

select *
from
  tq84_sys_op_map_nonnull
where
  sys_op_map_nonnull(col1) = sys_op_map_nonnull(col2);
--
-- C C       COL3
-- - - ----------
--              1
-- B B          3

drop table tq84_sys_op_map_nonnull;

Github repository Oracle-Patterns, path: /SQL/functions/null_related/sys_op_map_nonnull.sql

Technically, the function returns a raw…. This raw is built from the value that is passed to the function and then appending hex(0). In case of null, it returns hex(255), without an added 0.

So, sys_op_map_nonnull(null) = sys_op_map_nonnull(null) factually compares hex(255) with hex(255), which is true.

Oracle: SYS_OP_MAP_NONNULL

See also