True length of a String, as seen by Oracle

Question

So I am trying to save data to an Oracle database. I have a string:

Väste

(the name of a state somewhere).

When I do a .length() on it, I get 5, but when I save it to the database I get:

ORA-12899: value too large for column "dude"."POST_ADR"."STATE_CD" (actual: 6, maximum: 5)

So how do I get the "oracle" length?

Answer 1

You can, as others have shown, convert the Java string to a byte array using the Oracle database's character set and then get the length in bytes from that. That relies, however, on knowing what your database's character set is-- different databases will have different character sets which will result in different byte lengths for the same string in different character sets.

Assuming that your database is using a variable-width character set like UTF-8 (NLS_CHARACTERSET of AL32UTF8), you can also declare columns in Oracle based on the character length rather than the byte length. That can simplify your code since you can just check the character length of your string. It also simplifies the communication for users. It's generally hard for users to understand why a field can sometimes store 5 characters while other times it rejects a 2 character string depending on the characters that are part of the string (1 character in the UTF-8 character set can require up to 3 bytes of storage).

By default, when you declare a column

CREATE TABLE foo (
  col_name VARCHAR2(5)
);

that tells Oracle to allow up to 5 bytes of data. If you want to allow 5 characters of data regardless of the number of bytes, however, you can use character length semantics

CREATE TABLE foo (
  col_name VARCHAR2(5 CHAR)
);

Assuming you want to do this for all your tables while running your DDL, you can also set nls_length_semantics at the session level before running your DDL

ALTER SESSION SET nls_length_semantics = CHAR;

CREATE TABLE foo (
  col_name VARCHAR2(5)
);

creates a table with a column that allows up to 5 characters of data.