CODEWITHSUNDEEP
hibernatepostgresqljpaoracle11g
Matteo
Matteo

Reputation: 14940

org.hibernate.type.TextType and Oracle

We use Hibernate as a JPA provider and we have a class with a large object field marked with

@Lob
@Type( type = "org.hibernate.type.TextType" )
private String someString;

The column is created as

SOMESTRING            LONG()

This works flawlessly with PostgreSQL and MySQL. With Oracle when persisting the object

entityManager.persist( object );

we get an org.hibernate.exception.GenericJDBCException: Could not execute JDBC batch update exception.

Removing the @Type( type = "org.hibernate.type.TextType" ) annotation will solve the problem with Oracle but introduces an encoding problem with PostgreSQL as described in Cannot store Euro-sign into LOB String property with Hibernate/PostgreSQL

I would like to know how to define a large text field so that out program works on both PostgreSQL and Oracle. A pure JPA solution would be optimal but an Hibernate specific one will also do.

Edit:

The real exception:

java.sql.BatchUpdateException: ORA-22295: cannot bind more than 4000 bytes data to LOB and LONG columns in 1 statement

Now, the exception I missed explains the problem and in fact the object I am persisting has more than then the large string (at least one long DBID).

Upvotes: 9

Views: 11361

Answers (5)

Peter Butkovic
Peter Butkovic

Reputation: 12179

credits should go elsewhere (to user called: @liecno), but based on one of (his) comments at the answers in the: Postgres UTF-8 clobs with JDBC the compatibility with Oracle as well as Postgres can be simply achieved via:

@Lob
@Type(type="org.hibernate.type.StringClobType")
private String someString;

UPDATE:

OK, after some testing, I came to even more up to date solution, that worked for all my scenarios. As org.hibernate.type.StringClobType is deprecated (hibernate 4.2.x), I went to it's replacement: org.hibernate.type.MaterializedClobType.

@Lob
@Type(type="org.hibernate.type.MaterializedClobType")
private String someString;

Upvotes: 4

dboldureanu
dboldureanu

Reputation: 581

This worked for me. Extended PostgreSQL81Dialect like this:

public class MyPostgreSQL81Dialect extends PostgreSQL81Dialect
{

  @Override
  public SqlTypeDescriptor getSqlTypeDescriptorOverride(int sqlCode)
  {
    SqlTypeDescriptor descriptor;
    switch (sqlCode)
    {
      case Types.CLOB:
      {
        descriptor = LongVarcharTypeDescriptor.INSTANCE;
        break;
      }
      default:
      {
        descriptor = super.getSqlTypeDescriptorOverride(sqlCode);
        break;
      }
    }  
    return descriptor;
  }

}

Upvotes: 5

Steve Ebersole
Steve Ebersole

Reputation: 9443

@Lob
private String someString;

tells Hibernate to treat this as a "materialized CLOB". A CLOB because of the @Lob annotation, materialized because we have to extract the String/characters from the CLOB locator.

When I say CLOB here, I mean java.sql.Types.CLOB. So the JDBC type code. However, Hibernate has another level of indirection here: the Dialect. The Dialects are able to define how type codes are mapped to physical database types (used in schema export, cast calls, etc) as well as how bind/extract operations are performed against PreparedStatement/ResultSet. The PostgreSQL Dialects maps Types.CLOB to the physical "text" datatype; the Oracle Dialects map it to the physical CLOB datatype; the MySQL Dialects map it to the physical "longtext" datatype.

Upvotes: 1

mindas
mindas

Reputation: 26723

I had a similar problem and the only way was to extend/customize the Oracle dialect.

Upvotes: 2

Johanna
Johanna

Reputation: 5303

The error means, Oracle can't handle two columns with more than 4000 bytes each in one update. In the past Oracle already had problems with this - I remember versions where only one LONG column per table was allowed. I wonder they didn't remove this issue. (I don't know if you really have a second LONG or LOB column in that table, but I guess there will be one.)

I think removing the @Type annotation does not generally solve the problem. In your test the effect might be then there are just less than 4000 bytes, but with other data the error also might appear.

Solution: I think you have separate the update statement into two statements, like that what I sketch here (batch update is still possible):

for (many rows) {
  object.setFirstLongColumn(...);
  persist(object);
  session.flush();
  object.setSecondLongColumn(...);
  persist(object);
  session.flush();
}
commit();

The session.flush() is necessary to prevent Hibernate to change the order of the database statements which could interfere with ou logic.

One more thing: LONG is deprecated in Oracle. If you have good luck then replacing LONG with LOB in the database solves your problem.

Upvotes: 1

Related Questions