I have java code trimming a UTF-8 string to the size of my Oracle ( column which ends up throwing an error because java and Oracle see the string as different byte lengths. I've verified my NLS_CHARACTERSET parameter in Oracle is 'UTF8'.

I wrote a test which illustrates my issue below using the unicode chipmunk emoji (🐿️)

public void test() throws UnsupportedEncodingException, SQLException {
    String squirrel = "\uD83D\uDC3F\uFE0F";
    int squirrelByteLength = squirrel.getBytes("UTF-8").length; //this is 7
    Connection connection = dataSource.getConnection();

    connection.prepareStatement("drop table temp").execute();

    connection.prepareStatement("create table temp (foo varchar2(" + String.valueOf(squirrelByteLength) + "))").execute();

    PreparedStatement statement = connection.prepareStatement("insert into temp (foo) values (?)");
    statement.setString(1, squirrel);

This fails on the last line of the test with the following message:

ORA-12899: value too large for column
"MYSCHEMA"."TEMP"."FOO" (actual: 9, maximum: 7)

The setting of NLS_LENGTH_SEMANTICS is BYTE. Unfortunately, I cannot change this as it is a legacy system. I'm not interested in increasing the column size, just reliably being able to predict the Oracle size of a string.

  • Sadly I'm seeing conflicting reports on the internet about how many bytes this should be. Some say 7, some say 8, some say 12 (???). What happens if you declare the Oracle field as 8 instead of 7. Does it work then? I realize that doesn't explicitly answer your question of why but it may give some answer for you.
    – jcolebrand
    May 19 '17 at 22:58

What follows is my speculation.

Java Strings are internally represented using the UTF-16 encoding. When you getBytes("UTF-8") Java converts between the two encodings, and you probably use an up-to-date Java platform.

When you attempt to store a Java String in the database, Oracle also performs conversion between the Java native UTF-16 and the database character set as determined by NLS_CHARACTERSET.

The chipmunk character was approved as part of the Unicode standard in 2014 (according to the page you linked), while the latest release of Oracle 11g rel.2 was published in 2013.

One might assume that Oracle uses a different or outdated character conversion algorithm so the byte representation of 🐿️) on the server (9 bytes long) is different than what getBytes() returns on the client (7 bytes).

I guess to resolve this issue you could upgrade your Oracle server or use UTF-16 as the database character set.

  • That resolved the issue. My oracle 11g was using jdk 1.6.0_141 while the 12 instance is using jdk 1.8.0_121
    – agradl
    May 19 '17 at 16:22
  • 3
    Please mark the question as answered so the next person knows this worked :)
    – jcolebrand
    May 19 '17 at 22:58
  • I spoke too soon, I'm investigating further to confirm my suspicion - it wasn't related to the oracle version...stay tuned
    – agradl
    May 22 '17 at 1:19

The issue is with Oracle's handling of Supplementary unicode characters when NLS_LENGTH_SEMANTICS is UTF8.

From the documentation (emphasis added).

The UTF8 character set encodes characters in one, two, or three bytes. It is for ASCII-based platforms.

Supplementary characters inserted into a UTF8 database do not corrupt the data in the database. A supplementary character is treated as two separate, user-defined characters that occupy 6 bytes. Oracle recommends that you switch to AL32UTF8 for full support of supplementary characters in the database character set.

Additionally, the last code point in the squirrel string is a variation selector and optional. I saw this using a unicode character inspector

After changing the database's NLS_CHARACTERSET parameter to AL32UTF8 the test passed.

  • And the chipmunks rejoice! Oct 8 '20 at 14:10

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.