NLS_LENGTH_SEMANTICS Affects BYTE CHAR Length Semantics

Recently I worked on a case regarding the BYTE and CHAR length semantics issue, after performing root cause analysis, we found out the victim was Oracle SQL Developer, and the murder was an Oracle parameter – NLS_LENGTH_SEMANTICS.

Here is the detail of the story.

Background:

1)      Customer had a request to create some partitioned tables as usually.

2)      DBA created those tables according to customer’s request.

3)      Customer came back with some application error logs, which indicated the data in those tables were not correct as they have different length as expected.

4)      DBA checked those tables, data displayed on the screen are correct, however, it is abnormal when using length function from application side.

Analysis:

1)      Confirmed with DBA who performed the deployment of those partitioned tables, it was deployed by pure SQL scripts in tool SQL Developer.

2)      Customer and DBA are from different countries with different type of NLS setting with their support tools and applications.

3)      Re-deployed those tables in SQLPLUS, everything worked perfectly

4)      For test purpose, re-deployed those tables in Oracle SQL Developer, application raised the same error.

5)      Checked all NLS settings from database and support tools, observed the database parameter NLS_LENGTH_SEMANTICS has different values: One is using Oracle default value – BYTE, while the other one use the other value – CHAR, which is not default.

6)      DBA adjusted the NLS setting in Oracle SQL Developer, especially setting the value of NLS_LENGTH_SEMANTICS to CHAR, in order to match customer’s environment.  

7)      Recreated those tables via SQLPLUS and SQL Developer, all worked perfectly.

 

Pains and Gains

1)      Refer to Oracle database documentation which has clear information of parameter NLS_LENGTH_SEMANTICS with default value ’BYTE’

2)      Document as below

Property Description
Parameter type String
Syntax NLS_LENGTH_SEMANTICS = stringExample: NLS_LENGTH_SEMANTICS = ‘CHAR’
Default value BYTE
Modifiable ALTER SESSION
Range of values BYTE | CHAR

NLS_LENGTH_SEMANTICS enables you to create CHAR and VARCHAR2 columns using either byte or character length semantics. Existing columns are not affected.

NCHAR, NVARCHAR2, CLOB, and NCLOB columns are always character-based. You may be required to use byte semantics in order to maintain compatibility with existing applications.

NLS_LENGTH_SEMANTICS does not apply to tables in the SYS schema.

In general, like many DBA’s said, Oracle SQL Developer and other 3rd party tools are flexible and convenient to improve developers and DBA’s in everyday life,  however, if we could keep it in mind that the difference and relationship between database side and application side, our life will be better and more relax.

Tags: , ,

One Response to “NLS_LENGTH_SEMANTICS Affects BYTE CHAR Length Semantics”

  1. Hermine Says:

    always i used to read smaller content that as well clear their
    motive, and that is also happening with this post which
    I am reading at this place.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s


%d bloggers like this: