Skip to Main Content

Oracle Database Free

Announcement

For appeals, questions and feedback, please email oracle-forums_moderators_us@oracle.com

oracle error ORA-01461 when persisting entity with special characters in absurdly specific circumstances

user-nehv6Jul 31 2023 — edited Jul 31 2023

This is a cross-post of https://github.com/quarkusio/quarkus/issues/35070 as it appears that I found a bug, or at least a very weird edge case with either the Oracle JDBC driver or the Oracle Free Database.

In some absurdly specific circumstances, trying to persist an entity containing the string Nêin will cause the following error when connected to an oracle database:

ORA-01461: can bind a LONG value only for insert into a LONG column

To reproduce this bug you need an Oracle Database Free and a JDBC driver version 23.2.0.0. Basically, executing a prepared statement as a batch with one value containing the string Nêin causes trouble, if the statement also updates some other entity and sets some other nullable boolean field to null in one entity of the batch, and non-null in the other entity.

It is difficult to explain what the circumstances are, because they are weirdly specific. If using a JDBC statement batch size of at least 2 and the Hibernate ORM, a reproducer can be as simple as:

MyEntity entityOk = new MyEntity(null, null); // some entity with a Boolean and a String field
MyEntity entityBorked = new MyEntity(true, "Nêin");

entityManager.persist(entityOk);
entityManager.persist(entityBorked);
entityManager.flush(); // ERROR! ORA-01461

Surprisingly, all these little changes fix the problem:

  • Using other special characters, e.g. stuff like "äöüßÄÖÜẞ💩"
  • Slightly changing the string's size, e.g. "Nêi" and "Nêin_" both work
  • Using the decomposite unicode normalization "Nêin" (e + 0x0302 "COMBINING CIRCUMFLEX ACCENT")
    instead of the composite form "Nêin" (0x00EA "LATIN SMALL LETTER E WITH CIRCUMFLEX")
  • Not having a JDBC batch size > 1, or flushing in-between the two inserts
  • Changing the order in which the two entities are persisted
  • Removing the random additional nullable boolean field
  • Not having the Boolean be null for one and not-null for the other entity,
    e.g. true->false or false->true makes the test pass
  • not having the first entity's string be null

Here is a reproducer using ~a prepared statement and Hibernate ORM~ pure JDBC: https://github.com/marko-bekhta/hibernate-test-case-templates/commit/663bf6c55a6bbad855c3361d2f1f504965f3108d
The linked Quarkus issue also contains a reproducer for Quarkus, which automatically starts a oracle-free database in docker for you.

Comments
Post Details
Added on Jul 31 2023
13 comments
896 views