Friday, September 12, 2008

empty string is NULL in Oracle Database

Let's say you have a string t, and want to insert into 
an oracle database, and want to find the row later. 
Sure, let's do this:

insert into table_a (status) values(?)
select * from table_a where status = ?

In java, it's pretty simple, right? 
Just use prepared statement, 
ps.setString(1,t)
And when read, also
ps.setString(1,t)

Everything works normal, unless t="".
When t is an empty string, no rows found!!!

Actually, in Oracle database, there is no zero-length string. Any zero-length string, either from a function call or the literal '', is treated as null. 

This is an atrocious Oracle Database behavior!


No comments: