org.hibernate.type.TextType and Oracle

Question

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).

Answer 1

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;
  }

}

Answer 2

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;

Answer 3

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