[FIXED] Empty CLOB instead of NULL in Oracle CLOB field

Issue

Writing null string into Oracle CLOB field using Hibernate sometimes result in an empty CLOB instead of NULL in the field.

Table STEP with a mix of varchar2, number and CLOB fields once was extended with a new CLOB field.

ALTER TABLE STEP 
    ADD (IN_PAR_A CLOB) 
    LOB(IN_PAR_A) STORE AS CLOB_STEP_IN_PAR_A 
        (NOCACHE FILESYSTEM_LIKE_LOGGING COMPRESS HIGH TABLESPACE <tblspace for LOBs>);

and a corresponding field was added to entity

@Entity
@Table(name = "STEP")
@Data
public class Step {
  //some other fields
  @Column(name = "IN_PAR_A")
  private String inParA;
}

All work as expected when non-null value stored. But when new record stored with non-null value which later updated to null (using transaction) sometimes there is an empty CLOB stored in the field instead of NULL.

Step entity = session.get(Step.class, "some ID");
entity.setInParA(null);
//other fields
session.update(entity);

Main problem is instability - non-null value is written only in 1/3..1/4 of records. I was unable to reproduce at dev instance of system via direct hibernate storing Step entity with any combinations of NULL and "" in the step.inParA field. But it happens on product system. I was able to debug a little Oracle by adding a trigger and found that sometimes it got "" from driver, which I again unable to reproduce at dev. Also I was able to initiate about two dozen times the exactly same sequence, which lead to new STEP records, and some of them stored empty CLOB while most stored null. I have tried adding a trigger to replace empty to NULL, but it ruined the record - somehow instead of NULL or empty the field stored some random data from other fields.

It is required that STEP.IN_PAR_A contain NULL instead of empty CLOB, but I have no idea what to fix.

Any advice would be appreciated.

UPD As suggested just adding @Lob annotation helped. Old 14 columns in 5 tables without this annotation works fine (but now annotations are added)

Solution

One solution would be to use @Lob annotation instead. Although Hibernate supports strings to deal with clob columns, I found always better to use this annotation and avoid therefore any potential issue.

Here you can find a nice article on how to use it

Hibernate LOB

My personal believe is that Hibernate still has some issues when dealing with CLOB using string. I found myself in other strange scenarios and using @LOB always solved them.

Answered By - Roberto Hernandez
Answer Checked By - Pedro (JavaFixing Volunteer)