Precision of NVARCHAR2 column is 1.5 times lower than displayed by desc command

I wanted to update precision of an nvarchar2 column and came across an interesting case. I have tried to replicate it below:

create table test_table1
 (col1 nvarchar2(100));

table TEST_TABLE1 created.

desc test_table1
Name Null Type           
---- ---- -------------- 
COL1      NVARCHAR2(150)

We observed that the displayed precision of the nvarchar2 column is 1.5 times the specified precision. So if I have to replicate the precision of a column of type NVARCHAR2(375), then we need to declare it as NVARCHAR2(250).

I tried searching for an explanation but couldn't find any. Does anyone have any idea on this please? We are using Oracle-11g, connecting with Oracle SQL Developer.

NLS_SESSION_PARAMETERS

select * from NLS_SESSION_PARAMETERS;

+-------------------------+------------------------------+
|        PARAMETER        |            VALUE             |
+-------------------------+------------------------------+
| NLS_LANGUAGE            | AMERICAN                     |
| NLS_TERRITORY           | AMERICA                      |
| NLS_CURRENCY            | $                            |
| NLS_ISO_CURRENCY        | AMERICA                      |
| NLS_NUMERIC_CHARACTERS  | .,                           |
| NLS_CALENDAR            | GREGORIAN                    |
| NLS_DATE_FORMAT         | YYYY.MM.DD HH24:MI:SS        |
| NLS_DATE_LANGUAGE       | AMERICAN                     |
| NLS_SORT                | BINARY                       |
| NLS_TIME_FORMAT         | HH.MI.SSXFF AM               |
| NLS_TIMESTAMP_FORMAT    | DD-MON-RR HH.MI.SSXFF AM     |
| NLS_TIME_TZ_FORMAT      | HH.MI.SSXFF AM TZR           |
| NLS_TIMESTAMP_TZ_FORMAT | DD-MON-RR HH.MI.SSXFF AM TZR |
| NLS_DUAL_CURRENCY       | $                            |
| NLS_COMP                | BINARY                       |
| NLS_LENGTH_SEMANTICS    | BYTE                         |
| NLS_NCHAR_CONV_EXCP     | FALSE                        |
+-------------------------+------------------------------+

NLS_DATABASE_PARAMETERS

select * from NLS_DATABASE_PARAMETERS;
+-------------------------+----------------------------+
|        PARAMETER        |           VALUE            |
+-------------------------+----------------------------+
| NLS_LANGUAGE            | AMERICAN                   |
| NLS_TERRITORY           | SWITZERLAND                |
| NLS_CURRENCY            | SFr.                       |
| NLS_ISO_CURRENCY        | SWITZERLAND                |
| NLS_NUMERIC_CHARACTERS  | .'                         |
| NLS_CHARACTERSET        | AL32UTF8                   |
| NLS_CALENDAR            | GREGORIAN                  |
| NLS_DATE_FORMAT         | DD.MM.RR                   |
| NLS_DATE_LANGUAGE       | AMERICAN                   |
| NLS_SORT                | BINARY                     |
| NLS_TIME_FORMAT         | HH24:MI:SSXFF              |
| NLS_TIMESTAMP_FORMAT    | DD.MM.RR HH24:MI:SSXFF     |
| NLS_TIME_TZ_FORMAT      | HH24:MI:SSXFF TZR          |
| NLS_TIMESTAMP_TZ_FORMAT | DD.MM.RR HH24:MI:SSXFF TZR |
| NLS_DUAL_CURRENCY       | SF                         |
| NLS_COMP                | BINARY                     |
| NLS_LENGTH_SEMANTICS    | BYTE                       |
| NLS_NCHAR_CONV_EXCP     | FALSE                      |
| NLS_NCHAR_CHARACTERSET  | UTF8                       |
| NLS_RDBMS_VERSION       | 11.2.0.4.0                 |
+-------------------------+----------------------------+

nls_length_semantics

show parameter nls_length_semantics;
Show parameters query failed

Resolution

Thanks for the effort guys. The problem is indeed with SQL Developer.

I tried running it after connecting manually in Unix. Got correct result

create table test_table1
 (col1 nvarchar2(100));

Table created.

desc test_table1;
 Name  Null?   Type
 ---------------------------
 COL1          NVARCHAR2(100)

Thanks for both the answers. Thanks Paul for declaring the bounty and thanks jsapkota and Giova for taking your time and helping me out.

Best Answer

I'm quite sure that this is a problem of SQL Developer... Retry to desc table after issuing:

alter session set NLS_LENGTH_SEMANTICS='CHAR';

Is correct now? If not try to desc the table using sqlplus on the database server bypassing the Oracle Listener:

>export NLS_LANG=AMERICAN_SWITZERLAND.AL32UTF8 
>export ORACLE_SID=<YOURINSTANCESID>
>sqlplus / as sysdba
>sqlplus>desc owner.table

If the result is not correct, execute:

SQLPLUS>alter session set NLS_LENGTH_SEMANTICS='CHAR'; SQLPLUS>desc owner.table

If you can'tdo it on the server try the same with you sqldeveloper (but you will not bypass the listener):

>set NLS_LANG=AMERICAN_SWITZERLAND.AL32UTF8 
>C:\oracle\client\product\12.1.0\client\sqldeveloper\sqldeveloper\bin\sqldevelope‌rW.exe

Change the sqldevelope‌rW.exe path. Let me know When you declare a nvarchar2 field, the size is expressed ALWAYS in char. With desc command you are viewing the size in bytes. Sqldeveloper in some way is not able to display the vlaue in char. Also you can try to update your sqldevleoper version.

Giova

Note: I do not find Offical Oracle documentation about but this wiki explains better this behavior of sql developer:

Setting NLS LENGTH SEMANTICS

In the past I found the same problem with plsqldeveloper and other clients. Maybe the most updated versions works correctly.