-
Title
NVARCHAR2 datatype field size is doubled after creation -
Description
SQL Navigator
After creating a table with a NVARCHAR2 datatype the field size is doubled from 50 to 100. -
Resolution
A size of NVARCHAR2 (and NCHAR) datatypes columns is always defined as CHAR, as opposed to VARCHAR2 (and CHAR), which can be (since 9i) either BYTE or CHAR.
NVARCHAR2 in most databases is stores this datatype by default in NLS_NCHAR_CHARACTERSET = AL16UTF16. This is a fixed 2 byte character set.
During creation of a column defined as NVARCHAR2 (50) the column has a size of 50 characters by default. This is translated internally by Oracle to 100 bytes (50 characters of 2 byte size).
The issue for sqlnav4 is that it doesn't do a translation back to characters. A possible workaround for this issue could be to alter the NLS_LENGTH_SEMANTICS parameter from BYTE to CHAR in the databases init.ora (can be done dynamically in Oracle 9i). Other wise it is a manual change of a script.