NLS_CHARACTERSET and SQL Developer

user652526 wrote:

 

I have an issue with counting the length of a string with multibyte characters in it.

 

System setup:
Oracle 19c version 19.3.0.0.0 - no patching
SQL Developer Version 19.1.0.094
NLS PARAMTERS

"Parameter"	"Value"
"NLS_CALENDAR"	"GREGORIAN"
"NLS_CHARACTERSET"	"AL32UTF8"
"NLS_COMP"	"BINARY"
"NLS_CURRENCY"	"$"
"NLS_DATE_FORMAT"	"DD-MON-RRRR HH24:MI:SS"
"NLS_DATE_LANGUAGE"	"AMERICAN"
"NLS_DUAL_CURRENCY"	"$"
"NLS_ISO_CURRENCY"	"AMERICA"
"NLS_LANGUAGE"	"AMERICAN"
"NLS_LENGTH_SEMANTICS"	"BYTE"
"NLS_NCHAR_CHARACTERSET"	"AL16UTF16"
"NLS_NCHAR_CONV_EXCP"	"FALSE"
"NLS_NUMERIC_CHARACTERS"	".,"
"NLS_SORT"	"BINARY"
"NLS_TERRITORY"	"AMERICA"
"NLS_TIMESTAMP_FORMAT"	"DD-MON-RR HH.MI.SSXFF AM"
"NLS_TIMESTAMP_TZ_FORMAT"	"DD-MON-RR HH.MI.SSXFF AM TZR"
"NLS_TIME_FORMAT"	"HH.MI.SSXFF AM"
"NLS_TIME_TZ_FORMAT"	"HH.MI.SSXFF AM TZR"

When I execute the following statement in SQL Developer:

 

 

It returns 10.

 

When I execute it from SQL PLUS command line it returns 15.

Could someone explain why the difference in the value returned for the count?

 

Thanks in advance!

 

John

Easy.

The NLS parameters can be set at multiple levels but are ultimately controlled by the client.  Part of the design of SQL Dev is that it sets its own defaults.  On the other hand, sqlplus, absent any intervention by the user, simply inherits the values from the database's settings.

From what operating system did you run sqlplus?

I was able to get the same result in Centos 6:

[cormaco@vbox1 ~]$ sqlplus hr/hr@pdb1

SQL*Plus: Release 18.0.0.0.0 - Production on Wed Sep 2 16:11:37 2020
Version 18.4.0.0.0

Copyright (c) 1982, 2018, Oracle.  All rights reserved.

Letzte erfolgreiche Anmeldezeit: Mi Sep 02 2020 16:06:40 +02:00

Verbunden mit: 
Oracle Database 18c Express Edition Release 18.0.0.0.0 - Production
Version 18.4.0.0.0

SQL> select length('ä2ää13ääXX') from dual; 

LENGTH('??2????13????XX')
-------------------------
               15

It can be fixed by setting NLS_LANG:

[cormaco@vbox1 ~]$ export NLS_LANG=GERMAN_GERMANY.UTF8
[cormaco@vbox1 ~]$ sqlplus hr/hr@pdb1

SQL*Plus: Release 18.0.0.0.0 - Production on Mi Sep 2 16:12:39 2020
Version 18.4.0.0.0

Copyright (c) 1982, 2018, Oracle.  All rights reserved.

Letzte erfolgreiche Anmeldezeit: Mi Sep 02 2020 16:11:37 +02:00

Verbunden mit: 
Oracle Database 18c Express Edition Release 18.0.0.0.0 - Production
Version 18.4.0.0.0

SQL> select length('ä2ää13ääXX') from dual; 

LENGTH('Ä2ÄÄ13ÄÄXX')
--------------------
          10

Important is setting UTF8 as the client characterset, you can choose any language and territory.

"Part of the design of SQL Dev is that it sets its own defaults"
This is really something that falls out of being a java app using the oracle JDBC driver..where those properties are set automatically depending on the OS region/locale environments.