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.
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.
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 🙂
10 Comments
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?
not sure what you mean, can you share a test case?
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.
Sorry, should have stated, SQLDeveloper 4.1.3 on Mac using Oracle JDBC Thin.
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’?
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.
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
I believe that’s fixed in version 4.1, some colors work better than others. I have red cells working find against the zebra style grids.
Ok, that’s fine. Actually I’m on 4.0.3
Regards,
Roman
Def a bug in that and earlier versions. Here’s what it looks like, working!, in version 4.1