Varchar2(N Byte|Char) Default -> Char or Byte

varchar2(n BYTE|CHAR) default - CHAR or BYTE

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.

What does it mean when the size of a VARCHAR2 in Oracle is declared as 1 byte?

You can declare columns/variables as varchar2(n CHAR) and varchar2(n byte).

n CHAR means the variable will hold n characters. In multi byte character sets you don't always know how many bytes you want to store, but you do want to garantee the storage of a certain amount of characters.

n bytes means simply the number of bytes you want to store.

varchar is deprecated. Do not use it.
What is the difference between varchar and varchar2?

ALTER statement: Why VARCHAR2(50 BYTE) instead of VARCHAR2(50 CHAR)?

Answering myself (thanks to the tip provided by this other answer):

I should have executed instead:

ALTER TABLE USERX.MY_TABLE MODIFY (LASTNAME VARCHAR2(50 CHAR));

(note the extra CHAR after 50)

Difference between VARCHAR2(10 CHAR) and NVARCHAR2(10)

The NVARCHAR2 datatype was introduced by Oracle for databases that want to use Unicode for some columns while keeping another character set for the rest of the database (which uses VARCHAR2). The NVARCHAR2 is a Unicode-only datatype.

One reason you may want to use NVARCHAR2 might be that your DB uses a non-Unicode character set and you still want to be able to store Unicode data for some columns without changing the primary character set. Another reason might be that you want to use two Unicode character set (AL32UTF8 for data that comes mostly from western Europe, AL16UTF16 for data that comes mostly from Asia for example) because different character sets won't store the same data equally efficiently.

Both columns in your example (Unicode VARCHAR2(10 CHAR) and NVARCHAR2(10)) would be able to store the same data, however the byte storage will be different. Some strings may be stored more efficiently in one or the other.

Note also that some features won't work with NVARCHAR2, see this SO question:

• Oracle Text will not work with NVARCHAR2. What else might be unavailable?

Liquibase VARCHAR2 length in chars

There isn't a built in way in liquibase to define a datatype as "varchar 255, char type" and have it output VARCHAR(255) on postgresql and VARCHAR2(255 CHAR) on oracle.

Liquibase is designed to be extendable (liquibase.org/extensions) and you should be able to override how the string "VARCHAR(255)" in the changelog is converted to a database-specific type but it will require some java coding.

Alternately, changelog parameters would allow you to dynamically add CHAR to the definition depending on the database. If you add <property name="varcharUnit" value="CHAR" dbms="oracle"/> to the top of your changelog, then anywhere in your changelog file you can use type="VARCHAR(255 ${varcharUnit})" and it will evaluate to "VARCHAR(255 CHAR)" on oracle and VARCHAR(255) everywhere else. It is more verbose and you need to remember to always add the variable compared to the extension method but it is more straightforward.

VARCHAR2(1000) limited to 100 chars only

Generally, when the string to store in a varchar field is too long, it is cropped silently. Anyway when there is an error message, it is generally specific. The error seems to be related to a operation on a string (String.substring()?).

Furthermore, even when the string is encoded in UTF-8, the ratio characters/bytes shouldn't be that low.

You really should put the code sample where your error occurs in you question and the string causing this and also have a closer look at the stacktrace to see where the error occurs.

From the code you posted in your chat, I can see this line of code :

String str1 = str.substring(index1 + 1, index2); 

You check that index1 and index2 are different than -1 but you don't check if (index1 + 1) >= index2 which makes your code crash.

Try this with str = "*]ab=null[" (which length is under 100 characters) but you can also get the error with a longer string such as "osh]] [ = null ]Clipers: RRR was removed by user and customer called in to have it since it was an RRT".

Once again the size of the string doesn't matter, only the content!!!

You can reproduce your problem is a closing square bracket (]) before an opening one([) and between them an equal sign (=) followed (directly or not) by the "null" string.

Oracle modify column not allowed because of the length of value

By default varchar2(1) is treated as varchar with the length of 1 byte. However, some encodings require more than 1 byte to store a character (UTF-8, UTF-16, etc.). Probably there's a character in the table that requires more than one byte, so the size of varchar2(1) is simply not enough.

varchar2(1 char) forces Oracle count the size in characters, not bytes, so that fixes the issue.

SQL Developer fills remaining space in char with spaces

You are operating under a misconception; it has nothing to do with SQL Developer.

A CHAR data-type is a fixed-length string; if you do not provide a string of the full length then Oracle will right-pad the string with space (ASCII 32) characters until it has the correct length.

From the documentation:

CHAR Datatype

The CHAR datatype stores fixed-length character strings. When you create a table with a CHAR column, you must specify a string length (in bytes or characters) between 1 and 2000 bytes for the CHAR column width. The default is 1 byte. Oracle then guarantees that:

• When you insert or update a row in the table, the value for the CHAR column has the fixed length.
• If you give a shorter value, then the value is blank-padded to the fixed length.
• If a value is too large, Oracle Database returns an error.

Oracle Database compares CHAR values using blank-padded comparison semantics.

To solve this, do not use CHAR for variable length strings and use VARCHAR2 instead.

VARCHAR2 and VARCHAR Datatypes

The VARCHAR2 datatype stores variable-length character strings. When you create a table with a VARCHAR2 column, you specify a maximum string length (in bytes or characters) between 1 and 4000 bytes for the VARCHAR2 column. For each row, Oracle Database stores each value in the column as a variable-length field unless a value exceeds the column's maximum length, in which case Oracle Database returns an error. Using VARCHAR2 and VARCHAR saves on space used by the table.

---

Related Topics