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

Question

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 1

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?

Answer 2

it means ONLY one byte will be allocated per character - so if you're using multi-byte charsets, your 1 character won't fit

if you know you have to have at least room enough for 1 character, don't use the BYTE syntax unless you know exactly how much room you'll need to store that byte

when in doubt, use VARCHAR2(1 CHAR)

same thing answered here Difference between BYTE and CHAR in column datatypes

Also, in 12c the max for varchar2 is now 32k, not 4000. If you need more than that, use CLOB

in Oracle, don't use VARCHAR

Answer 3

The VARCHAR datatype is synonymous with the VARCHAR2 datatype. To avoid possible changes in behavior, always use the VARCHAR2 datatype to store variable-length character strings.

If your database runs on a single-byte character set (e.g. US7ASCII, WE8MSWIN1252 or WE8ISO8859P1) it does not make any difference whether you use VARCHAR2(x BYTE) or VARCHAR2(x CHAR).

It makes only a difference when your DB runs on multi-byte character set (e.g. AL32UTF8 or AL16UTF16). You can simply see it in this example:

CREATE TABLE my_table (
    VARCHAR2_byte VARCHAR2(1 BYTE), 
    VARCHAR2_char VARCHAR2(1 CHAR)
);

INSERT INTO my_table (VARCHAR2_char) VALUES ('€');
1 row created.

INSERT INTO my_table (VARCHAR2_char) VALUES ('ü');
1 row created.

INSERT INTO my_table (VARCHAR2_byte) VALUES ('€');
INSERT INTO my_table (VARCHAR2_byte) VALUES ('€')
Error at line 10
ORA-12899: value too large for column "MY_TABLE"."VARCHAR2_BYTE" (actual: 3, maximum: 1)

INSERT INTO my_table (VARCHAR2_byte) VALUES ('ü')
Error at line 11
ORA-12899: value too large for column "MY_TABLE"."VARCHAR2_BYTE" (actual: 2, maximum: 1)

VARCHAR2(1 CHAR) means you can store up to 1 character, no matter how many byte it has. In case of Unicode one character may occupy up to 4 bytes.

VARCHAR2(1 BYTE) means you can store a character which occupies max. 1 byte.

If you don't specify either BYTE or CHAR then the default is taken from NLS_LENGTH_SEMANTICS session parameter.

Unless you have Oracle 12c where you can set MAX_STRING_SIZE=EXTENDED the limit is VARCHAR2(4000 CHAR)

However, VARCHAR2(4000 CHAR) does not mean you are guaranteed to store up to 4000 characters. The limit is still 4000 bytes, so in worst case you may store only up to 1000 characters in such field.

See this example (€ in UTF-8 occupies 3 bytes):

CREATE TABLE my_table2(VARCHAR2_char VARCHAR2(4000 CHAR));

BEGIN
    INSERT INTO my_table2 VALUES ('€€€€€€€€€€');
    FOR i IN 1..7 LOOP
        UPDATE my_table2 SET VARCHAR2_char = VARCHAR2_char ||VARCHAR2_char;
    END LOOP;
END;
/

SELECT LENGTHB(VARCHAR2_char) , LENGTHC(VARCHAR2_char) FROM my_table2;

LENGTHB(VARCHAR2_CHAR) LENGTHC(VARCHAR2_CHAR)
---------------------- ----------------------
                  3840                   1280
1 row selected.


UPDATE my_table2 SET VARCHAR2_char = VARCHAR2_char ||VARCHAR2_char;

UPDATE my_table2 SET VARCHAR2_char = VARCHAR2_char ||VARCHAR2_char
Error at line 1
ORA-01489: result of string concatenation is too long

See also Examples and limits of BYTE and CHAR semantics usage (NLS_LENGTH_SEMANTICS) (Doc ID 144808.1)

Answer 4

To answer you first question:
Yes, it means that 1 byte allocates for 1 character. Look at this example

SQL> conn / as sysdba
Connected.
SQL> create table test (id number(10), v_char varchar2(10));

Table created.

SQL> insert into test values(11111111111,'darshan');
insert into test values(11111111111,'darshan')
*
ERROR at line 1:
ORA-01438: value larger than specified precision allows for this column


SQL> insert into test values(11111,'darshandarsh');
insert into test values(11111,'darshandarsh')
*
ERROR at line 1:
ORA-12899: value too large for column "SYS"."TEST"."V_CHAR" (actual: 12,
maximum: 10)


SQL> insert into test values(111,'Darshan');

1 row created.

SQL> 

And to answer your next one: The difference between varchar2 and varchar :

1. VARCHAR can store up to 2000 bytes of characters while VARCHAR2 can store up to 4000 bytes of characters.
2. If we declare datatype as VARCHAR then it will occupy space for NULL values, In case of VARCHAR2 datatype it will not occupy any space.