6

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.

  • @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 '16 at 10:27
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:

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

  • 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 '16 at 7:02
4
+200

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 '16 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. – JSapkota Jan 15 '16 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 '16 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 '16 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. – JSapkota Jan 15 '16 at 7:15

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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