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

Have a fun 🙂

Tomasz

 

Leave a Reply

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