Weird behaviour of DBA client tools with database NLS_LENGTH_SEMANTICS settings
SQL Developer and PL/SQL Developer show some weird behaviour when dealing with a unicode Oracle database.
NLS_LENGTH_SEMANTICS is available as database and session parameter. In a database which is running charset AL32UTF8 or another multibyte charset, this parameter is usually set to CHAR database-wise, which results in new char columns being created with n characters length instead of n bytes length, as it would be with a BYTE setting of this parameter. The default database and session setting is BYTE, unfortunately, obviously for backwards compatibility reasons. See also: Show oracle charset
So if you don't adjust your client and / or database settings and create a new column without specifying the semantics, like:
you get this table:
CHAR_USED = B reads as BYTE semantics are used.
You can add 20 single-byte characters in the FNAME column:
But if you insert multi-byte characters, you get:
This teaches us to create tables and their columns with CHAR semantics.
But how can we make sures this is the default for your session ?
First, you can check the database setting. In a unicode database, the parameter ought to be set to CHAR:
Let's check it in sqlplus:
Now, let's check in SQL Developer:
Yes, you are connected to the same database... but SQL Developer rewrites the database output!
This is because the tools preferences are set to BYTE.
You can check this in Tools/Preferences/Database/NLS:
Once set to CHAR, SQL Developer has session settings for char semantics and new columns are created in char semantics.
Also, the above query has correct output:
In PL/SQL Developer 7.1, the SQL output is correct:
But there is a report in "Reports/DBA/NLS Database Parameters", which still states
for an unknown reason. Probably a bug, in version 9 history there are a lot of related entries:
[ PL/SQL Developer - News|http://www.allroundautomations.com/plsqldevnew.html]