What exactly means that the size of the VARCHAR2 field is declared as 1 BYTE? (in Oracle DB)

Question

I am not so in the database and I have the following doubts.

I know that I can declare a varchar2 field using the number of the character that it can contain.

But in the Oracle database I'm working on, I found that a field (named PDF) is defined as:

 VARCHAR2(1 BYTE) 

What exactly does it mean? How much does it contain?

Another doubt: what exactly is the difference between the VARCHAR field and the VARCHAR2 field?

Tnx

Answer 1

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

n CHAR means that the variable will contain n characters. In multibyte character sets, you do not always know how many bytes you want to keep, but you want to guarantee storage of a certain number of characters.

n bytes means just the number of bytes you want to keep.

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

Answer 2

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

If your database works with a single-byte character set (for example, US7ASCII , WE8MSWIN1252 or WE8ISO8859P1 ), it does not matter if you VARCHAR2(x BYTE) or VARCHAR2(x CHAR) .

This only matters when your database is working with a multibyte character set (for example, AL32UTF8 or AL16UTF16 ). You can just see this 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 that you can store up to 1 character, no matter how many bytes it has. In the case of Unicode, one character can occupy up to 4 bytes.

VARCHAR2(1 BYTE) means that you can save a character that takes up max. 1 byte

If you do not specify either BYTE or CHAR then the default value is taken from the NLS_LENGTH_SEMANTICS session NLS_LENGTH_SEMANTICS .

If you do not have Oracle 12c, where you can set MAX_STRING_SIZE=EXTENDED limit will be VARCHAR2(4000 CHAR)

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

Check out this example ( € in UTF-8 takes 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 Limitations of Using BYTE and CHAR Semantics (NLS_LENGTH_SEMANTICS) (Document ID 144808.1)

Answer 3

To answer the first question:
Yes, this means that 1 byte is allocated 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 the following question: Difference between varchar2 and varchar :

• varchar can store up to 2000 bytes characters, and varchar2 can store up to 4000 bytes characters.
• If we declare the data type as varchar , then it will take its place for NULL values . In the case of varchar2 datatype, it will not occupy any space.

Answer 4

this means that only one byte will be allocated for each character - therefore, if you use multi-byte encodings, your 1 character will not match

if you know that you should have at least enough space for 1 character, do not use the BYTE syntax unless you know exactly how much space you will need to store this byte

if in doubt, use VARCHAR2 (1 CHAR)

same answer here difference between BYTE and CHAR in column types

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

in Oracle, do not use VARCHAR

Answer 5

Maximum memory allocation in SQL.

 VARCHAR : 4000 BYTE VARCHAR2 : 4000 BYTE 

Now when we declare VARCHAR2(1 BYTE) or VARCHAR2(1) . This means that it can store up to 1 character.

More details