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.



|        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_TIME_TZ_FORMAT      | HH.MI.SSXFF AM TZR           |
| NLS_DUAL_CURRENCY       | $                            |
| NLS_COMP                | BINARY                       |
| NLS_LENGTH_SEMANTICS    | BYTE                         |
| NLS_NCHAR_CONV_EXCP     | FALSE                        |


|        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_TIME_TZ_FORMAT      | 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       |                 |


show parameter nls_length_semantics;
Show parameters query failed 


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.

  • @JSapkota - Thanks for looking at it. I understand that the actual value is 100 but somehow it is showing as 150 due to some NLS settings. I updated my question with both session parameter and database parameter values. Also the show parameter nls_length_semantics query is not working for me. I pasted that too in question.
    – Utsav
    Jan 14, 2016 at 10:27

2 Answers 2


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:

>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):


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.


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


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

  • You were spot on in assuming it was SQL Developer issuer. Unfortunately I am not supposed to changed the session parameter, but in Unix I got the correct result. Thanks.
    – Utsav
    Jan 15, 2016 at 7:02

The problem is in the NLS_NCHAR_CHARACTERSET = UTF8. I simulated the same table in a different database with NLS_NCHAR_CHARACTERSET = AL16UTF16. The describe statement shows the correct length as defined while creating table.

UTF8 can be 1, 2, 3 or 6 bytes / character, any AL32UTF8 character that is 4 bytes will be stored as 2 time 3 bytes in UTF8. If you want to store 1 byte character in UTF8 it takes 1.5 bytes (1*1.5=1.5).

For further details, see:

AL32UTF8 / UTF8 (Unicode) Database Character Set Implications [ID 788156.1]

  • This confirms that the mismatching view is because of sqldeveloper and/or NLS_LENGTH_SEMANTICS value (bytes and not char)
    – Giova
    Jan 14, 2016 at 18:33
  • @Giova. Have you tested it? i mean if you change the semantics and the length is consistent as define while creating table. I changed semantics and tested many times with utf8 it still shows the length 1.5 times larger. So i tested it by changing nchar_characterset to al16utf16 is the only next options available for nchar_characterset.
    – atokpas
    Jan 15, 2016 at 2:00
  • I tested it sjth some other tools that ade not vompletely aware of nvarchar2. I can send you a list later. I check sqldeveloper foc a f Metsljnk. This "issue" is not reported but this behavior, if expected, should be dicumented.... at the moment I don't hsve a csi in this scope to open a sr about this problem.
    – Giova
    Jan 15, 2016 at 6:53
  • @JSapkota - Thanks for the explanation on why UTF8 takes 1.5 times the size. Only doubt still in my mind is that I see NLS_NCHAR_CHARACTERSET UTF8 in my unix session too, but I am getting correct values when doing a describe on unix session, but not in SQL Developer.
    – Utsav
    Jan 15, 2016 at 7:08
  • 1
    @Utsav as Giova commented it must be the issue related to the SQL Developer. I also tested it with SQL*PLUS the length was correctly shown.
    – atokpas
    Jan 15, 2016 at 7:15

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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