13

WARNING: see my own answer below. The problem is caused by old Oracle drivers that were present on the classpath in addition to 10.2.0.4. Problem solved. Leaving the rest of this question for posterity.

I've been banging my head against the following. Here's a simple POJO distilled from my application code:

@Entity
@Table(name = "PIGGIES")
public class Piggy {    
    private Long id;
    private String description;

    public Piggy() {}

    @Id
    @GeneratedValue
    @Column(name = "PIGGY_ID")
    public Long getId() { return id; }
    public void setId(Long id) { this.id = id; }

    @Lob
    @Column(name = "PIGGY_DESCRIPTION")
    public String getDescription() { return description; }
    public void setDescription(String d) { description = d; }
}

There's a String property and a CLOB column. When the contents are short (e.g. "hello world"), it persists just fine. With longer strings, I get the following exception:

java.sql.SQLException: operation not allowed: streams type cannot be used in batching
        at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:134)
        at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:179)
        at oracle.jdbc.driver.OraclePreparedStatement.addBatch(OraclePreparedStatement.java:4236)
        at org.apache.commons.dbcp.DelegatingPreparedStatement.addBatch(DelegatingPreparedStatement.java:172)
        at org.apache.commons.dbcp.DelegatingPreparedStatement.addBatch(DelegatingPreparedStatement.java:172)
        at org.hibernate.jdbc.BatchingBatcher.addToBatch(BatchingBatcher.java:31)
        at org.hibernate.persister.entity.AbstractEntityPersister.update(AbstractEntityPersister.java:2403)

I'm using Hibernate 3.2.3 with Oracle JDBC driver 10.2.0.4. The exception's message indicates that batching may be at fault. While I can disable batching in this simple case, I need to have it enabled for the "real" POJOs. In fact, as things stand right now, query batching is the only reason we're using Hibernate at all.

So, my question is, how can I make the above work?

EDIT: Interesting observation: the value of my "description" property persists just fine as long as it's exactly 1333 characters long or shorter. Such an odd number!

EDIT 2: In an attempt to find a solution, I modified the getProperty() annotations as follows, which has made no difference:

@Lob
@Type(type="text")
@Column(name = "PIGGY_DESCRIPTION", length = Integer.MAX_VALUE)
public String getDescription() { return description; }

EDIT 3: Here's the DDL for "PIGGIES":

CREATE TABLE "PIGGIES" 
 (  "PIGGY_ID" NUMBER NOT NULL ENABLE, 
"PIGGY_DESCRIPTION" CLOB, 
 CONSTRAINT "PIGGIES_PK" PRIMARY KEY ("PIGGY_ID")
USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 
STORAGE(INITIAL 1048576 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "BBDATA"  ENABLE
 ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
STORAGE(INITIAL 1048576 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
  TABLESPACE "BBDATA" 
LOB ("PIGGY_DESCRIPTION") STORE AS "SYS_LOB0000177753C00002$$"(
TABLESPACE "BBDATA" ENABLE STORAGE IN ROW CHUNK 8192 PCTVERSION 10
NOCACHE 
STORAGE(INITIAL 1048576 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)) ;

And here's the entire stack:

org.hibernate.exception.GenericJDBCException: could not update: [com.bamnetworks.cms.types.Piggy#934]
    at org.hibernate.exception.SQLStateConverter.handledNonSpecificException(SQLStateConverter.java:103)
    at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:91)
    at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:43)
    at org.hibernate.persister.entity.AbstractEntityPersister.update(AbstractEntityPersister.java:2425)
    at org.hibernate.persister.entity.AbstractEntityPersister.updateOrInsert(AbstractEntityPersister.java:2307)
    at org.hibernate.persister.entity.AbstractEntityPersister.update(AbstractEntityPersister.java:2607)
    at org.hibernate.action.EntityUpdateAction.execute(EntityUpdateAction.java:92)
    at org.hibernate.engine.ActionQueue.execute(ActionQueue.java:248)
    at org.hibernate.engine.ActionQueue.executeActions(ActionQueue.java:232)
    at org.hibernate.engine.ActionQueue.executeActions(ActionQueue.java:140)
    at org.hibernate.event.def.AbstractFlushingEventListener.performExecutions(AbstractFlushingEventListener.java:298)
    at org.hibernate.event.def.DefaultFlushEventListener.onFlush(DefaultFlushEventListener.java:27)
    at org.hibernate.impl.SessionImpl.flush(SessionImpl.java:1000)
    at org.hibernate.impl.SessionImpl.managedFlush(SessionImpl.java:338)
    at org.hibernate.transaction.JDBCTransaction.commit(JDBCTransaction.java:106)
Caused by: java.sql.SQLException: operation not allowed: streams type cannot be used in batching
    at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:134)
    at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:179)
    at oracle.jdbc.driver.OraclePreparedStatement.addBatch(OraclePreparedStatement.java:4236)
    at org.apache.commons.dbcp.DelegatingPreparedStatement.addBatch(DelegatingPreparedStatement.java:172)
    at org.apache.commons.dbcp.DelegatingPreparedStatement.addBatch(DelegatingPreparedStatement.java:172)
    at org.hibernate.jdbc.BatchingBatcher.addToBatch(BatchingBatcher.java:31)
    at org.hibernate.persister.entity.AbstractEntityPersister.update(AbstractEntityPersister.java:2403)
    ... 45 more
