From a discussion on the news group recently. The issue was the most efficient way to compare two string fields where we wanted to over-ride the Oracle handling of NULL's, namely, a null never is 'equal' to another null.
Thus from a SQL perspective, there appeared to be two options
where a = b or ( a is null and b is null)
where nvl(a,special_char) = nvl(b,special_char)In the second case, the special_char would need to be a value chosen that we know columns 'a' and 'b' can never possibly acquire. The problem here is choosing this special character.
Jonathan Lewis suggested a workaround to this issue by using the undocumented function SYS_OP_MAP_NONNULL, which appears to return an impossible value when the passed parameter is null. Thus the predicate would simply become:
where sys_op_map_nonnull(a) = sys_op_map_nonnull(b)See the full newsgroup post here