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?
About Jeffrey Kemp
Application Designer & Developer at Oracle specialising in Oracle APEX (Application Express), Oracle SQL and PL/SQL. Oracle ACE Alumni ♠️. Piano player, father, husband, Christian. The views expressed on this blog are my own and do not necessarily reflect the views of Oracle.
John Flack
5 August 2010 - 10:55 pm
Years ago, Oracle started to warn us that they were eventually going to correct the non-standard usage of an empty string (”) as the same as NULL – at least for VARCHAR. VARCHAR2 would stay the same, being Oracle’s own proprietary variation on the ANSI standard SQL datatype, VARCHAR. They’ve stopped warning, and never implemented this change – VARCHAR as far as I know is still the same as VARCHAR2.
Phil Lambert
8 September 2010 - 8:14 am
I often “type” a NULL with TO_CHAR, TO_NUMBER, TO_DATE, etc. So you could use this:
EXECUTE IMMEDIATE ‘update my_table set my_column = :value’
USING TO_CHAR(NULL);