Using function in where clause with clob parameter

Question

We are using a @NamedNativeQuery to fetch entities from our database that are qualified by the stored procedure flexmatch in the where clause of a query.

This works fine in general, but when the parameter chimeString exceeds 4.000 characters it fails raising the following exception:

ORA-01460: unimplemented or unreasonable conversion requested

This does make sense, as 4.000 characters are Oracle's border between String and Clob.

We tried to

use org.hibernate.engine.jdbc.ClobProxy

return entityManager
    .createNamedQuery("Structure.findByExactMatch", Structure.class)
    .setParameter("chime", ClobProxy.generateProxy(chimeString))
    .getResultList();

use javax.persistence.criteria.ParameterExpression together with org.hibernate.engine.jdbc.ClobProxy

ParameterExpression<Clob> chimeParam = entityManager
    .getCriteriaBuilder()
    .parameter(Clob.class, "chime");
return entityManager
    .createNamedQuery("Structure.findByExactMatch", Structure.class)
    .setParameter(chimeParam, ClobProxy.generateProxy(chimeString))
    .getResultList();

Libraries & System:

Oracle 11g
Hibernate 3.6.6

The find method.

public List<Structure> findByExactMatch(String chimeString) {
  return entityManager
      .createNamedQuery("Structure.findByExactMatch", Structure.class)
      .setParameter("chime", chimeString)
      .getResultList();
}

The Structure entity.

@Entity
@NamedNativeQueries({
  @NamedNativeQuery(
    name = "Structure.findByExactMatch",
    query = "SELECT id, molfile(ctab) ctab FROM structure " + 
            "WHERE flexmatch(ctab, :chime, 'all')=1",
    resultClass = Structure.class) })
public class Structure {

  @Id
  @Column(name = "ID")
  private long id;

  @Lob
  @Column(name = "CTAB")
  private String ctab;

  // getter & setter

}

Edit 1 The pl/sql function, as you can see it is overloaded.

FUNCTION flexmatch(
    molobj IN BLOB, 
    querymol IN VARCHAR2,
    args IN VARCHAR2)
RETURN NUMBER

FUNCTION Flexmatch(
    molobj IN BLOB, 
    querymol IN CLOB,
    args IN VARCHAR2)
RETURN NUMBER

score 0 · Accepted Answer · answered Dec 23 '13 at 14:45

0

After some days of trying, we gave up to solve it within Hiberante. We ran the query using SpringJDBC, which is also present in the project, and used the ID to populate a Hiberante entity. You could do this with plain old JDBC also.

answered Dec 23 '13 at 14:45

cheffe

9,345
2
46
57

Using function in where clause with clob parameter

1 Answers1

Linked