Blob insert/update in jooq

Question

Getting "error occurred during batching: ORA-00933 SQL command not properly ended.

I'm trying to update/insert byte array in Oracle BLOB column using jooq syntax as follows:-

Map<Field<Object>, Object> fieldValueMap = new HashMap<>();
fieldValueMap.put(field("BLOB_COLUMN"), "test".getBytes());

Query = DSLContext.update(table(tablename)).set(fieldValueMap).where(condition)

Formed query for blob column as follows:-

Update tablename set BLOB_COLUMN = X'74657374' where condition.

Please help with the above issue.

Answer 1

X'74657374' is the default rendering of bytes[] inline literals for unknown dialects, as well as a few known dialects, including e.g. H2, HSQLDB, MariaDB, MySQL, SQLite. If you had used the SQLDialect.ORACLE dialect, then you'd have gotten something like hextoraw('74657374') as generated SQL, which wouldn't produce the error you've seen.

But you probably don't want to get inline literals anyway. This probably happened because you either:

• Used static statements
• Explicitly used inline literals