Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium


Typical varchar2 size

Posted on 2005-03-07
Medium Priority
Last Modified: 2012-08-13
I have two datatype questions for Oracle:

(1)  What's the best DT to represent an enum with just two values such binary | ASCII?

(2)  What's the typical size of a vachar2 for most strings.  I've seen schemas where just about every varchar2 has been set to 100, such as username and password fields.
Question by:lcor
LVL 25

Expert Comment

ID: 13481915
I guess people are kind of lax when setting varchar2 fields...as they don't take up space unless there is data.  Not true for key columns, but otherwise I guess I understand that.  I have never designed tables that way.  I know how long fields are (or are allowed to be) and use that to determine the data length.

Per someone else:

The most commonly seen design is to imitate the many Boolean-like flags that Oracle's data dictionary views use, selecting 'Y' for true and 'N' for false. However, to interact correctly with host environments, such as JDBC, OCCI, and other programming environments, it's better to select 0 for false and 1 for true so it can work correctly with the getBoolean and setBoolean functions.

LVL 11

Expert Comment

ID: 13483563
Varchar2 DataType takes 1 byte for each character. Like 'sujit123' should take 8 bytes of space. a typical VARCHAR2 can have maximum 32767 characters (so 32767 bytes).


Expert Comment

by:Mehul Shah
ID: 13483950
The Varchar2 Column can have maximum of 4000 bytes i.e. 4000 Characters.

 There is no typical size of a varchar2 Column. You need to decided the size based on the maximum length of the data that a column can contain.
Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.


Expert Comment

ID: 13484041
for your first question: to store data in binary form you can use raw or long raw:

 Raw binary data of length size bytes.
You must specify size for a RAW value.
 Maximum size is 2000 bytes
 32767 bytes
 Raw binary data of variable length. (not intrepreted by PL/SQL)
 2 Gigabytes - but now deprecated
 32760 bytes
Note this is smalller than the maximum width of a LONG RAW column
your second q: max size of varchar2 is 4000 bytes minimum is 1 in oracle 9 and max 32767 bytes minimum is 1 in pl/sql.

Accepted Solution

Jankovsky earned 2000 total points
ID: 13484211
There are different points of view to store flag or another two valued fields.
I'm used to use Varchar2(1) fields such as 'Y'/'N' or 'B'/'A' in your case. The columns takes the same 1 byte as NUMBER(1) field.
If the space taken in the database doesn't matter for you, you can use full meaning string 'BINARY','ASCII' to make data more understandable.

Size of Varchar2 fields should be designed according to desired constraints of the maximum lengths. It's possible to define unprecise domains for cases, when you aren't sure.

flags: length 1
short codes : length 10
codes: length 30
names: length 50
long names: length 100
descriptions: length 500
long descriptions: length 4000

Anyway for certainly defined fields I recommend use exact length specification.

About limits:

There is a limit of 4000 bytes in teh database and 32767 bytes in the PL/SQL precedures.

LVL 48

Expert Comment

ID: 13484453
VARCHAR2 is a long story.
It is with variable length.
It has a prefix for the length (1 byte if the length is less then 255 bytes, 3bytes with length more then 255 bytes).

If you define length for VARCHAR2 it is considered as maximal length.

You have to know that if you use nonEnglish letters and UTF8 character set then
1. One character can be stored in more then 1 byte (1 to 4 bytes).
The nonEnglish characters in the European Languages required in most cases 2 charactes.
This limited the effective number of characters in VARCHAR2 strings.

2. If you would like to guarantee thet you can store a definite number of characters in a variable you should use 'char' attribute.


name varchar2 (35);

means maximum 35 bytes for this variable, but in UTF characters sets NOT 35 characters. They could be less if you use nonEnglish letters.

name varchar2 (35 char);

means 35 characters, not 35 bytes. In the worst case name will be 140 bytes long.

Author Comment

ID: 13496644
Thanks, for everyone's input but Bob's answer is the closest to what I'll use.

Featured Post

NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.

580 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question