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:

  1. If I disagreed with the result, I’m annoyed.
  2. 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?

Forms Library: General bits and pieces
Forms Library: PKG_DEBUG

Comments

  1. 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.

  2. 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);

Leave a Reply

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