Max A.
  • 4,842
  • 6
  • 29
  • 27

2 Answers2

32

Moron alert: it turns out that I had a stale JAR with 9-something Oracle JDBC classes on my classpath. Having cleaned that up, everything simply worked magically with just the following annotations:

@Lob
@Column(name = "PIGGY_DESCRIPTION")
public String getDescription() { return description; }

Blame the fat fingers.

Max A.
  • 4,842
  • 6
  • 29
  • 27
5

Have you tried dropping the @Lob annotation, and just annotating it with @Column? In my experience, you don't need to tell hibernate the column type for a CLOB, it will determine it on its own.

Can you include a snippet of the client code which is performing the batching operation?

Dherik
  • 17,757
  • 11
  • 115
  • 164
Jherico
  • 28,584
  • 8
  • 61
  • 87
  • I'm giving your suggestion a try, we'll find out in a few minutes. Regarding batching, there's nothing in my own code that does batching. It's a built-in Hibernate feature. There's a @BatchSize annotation that we use on associations in the app. If you look at the stack trace in my question, you can see all the batching calls that Hibernate goes through. – Max A. Oct 29 '09 at 16:16
  • Nope, removing @Lob and just leaving @Column and @Type didn't help. I've annotated the class itself with @BatchSize(size=0) for isolation purposes even though it's not an acceptable course of action overall. – Max A. Oct 29 '09 at 16:18
  • So the error is being thrown on the commit, what is the minimum set of operations inside the transactions needed to trigger the error? – Jherico Oct 29 '09 at 17:04
  • I'm just trying to persist one instance of `Piggy`. One table, one row, two columns. There's not much to it. This is actually exactly the same behavior as I'm seeing in the real app where there is, if you will, a collection of much larger `Piggy`-s. – Max A. Oct 29 '09 at 17:10
  • So your description has to be `<= 1333` that's rather odd and you have ruled out this being a side-effect of batching by removing any explicit batch references in your code(annotations/xml) and in your properties file? – non sequitor Oct 29 '09 at 18:56
  • See my own answer re: extraneous JARs. Given the very bizarre numerology of 1333 I'm inclined to think that I was simply being screwed by some conflicts somewhere. – Max A. Oct 29 '09 at 19:38
  • You should change the title of the question. Fluent Nhibernate is better instead of Nhibernate :) – NetSide Dec 17 '09 at 13:15