Menu
I am supporting code that has below schema declaration:-
create table sample ( id number Primary key, username varchar2(100), ); I looked up on Oracle documentation & varchar2 has two declaration modes varchar2(n BYTE) and varchar2(n CHAR), If I don't specify explicitly BYTE or CHAR & just declare username varchar2(500) then will 500 BYTES be allocated or 500 characters?
thanks,
248
Varchar2(10) uses the current value of NLS_LENGTH_SEMANTICS to determine the limit for the string. If this is byte, then it's 10 bytes. If it's char, then it's 10 characters. In multibyte character sets these can be different!
VARCHAR2(20 BYTE) : Allows only the specified number of bytes to be stored in the column, regardless of how many characters this represents. VARCHAR2(20 CHAR) : Allows the specified number of characters to be stored in the column regardless of the number of bytes this equates to.
Default and minimum size is 1 byte. BYTE and CHAR have the same semantics as for VARCHAR2 . Fixed-length character data of length size characters. Maximum size is determined by the national character set definition, with an upper limit of 2000 bytes.
The VARCHAR2 data type specifies a variable-length character string in the database character set. You specify the database character set when you create your database. When you create a table with a VARCHAR2 column, you must specify the column length as size optionally followed by a length qualifier.
The default will be whatever your NLS_LENGTH_SEMANTICS parameter is set to. By default, that is BYTE to be consistent with older versions of Oracle where there was no option to use character length semantics. If you are defining your own schema and you are using a variable width character set (like AL32UTF8), I'd strongly recommend setting NLS_LENGTH_SEMANTICS to CHAR because you almost always intended to specify lengths in characters not in bytes.
60
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
