Skip to content

In Oracle, ” = NULL but NULL != ”

August 5, 2010

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?

About these ads

From → PL/SQL

2 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

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 180 other followers

%d bloggers like this: