In Oracle, ” = NULL but NULL != ”
When I get the result of my PL/SQL quiz for the day, I’m pleased when I got it right, but if I got it wrong, I’m either annoyed or overjoyed:
- If I disagreed with the result, I’m annoyed.
- If I agreed with the result, I’m overjoyed – because I learned something new, or I was reminded of something I should have remembered.
Option #2 was my experience this morning – yesterday’s quiz featured the following code snippet:
... EXECUTE IMMEDIATE 'update my_table set my_column = :value' USING NULL; ...
This was one of four other, very similar, options – and I failed to notice that this version was binding NULL directly into the statement, instead of using a variable as any normal, reasonable, rational human being would. This snippet raises PLS-00457: expressions have to be of SQL types, which in this case is due to the fact that NULL is of no particular SQL type.
If one wanted to bind a literal NULL into a statement such as the one above, you don’t necessarily need a variable:
... EXECUTE IMMEDIATE 'update my_table set my_column = :value' USING ''; ...
Proving that while ” is NULL, NULL is not ” – they are not always interchangeable.
P.S. please ignore the post title – I know it is incorrect to write ” = NULL or NULL != ” - but it wasn’t meant to be code, ok?