0

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.

Zac
  • 1,305
  • 3
  • 17
  • 28
Trebla
  • 1,164
  • 1
  • 13
  • 28

1 Answers1

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.

kfinity
  • 8,581
  • 1
  • 13
  • 20
  • Fantastic idea on the `sqlRestriction` (that I was unaware of), unfortunately, I wasn't clear that I needed hibernate to be aware of this as the application has multiple deployments against different databases, but I believe this would have worked based on the documentation. The "like" comparison should be sufficient (I will do further testing to confirm, but I'm confident in your solution). – Trebla Oct 30 '17 at 11:44