ORA-12899 when inserting from sqlplus, but ok with jdbc

Question

Inserting 'ééééé' into a column defined as varchar(5) runs fine from squirrel sql client (jdbc).
But doing the same insert from sqlplus fails with ORA-12899 (too long value).

NLS_CHARACTERSET is set to AL32UTF8
NLS_LENGTH_SEMANTICS is set to CHAR

It look like sqlplus is not honoring these settings...

Where should I look ?

Answer 1

I have the same character set:

SELECT value FROM nls_database_parameters WHERE parameter = 'NLS_CHARACTERSET';

Result:

VALUE
-----------
AL32UTF8

I have NLS_LENGTH_SEMANTICS set to BYTE, but I can override this option in this way:

CREATE TABLE encoding (name VARCHAR2(5 CHAR));
INSERT INTO encoding VALUES('ééééé');

1 row created.

My NLS_LANG is: AMERICAN_AMERICA.UTF8, because my server is encoded UTF8. But if I unset this environment variable:

unset NLS_LANG

and if i try again:

INSERT INTO encoding VALUES('ééééé');
ORA-12899; value too large for column "..."."..."."...." (actual: 10, maximum: 5)

so, yo can do a try and set your NLS_LANG variable, which is in the format

[NLS_LANGUAGE]_[NLS_TERRITORY].[NLS_CHARACTERSET]

And it would work. You can obtain this parameters from NLS_DATABASE_PARAMETERS.