Tuesday 11 March 2014

Multi-table INSERT and handling of NULL values in the WHEN condition

Thought I'd just blog this to ensure I don't forget it. Basically, I had a problem with a multi-table INSERT with some very basic conditional processing. One of the conditions was a very simple equality comparison between a column and a literal, but astonishingly Oracle treats this as "true" if the column is NULL (unlike in all other circumstances where expression = value is false if "expression" is NULL. My test case is shown here :
CREATE TABLE mti_test ( a VARCHAR2(10), b VARCHAR2(10) )
/

CREATE TABLE temp_mc ( a VARCHAR2(100) )
/

INSERT INTO mti_test (a,b)
SELECT 'NUMBER', '1' FROM dual 
UNION ALL
SELECT 'CHAR', 'B' FROM dual
/
Now, running the following multi-table insert has no problems :
SQL> INSERT ALL
  2    WHEN a = 'NUMBER'
  3     AND 1 = TO_NUMBER(b)
  4    THEN
  5      INTO TEMP_MC VALUES ( 'Y' )
  6  SELECT a,b 
  7    FROM mti_test
  8  /

1 row created.
However, now introduce a row with a NULL value for "a", i.e. :
INSERT INTO mti_test (a,b)
SELECT NULL, 'C' FROM dual
/
and suddenly :
SQL> INSERT ALL
  2    WHEN a = 'NUMBER'
  3     AND 1 = TO_NUMBER(b)
  4    THEN
  5      INTO TEMP_MC VALUES ( 'Y' )
  6  SELECT a,b 
  7    FROM mti_test
  8  /
   AND 1 = TO_NUMBER(b)
           *
ERROR at line 3:
ORA-01722: invalid number
So, now Oracle thinks that NULL = 'NUMBER' is true! The immediate fix is to also include a "AND a IS NOT NULL" predicate, i.e. :
SQL> INSERT ALL
  2    WHEN a = 'NUMBER'
  3     AND a IS NOT NULL
  4     AND 1 = TO_NUMBER(b)
  5    THEN
  6      INTO TEMP_MC VALUES ( 'Y' )
  7  SELECT a,b 
  8    FROM mti_test
  9  /

1 row created.
Just something to be aware of when doing multi-table inserts.