SQL Developer Quick Tip: Take the Guess Work out of NULL

thatjeffsmith SQL Developer 10 Comments

Tell Others About This Story:

There’s a lot of confusion out there about the absence of a value, also known as NULL. It can cause havoc with your queries if you don’t account for it. Some folks confuse it with a blank space in a string. At least SQL Developer can make one thing a bit easier, interpreting NULLs in a data grid.

By default when SQL Developer displays data in a grid, it indicates NULL with {null}. I’m a bit thick in the head though, and I require an extra nudge or two to get the message through to my brain. I have some friends who are purists though and don’t want their tools to show anything that’s not there.

Thankfully this is configurable in the Preferences.

Preferences – that thing you never look at

Nearly every piece of software has a set of options that can be configured by the user to tweak their experience to their liking. Many tools have tons of options that never get inspected, and that’s fine. Here’s a quick tip for the SQL Developer Preferences – use the ‘Search’ box.

So type ‘null’ in the search dialog – but don’t hit ENTER key! That will close the dialog. The search is search-as-you-type, and is wicked fast.

Search as you type preferences, for the win!

Don’t do as I do

I do a lot of demos and presentations. Instead of blindly clicking through screens or slides, I like to make obnoxious tweaks to my software. This gets people’s attention and they’ll usually ask a question or two. One example of this is how I treat NULL in a grid.

There should be no question here, that field is NULL!

So go take a second now and set this option to your personal preferences. And the next time someone gives you a busted report because they don’t get NULL, have them read Tom’s take on NULL.

Zebra Grids + Null Coloring

Prior to version 4.1, there was a problem with asking for the background coloring for NULLs when you were also using the zebra preference for grids.

That’s fixed now 🙂

The 'Zebra' Preference and an Example

red_nulls_zebra

Tell Others About This Story:

Comments 10

  1. Jeff, now the only problem I am having is updating a row that has NULLs in it. SQL Developer reports an error of ‘Illegal format in column’. If I try to remove the (null) label, it just comes back. Any suggestions?

    1. thatjeffsmith Post
      Author
      1. Sure Jeff. When I look at a table directly (with Data tab), I have several columns that have NULL values (set in preferences to be (null) ). If I update some data in other columns in a row, and then click the commit button, I get an error ‘illegal format in column’ as the columns that are to remain NULL are numeric and the editor is seeing (null) as an invalid value. If I try to delete the (null) value (Mac), as soon as I leave the field, the (null) reappears and the problem is there again on the commit. Even if I change the preferences to show ‘nothing’ for NULLS, the error appears, regardless if a backspace the field or leave it as is. Is there a value or keystroke to actually enter a NULL into a field content? Just can’t find a way around having a NULL value remain there on the update. I realize I could just manually do an UPDATE, but there are long XML fields in this row and I just didn’t want to formulate SQL code to do it. (I have since actually, to get it to update). And thanks for getting back to me.

  2. I’m testing SQL Developer with MySQL. It displays “{null}” for string values that are empty, i.e. ”. That’s not NULL.

    Given a table ‘foo’, with a record like {‘id’: 0, ‘name’: ”}, and this query:
    select id, ifnull(name, ‘No’)
    from foo
    where id = 1;
    The result is: 1, {null}
    If SQL Developer correctly interpreted NULL, shouldn’t this be 1, ‘No’?

    1. thatjeffsmith Post
      Author

      null strings in Oracle are in fact ” – I’m not sure about MySQL. SQL Developer isn’t interpreting your query – the MySQL engine is. if the results are wrong, it’s probably your query – or in a very unlikely case, a MySQL DB bug.

  3. Hi Jeff, I’d like to combine the output of data grid in zebra form and show NULLS as a different color. This doesn’t seem to work all at a time.
    Can you combine the color value of the NULL color and the zebra’d color of the grid? If I chose PINK as the null color, then the grid changes between lighter and darker pink when NULL values occur?
    Regards,
    Roman

    1. thatjeffsmith Post
      Author
        1. thatjeffsmith Post
          Author

Leave a Reply

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