2

I retrieve data from java then insert into Oracle, problem is oracle took "" values then convert it into NULL into the table.

this is unwanted behaviour, and it cause problems when I retrieve it from the oracle again, which I am expecting a "" rather than NULL

I prefer a elegant solution setting something on the oracle server side. a global config or something rather than putting millions of if statements into my Java code.

Junchen Liu
  • 5,435
  • 10
  • 51
  • 62
  • 1
    Unfortunately [this is how Oracle functions](http://stackoverflow.com/q/13278773/1134080). You may consider switching to PostgreSQL or MySQL which function differently. – ADTC Oct 21 '14 at 11:20
  • 8
    Well known fact: Oracle *doesn't* have *empty string*, it uses `NULL` instead – Dmitry Bychenko Oct 21 '14 at 11:20
  • @Pablo most of the questions on this site, explains the same subject with a WHY, I am not looking for why, I am looking for a solution – Junchen Liu Oct 21 '14 at 13:33

2 Answers2

3

As said in the comments, nothing you can do about it. Oracle treats an empty string no different from NULL and will store NULL into a varchar2 column when you supply it with an empty string.
What you can do if you really want to have something in there, store a single space instead.

jwenting
  • 5,505
  • 2
  • 25
  • 30
  • `-` is often clearer that `` but essentially you are correct. – OldCurmudgeon Oct 21 '14 at 11:32
  • @ShishirKumar that's just how Oracle works. And historically it's quite correct behaviour. How would the database know whether an empty input field in a Forms application indicates an empty string or no input at all? – jwenting Oct 21 '14 at 11:53
  • Hey @jwenting, I'm sorry if I offend you. I am aware of this behavior of Oracle. But not comfortable with saving a single space or hyphen or anything when I just want to leave the column blank in first place. I believe this will defeat the idea, not as a whole, of Oracle to save null & empty spaces as same. – Shishir Kumar Oct 21 '14 at 12:19
  • reason I choose this as answer is because saving a single space is the solution. I know its not nice, but at least it shows the value we are expecting it to be there. its not the programmer made this odd situation, its oracle ill defined this function i guess – Junchen Liu Oct 21 '14 at 15:55
  • I am still uncomfortable with this choice of saving a single space instead of empty strings. Its like I wanted an empty bottle of wine but you give me wine bottle filled with water and I am ok as I can't see the water. – Shishir Kumar Oct 23 '14 at 06:38
  • -1 Avoid magic values (like `'-'`, which may be confused with valid user input). Just use `NULL` like everyone else to represent an empty string. – Jeffrey Kemp Oct 23 '14 at 07:00
  • 1
    @JeffreyKemp you troll, NULL is as much a "magic value" as anything. And I never say to use a hyphen. READ – jwenting Oct 23 '14 at 17:00
  • Hi jwenting, welcome to SO. We're all friends here, no offence is intended. Now - I agree, NULL is a magic value as well. However, the code to detect it is much simpler than that required to deal with non-standard magic values like `'-'` and `' '`, which may conflict with valid user input. Also, *I* never said *you* suggested the hyphen - you suggested a single space. But, it doesn't matter - both suffer from the same problem. A space is potentially worse, because in some UIs (e.g. SQL*Plus) a space is visually indistinguishable from a NULL. – Jeffrey Kemp Oct 24 '14 at 07:39
1

An empty string is treated as a null value in Oracle.

As said above, the only solution to differentiate between null values & empty strings is to keep in mind that the null value is unique in that you can not use the usual operands (=, <, >, etc) on a null value.

Instead, you must use the IS NULL and IS NOT NULL conditions to check nulls in Oracle.

Moreover, in order to select the records which has column as empty spaces but not empty string and not null, below can be used.

WHERE DATALENGTH(COLUMN) > 0

And if you want to select any string consisting entirely of spaces as empty.

WHERE COLUMN <> ''

Hope this helps you.

Shishir Kumar
  • 7,981
  • 3
  • 29
  • 45