Technical‎ > ‎Misc‎ > ‎

NLS_LENGTH_SEMANTICS -BYTE/CHAR


Free Hit Counters

Have you ever run into a problem when you are trying to insert data with special characters blowing up with ORA-12899: value too large for column.


The most surprising issue about this error is that encounter it when you might be trying to insert a string of length 25 into a table column of VARCHAR2(25) which should work like magic.


I will explain with an example:


Create a database Table abc_t.

CREATE TABLE abc_t (name_my VARCHAR2(25 BYTE));


Try inserting the 25 character value to abc_t.

INSERT INTO abc_t VALUES ('Höhenkirchen-Siegertsbrun');


This will blow up as though the length for the above string is 25 and column size is 25. Its because the byte size of the string above is 26 characters. The character causing the problem is ö .


Now one way to solve the problem would be to increase the size of the column to VARCHAR2(26). This is not a good fix.


The best solution to the problem is to define the table column

Create a database Table abc_t1.

CREATE TABLE abc_t (name_my VARCHAR2(25 CHAR));


Try inserting the 25 character value to abc_t.

INSERT INTO abc_t VALUES ('Höhenkirchen-Siegertsbrun');


This will work.


Now whats the difference. The difference is the way we defined the table VARCHAR2(25 BYTE/CHAR).


Now rather than taking care of this everytime when you define a database table you can set a Database Parameter called NLS_LENGTH_SEMANTICS.


You can query the value of this DB parameter using this query.

SELECT * FROM v$parameter WHERE name = Lower('NLS_LENGTH_SEMANTICS')


This value by default is set to BYTE so when you define a table column with VARCHAR2(25) it translates into VARCHAR2(25 BYTE).


But if you set this option to CHAR when you define a table column with VARCHAR2(25) it translates into VARCHAR2(25 CHAR).


Recommendation/Conclusion

For a System with Oracle Database where user will be keying in multilingual characters like French/Greek/German Characters one should set the NLS_LENGTH_SEMANTICS parameter to CHAR.



Comments