Grails 3 - Find object by CLOB value

Question

Running grails 3.2.10 against Oracle 12g. I have a domain with a field that's a CLOB, I'm trying to find all instances of this domain that have a specific value in that field.

class SomeDomain {
  String name
  String value

  static mapping = {
    value sqlType: 'CLOB'
  }
}

I realize that Oracle doesn't allow direct equality comparisons of CLOB values... e.g.,

SELECT * FROM some_domain where value = 'myValue'

will fail, but

SELECT * FROM some_domain where dbms_lob.compare(value, 'myValue') = 0

gives the correct results.

However, when I attempt to do HQL or Criteria queries to retrieve this in grails, it is attempting a direct equality comparison, and throwing Exception.

SomeDomain.findAllByValue('myValue') --> ORA-00932: inconsistent datatypes: expected - got CLOB

It seems like I've already instructed grails to treat this column as a CLOB, is there a way to convince it to use dbms_lob comparisons for a specific query?

I'm open to any other suggestions as well... fairly surprised I couldn't find this question asked anywhere, maybe I'm just bad at searching.

Answer 1

I'm new to Grails, so I'm kind of guessing here. But I have a couple ideas to try.

Instead of value sqlType: 'clob', I've seen some people suggest that you should use value type: 'text', or value type: 'materialized_clob'. But I'm not sure what these actually do differently.

Or, instead of using a findAllBy*, I think you could use Hibernate SQL Restrictions to do your query in native sql.

def c = SomeDomain.createCriteria()

def clobEquals = c.list {
  sqlRestriction "dbms_lob.compare(value, ?) = 0", ['myValue']
}

(I haven't actually tried this.)

Finally, as a workaround, you can use LIKE with clobs, and without include a wildcard it's equivalent to =

SomeDomain.findAllByValueLike('myValue')

This might not work with large string variables though.