NLS Settings in SQL Developer

NLS Settings in SQL Developer can be out of sync with database defaults.

By default, in SQL Developer, the NLS Length is set to byte.

This can lead to unexpected behavior.

In database NLS Length is set to CHAR:

SELECT 'NLS_DATABASE_PARAMETERS' as param_source, parameter, value FROM NLS_DATABASE_PARAMETERS 

WHERE PARAMETER='NLS_LENGTH_SEMANTICS'

UNION ALL

SELECT 'NLS_INSTANCE_PARAMETERS' as param_source, parameter, value FROM NLS_INSTANCE_PARAMETERS 

WHERE PARAMETER='NLS_LENGTH_SEMANTICS'

UNION ALL

SELECT 'NLS_SESSION_PARAMETERS' as param_source, parameter, value FROM NLS_SESSION_PARAMETERS 

WHERE PARAMETER='NLS_LENGTH_SEMANTICS';

PARAM_SOURCE                   PARAMETER                VALUE

------------------------------ ------------------------ ------

NLS_DATABASE_PARAMETERS        NLS_LENGTH_SEMANTICS     CHAR

NLS_INSTANCE_PARAMETERS        NLS_LENGTH_SEMANTICS     CHAR

NLS_SESSION_PARAMETERS         NLS_LENGTH_SEMANTICS     CHAR

Consider command 

ALTER TABLE MY_TABLE MODIFY MY_COLUMN VARCHAR2(400);

In SQL DEVELOPER it will be translated to: 

ALTER TABLE MY_TABLE MODIFY MY_COLUMN VARCHAR2(400 BYTE);

SELECT char_used, char_length, data_length 

  FROM USER_TAB_COLUMNS 

 WHERE table_name = 'MY_TABLE'

   AND column_name = 'MY_COLUMN'

char_used char_length data_length 

--------- ----------- ------------

B           200          200

But, when running same command in sqlplus:

ALTER TABLE MY_TABLE MODIFY MY_COLUMN VARCHAR2(200);

SELECT char_used, char_length, data_length 

  FROM USER_TAB_COLUMNS 

 WHERE table_name = 'MY_TABLE'

   AND column_name = 'MY_COLUMN';

char_used char_length data_length 

--------- ----------- ------------

C           200          800

To fix this behaviour:

Option 1. Add CHAR to ALTER TABLE statements:

ALTER TABLE MY_TABLE MODIFY MY_COLUMN VARCHAR2(400 CHAR);

Option 2. Change in SQL Developer NLS Length to CHAR.

Tools -> Preferences -> Database -> NLS -> Length -> CHAR