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.
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.
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
|Syntax||NLS_LENGTH_SEMANTICS = stringExample: NLS_LENGTH_SEMANTICS = ‘CHAR’|
|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.