Hacker News Reader

Comment by spullara | Hacker News Reader

Oracle cannot store empty strings (they are treated as NULL).

Wow, I've been working with Oracle for a million years and I didn't know that.

Fortunately, now that I'm over a million years old I'm probably not going to freak out about it no matter how wrong it seems. I might relax my pro-NULL politics a bit though.

This was one of the first things I recall learning about Oracle when I had my first internship dealing with it. It boggles my mind that you can work with Oracle for more than a week and not run into this.

Enjoy your boggle, because I've been working (on and off) with Oracle since 1994, and despite frequently being the "guy who explains NULL" I never ran across this, nor did anyone ever point it out to me.

I would even say I've worked with some top-notch DBA's, and I've at times been insufferable in my defense of NULL as a concept, and the databases in question have dealt with a whole lot of data and made a whole lot of money -- yet somehow I didn't know this.

Yay HN. :-)

In my experience it most often manifests in the form of columns that are constrained not NOT NULL into which you suddenly can't insert, say, a user who has no last name.

Stupid question: Have you been able to side-step the limitation because an empty string is stored as NULL?

I discovered this porting an application from Postgres to Oracle. Another thing that drives me nuts about Oracle is that there is no native boolean type.

As far as I can tell Oracle can store empty strings they just happen to be treated exactly like NULL. That's at least marginally better. Still, it's hard to imagine people being willing to use Oracle nowadays.

Why? Because you think sql is not good or because you think there are better relational databases?

I'd say that Oracle delivers quite much value for your money for many applications.

I have worked with all other relevant relational databases and also with a few nosql databases, in distributed systems and monoliths.

It's hard to make a recommendation that can be applied generically. The best answer is "It depends, and it's complicated"

One thing is sure. Nosql is not the future (quite the opposite) although sometimes -in rather special circumstances- the right solution. Graph databases are perhaps more interesting, and perhaps event sourcing, but the promises are way larger than the deliveries.

Edit. I forgot the obvious reason. You try to stay clear of the Dark Side.

Know any graph databases that you like? I recently tried arangodb and it consumes a crazy amount of memory for even a relatively small dataset (several million ~20 character strings)

Try Postgres. I'm serious. For a dataset that should easily fit in memory, doing graph queries CTE's in Postgres can work very well.

It's mostly because of the money. Relational is still a great type of database and I'm sure Oracle is great. But I haven't worked with many companies that could afford Oracle very easily if at all.

I also wouldn't make a generic database recommendation :)

Not necessarily a choice, but everywhere I've ever worked used Oracle. Including my last 2 <500-person Valley-based tech companies I've been employed at used it.

For all its drawbacks (and I'm not a DBA or developer) at least I don't have to participate in HN threads where people discuss the 10 buzzwordy-DB-but-not-a-DB trends-of-the-week to migrate your data to.. which will all be forgotten next week :)

> Still, it's hard to imagine people being willing to use Oracle nowadays.

I use Oracle every day, because that is one of the options for our customers rely on to run their critical Fortune 500 infrastructure. The other being SQL Server.

Does that mean that two empty strings won't compare equal to each other?

This rears its head when you want to make a string column nullable. If you do that, you can't store empty strings in it. Really broken IMHO.

Postgresql is pretty good, however Oracle still can do some crazy things, at the cost of a correspondingly insane amount (IMHO) of database admin / sysadmin overhead.

An empty string has a length. That length is zero. NULL does not have a length.

The length of a string shouldn't matter to anybody.

Really? I can think of at least a couple of good reasons it matters (i/o related).

What matters is the amount of space it takes up. What matters is the content of the string in the context of parsing it and displaying it. And in all of these scenarios -- the only relevant scenarios for using a string -- a null string and an empty string should almost always have identical behavior.

There is a big difference between not knowing a value and knowing the value is empty.

That's really up to the context of the problem, and the specific semantics of a null value. (Which is a value, and a present one at that. If you need to account for missing data, you can do that any number of ways, with sentinel values of any kind.)

The length of your comment is 51 characters.

> it's hard to imagine people being willing to use Oracle nowadays.

Only when it's the lesser of the super-evils you have to pick from. I'd pick Oracle over Progress(OpenEdge), but not much else :)

Reply on news.ycombinator.com