3

I am very new to oracle and today I found about the data type VARCHAR2, and I wanted to learn more about it and google the datatype where I met the problem.

I have gone through few articles about the datatype, and I found out some direct opposite descriptions for VARCHAR2.

DESCRIPTION 1:

  • When you create a table with a VARCHAR2 column, you specify a maximum column length (in bytes, not characters) between 1 and 2000 for the VARCHAR2 column(article)

DESCRIPTION 2:

  • you can store up to 4000 characters in a VARCHAR2 column. (article)

As you can see it is bit confusing. Is VARCHAR2 is to specify the maximum column length or maximum characters length? Somebody please explain me which one is the correct one?

Community
  • 1
  • 1
Ramesh
  • 2,297
  • 2
  • 20
  • 42

3 Answers3

5

It depends on your Oracle version, but both articles are mostly incorrect.

When you DECLARE the column, you can either declare the stated length EXPLICITLY as either bytes or characters, or IMPLICITLY using your session's default.

Also, the maximum length is 4000 bytes, NOT characters. Even if you declare VARCHAR2(4000 CHAR), the column cannot store more than 4000 BYTES. It will store 4000 characters if they are all single-byte, otherwise it will store fewer than 4000 characters.

1

DESCRIPTION 2:

  • you can store up to 4000 characters in a VARCHAR2 column.

This is correct

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.

Priyank Panchal
  • 119
  • 6
  • But the description 1 is from the oracle documentation – Ramesh Aug 15 '18 at 05:14
  • @Ramesh - OK, your comment above made me follow the first link. It is to Oracle 7. That probably means the limit (30 years ago or whenever that was) was indeed 2000 bytes - and perhaps you couldn't declare the length in characters. Not sure when both of these things changed, but it's at least since Oracle 10.2. I use Oracle 12.2, and that is not even the most current version. And in Oracle there are huge changes from one version to the next. –  Aug 15 '18 at 05:17
0

=> varchar2 datatype is same as varchar datatype. => its datatype with variable lengh. Ex. "name varchar2(20)" and pass the value of name is "Ram" so, LENGTH(name) is 3 NOT 20. => its internal datatype managed by oracle server only. => even if, you declare varchar oracle implicitely converts to into varchar2

Bhumik Mandli
  • 1