HiWhile creating a table, what is the difference between VARCHAR2(80) and VARCHAR2(80 BYTE)?

Questions by kkanmani

Showing Answers 1 - 7 of 7 Answers

geetha24

  • Feb 11th, 2007
 

Historically database columns which hold alphanumeric data have been defined using the number of bytes they store. This approach was fine as the number of bytes equated to the number of characters when using single-byte character sets. With the increasing use of multibyte character sets to support globalized databases comes the problem of bytes no longer equating to characters.Suppose we had a requirement for a table with an id and description column, where the description must hold up to a maximum of 20 characters.We then decide to make a multilingual version of our application and use the same table definition in a new instance with a multibyte character set. Everything works fine until we try of fill the column with 20 two-byte characters. All of a sudden the column is trying to store twice the data it was before and we have a problem.Oracle9i has solved this problem with the introduction of character and byte length semantics. When defining an alphanumeric column it is now possible to specify the length in 3 different ways: 1. VARCHAR2(20) 2. VARCHAR2(20 BYTE) 3. VARCHAR2(20 CHAR)Option 1 uses the default length semantics defined by the NLS_LENGTH_SEMANTICS parameter which defaults to BYTE. Option 2 allows only the specified number of bytes to be stored in the column, regardless of how many characters this represents. Option 3 allows the specified number of characters to be stored in the column regardless of the number of bytes this equates to.

  Was this answer useful?  Yes

psingla

  • Apr 28th, 2010
 

If system parameter NLS_LENGTH_SEMANTICS is set to default
then no difference

If system parameter NLS_LENGTH_SEMANTICS is changed from default behavior  BYTE to CHAR.
then VARCHAR2(80) means VARCHAR2(80 char)
each char takes 4 bytes so
VARCHAR2(80 char) can store 80*4=320 bytes of data
and VARCHAR2(80 bytes) can store 80 bytes of data

  Was this answer useful?  Yes

Give your answer:

If you think the above answer is not correct, Please select a reason and add your answer below.

 

Related Answered Questions

 

Related Open Questions