Oracle SYS_OP_MAP_NONNULL function

This article presents undocumented Oracle function SYS_OP_MAP_NONNULL that is another way to compare NULL values in Oracle.

It’s worth to read following articles to get understanding how Oracle treats NULL value in Oracle database:

Test data

CREATE TABLE test_tbl
(
  rec_num NUMBER,
  col1    NUMBER,
  col2    NUMBER
);

INSERT INTO test_tbl VALUES(1, NULL, NULL);
INSERT INTO test_tbl VALUES(2, 1,    NULL);
INSERT INTO test_tbl VALUES(3, NULL, 2);
INSERT INTO test_tbl VALUES(4, 3,    3);

COMMIT;

SELECT *
FROM TEST_TBL;

   REC_NUM       COL1       COL2
---------- ---------- ----------
         1                       
         2          1            
         3                     2 
         4          3          3

Usually to find/compare NULL values Oracle functions NVL, DECODE are used or IS NULL statement.

SELECT *
FROM TEST_TBL
WHERE nvl(col1, 100) = nvl(col2, 100);

   REC_NUM       COL1       COL2
---------- ---------- ----------
         1                       
         4          3          3

SELECT *
FROM TEST_TBL
WHERE decode(col1, NULL, 100, col1) = decode(col2, NULL, 100, col2);

   REC_NUM       COL1       COL2
---------- ---------- ----------
         1                       
         4          3          3 
SELECT *
FROM TEST_TBL
WHERE col1 = col2
  OR (col1 IS NULL AND col2 IS NULL);

   REC_NUM       COL1       COL2
---------- ---------- ----------
         1                       
         4          3          3

New function SYS_OP_MAP_NONNULL as default converts NULL value into RAW value FF

SELECT 
  SYS_OP_MAP_NONNULL(NULL), 
  SYS_OP_MAP_NONNULL('text') 
FROM dual;

SYS_OP_MAP_NONNULL(NULL) SYS_OP_MAP_NONNULL('TEXT')
------------------------ --------------------------
FF                       7465787400

CREATE TABLE TEST_TBL1
AS
SELECT 
  SYS_OP_MAP_NONNULL(NULL) ok, 
  SYS_OP_MAP_NONNULL('text') ok1 
FROM dual;
DESC test_tbl1
Name Null Type        
---- ---- ----------- 
OK        RAW(1 BYTE) 
OK1       RAW(5 BYTE)

So you can use it instead of  NVL, DECODE, IS NULL

SELECT *
FROM TEST_TBL
WHERE SYS_OP_MAP_NONNULL(col1) = SYS_OP_MAP_NONNULL(col2);

   REC_NUM       COL1       COL2
---------- ---------- ----------
         1                       
         4          3          3

Be careful when comparing different types using this function as this function maps different types to the same range of values.

Always convert to the same type before comparing different types

SELECT SYS_OP_MAP_NONNULL(a) a, SYS_OP_MAP_NONNULL(b) b
FROM ( SELECT '>\f' a, -9 b FROM DUAL )
WHERE SYS_OP_MAP_NONNULL(a) = SYS_OP_MAP_NONNULL(b);

A B
-------- -------
3E5C6600 3E5C6600

SELECT SYS_OP_MAP_NONNULL(a) a, SYS_OP_MAP_NONNULL(b) b
FROM ( SELECT '>\f' a, TO_CHAR(-9) b FROM DUAL )
WHERE SYS_OP_MAP_NONNULL(a) = SYS_OP_MAP_NONNULL(b);

no rows selected

Have a fun 🙂

Tomasz

 

2 thoughts on “Oracle SYS_OP_MAP_NONNULL function

  1. Why you should be careful with undocumented features.
    Do you think this will return the row or not? Try it!
    SELECT *
    FROM ( SELECT ‘>\f’ a, -9 b FROM dual )
    WHERE sys_op_map_nonnull(a) = sys_op_map_nonnull(b);

    • Neat!
      It produces a row because sys_op_map_nonnull treats datatypes differently, mapping their results into the same range.
      The problem is, the first column is VARCHAR2 and the second is NUMBER.
      The same match would happen for ‘>df’ and -1.

      Point taken: gotta be careful with undocumented features, but datatype issues will bite you with documented features as well.

Leave a Reply to Paul Rutgers Cancel reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.