Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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 ?

share|improve this question

1 Answer 1

up vote 2 down vote accepted

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.

share|improve this answer
1  
Thanks. In fact, I had to export a NLS_LANG=".AL32UTF8" environment variable in my bash to get sqlplus using UTF8. LANG was set to fr_FR.UTF-8, and I thought it was enough, but it was not. –  alci Jan 10 at 17:09

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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