Oracle converts empty string to null but JPA doesn't update entity cache correspondingly

It's a well known fact, that Oracle treats empty strings as null.

However, I'm having an issue because of this behaviour due to JPA's caching.

First I persist using JPA (Toplink Essentials) an entity, which has an empty string as one field. Oracle converts this value to null when it stores it.

However, when I fetch the entity, JPA seems to return it from the cache, where this field is still an empty string. JPA doesn't seem to know, that what got stored into the database was actually a null value, and this incoherency is causing problems.

Is there a way to solve this issue on the JPA or application server (Oracle AS) configuration level? This something that I would not want to fix in the application level (but will do if it's necessary).

From stackoverflow

• I think the problem is that Oracle does not store the string as a null value it stores it as an "unset"-value (let's call it super-null). so if you try to select the values that are null you will not retrieve that item because it is unset and not null. have you tried to insert specifically null and not an empty-string?

  I had a project once - where I had a similar problem where the solution was to store specifically null into the database-column and not string.empty since it was treated as not set and not as null...

  tputkonen : Actually those values do get stored as null to the database.

  Gambrinus : okay... have you checked if the result is DBNull? since java makes a difference between null and DBNull

  tputkonen : Not sure what you mean, but first I persist an entity with field foo (empty string). Oracle stores this as null. Then I test if foo contains null value, if not, try to store it to another non nullable field => exception. I wish JPA would nullify foo the same way it stores id's when persisting.

  tputkonen : Additional comment: in the application level I should not know that Oracle treats empty string as null, that why JPA should replace the field after persisting the value. My code would work if I changed the DB provider to one which can store empty strings.

  Gambrinus : I meant this http://www.oracle.com/technology/products/jdev/esdk/api1012/oracle/jdeveloper/cm/ds/db/DBNull.html

  tputkonen : Ok, we are not using JDeveloper extensions. But I don't think it would make any difference.

• We have migrated to EclipseLink, which supports @ReturnInsert and @ReturnUpdate annotations. These help to refresh the field with the value that actually got stored into the database.