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.
No comments:
Post a Comment