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

sql oracle varchar sqldatatypes
AndreaNobili picture AndreaNobili · Jun 16, 2015 · Viewed 112.8k times · Source

I know that I can declare a varchar2 using the number of the characters that it should be able to contain.

However, in an Oracle database on which I am working, I found that a field (named PDF) is defined as follows:

VARCHAR2(1 BYTE)

What does this mean? How many characters can it contain?

Another, related question: What is the difference between a VARCHAR and a VARCHAR2?

Answer

Rene picture Rene · Jun 16, 2015

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?

Related questions

Difference between BYTE and CHAR in column datatypes

In Oracle, what is the difference between : CREATE TABLE CLIENT ( NAME VARCHAR2(11 BYTE), ID_CLIENT NUMBER ) and CREATE TABLE CLIENT ( NAME VARCHAR2(11 CHAR), -- or even VARCHAR2(11) ID_CLIENT NUMBER )

Read More
Finding rows that don't contain numeric data in Oracle

I am trying to locate some problematic records in a very large Oracle table. The column should contain all numeric data even though it is a varchar2 column. I need to find the records which don't contain numeric data (The …

Read More
alternative to listagg in Oracle?

listagg is a function introduced in Oracle 11.2! now this function is bugging us allot, we are migrating from MySQL to Oracle and we have this query: SELECT p_id, MAX(registered) AS registered, listagg(MESSAGE, ' ') within GROUP (ORDER …

Read More