oracle: DbJson - inconsistent datatypes: expected - got CLOB

[rvowles]

I did search for the issue, I'm just looking for some advice as to what to about it. I have a field that is marked as a @DbJson - which in Oracle is translating to a CLOB. When this field is requested from the database it gives this error because I believe CLOBs cannot be retrieved in a normal select.

Expected behavior

I'm not sure if on Oracle I should expect it to work. If I need to turn it into a VARCHAR(4000) or similar for Oracle I can do that, I'm just thinking it should be in the list of closed issues with what to do :-) Ideally I don't want to put in specific code to detect if the app is talking to Oracle and lazy load that specific field.

Actual behavior

"javax.persistence.PersistenceException: Query threw SQLException:ORA-00932: inconsistent datatypes: expected - got CLOB\n Bind values:[] Query was:select distinct t0.id, t0.is_prod_environment, t0.name, t0.description, t0.when_archived, t0.when_unpublished, t0.u_env_inf, t0.m_env_inf, t0.when_updated, t0.when_created, t0.version, t0.fk_prior_env_id, t0.fk_app_id from fh_environment t0 join fh_application u1 on u1.id = t0.fk_app_id join fh_portfolio u2 on u2.id = u1.fk_portfolio_id join fh_group u3 on u3.fk_portfolio_id = u2.id join fh_person_group_link u4 on u4.fk_group_id = u3.id where t0.fk_app_id = ? and lower(t0.name) like ?  and t0.when_archived is null and u4.fk_person_id = ?\n\tio.ebean.config.dbplatform.SqlCodeTranslator.translate(SqlCodeTranslator.java:80) ~[ebean-api-12.16.0.jar:?]\n\tio.ebean.config.dbplatform.DatabasePlatform.translate(DatabasePlatform.java:245)

Steps to reproduce

@rbygrave - its line 304 in here: backend/mr-db-sql/src/main/kotlin/io/featurehub/db/services/EnvironmentSqlApi.kt - I want to return that data, its a Map<String, String> 🤷

[rbygrave]

Hmmm yes.

If I need to turn it into a VARCHAR(4000) or similar for Oracle

If we use @DbJson(length = 4000) as the mapping ... then we should see VARCHAR being used as the fallback - VARCHAR(4000). Ebean gives this a "logical type of json(4000)" and when that is mapped for Oracle that would map to a VARCHAR2(4000).

So a workaround today would be to change @DbJson to say @DbJson(length=4000) for all the mappings - bit of a PITA.

I'd suggest CLOB is a bad choice for the default type for Oracle (as you note). Some DB's treat CLOB just like LONGVARCHAR so we kind of get away with it on those DB's but not with Oracle. Hmmm - some more thinking needed here.

I believe we should consider adding a nicer way to configure the JSON fallback type so we have better control over this similar to the way we can configure the fallback type for UUID. For example, specify the fallback type for DbJson is varchar(4000) - explicit control is available via @DbJson(storage=... ,length=...) the storage and length attributes.

[rbygrave]

Noting Oracle 21c has a JSON database type so that should be the preferred type to use for Oracle 21c going forward.