Recently a fellow database architect claimed that in Oracle the type VARCHAR2(255) means a string of 255 bytes, not characters. There is not much difference between the two in the English-speaking world. It matters though if you want to handle people with names like Kołłątaj. (Not that Hugo Kołłątaj – a famous Polish 18th century politician – would ever use any of our systems, but he became our byword for all non-pure-ASCII names). I was very surprised by what the architect said and I decided to further investigate the matter.
VARCHAR2: is it bytes or characters?
In Oracle, you can define the type of a string column as:
- VARCHAR2(15 BYTE) – the column can hold string of up to 15 bytes,
- VARCHAR2(15 CHAR) – the column can hold string of up to 15 characters,
- simply VARCHAR2(15) – the semantics depend on the value of the NLS_LENGTH_SEMANTICS parameter of the session creating the table. By default yout table is created with byte semantics.
However, Oracle recommends to use VARCHAR2(15 CHAR) if you want the char semantics.
Set NLS_LENGTH_SEMANTICS parameter for the session
To find out what is the setting for the current session:
select * from v$nls_parameters where parameter = 'NLS_LENGTH_SEMANTICS';
To change the parameter for the current session:
alter session set nls_length_semantics=char; -- or byte
Note that this doesn't change the length of the existing columns, only for columns in the new tables. You can also change NLS_LENGTH_SEMANTICS parameter globally for the database but we won't cover it here as it's not really recommended by Oracle.
Vertabelo supports all three ways of defining VARCHAR columns in Oracle.
Try out our online modeling tool: design your Oracle database in Vertabelo.