?
Solved

Typical varchar2 size

Posted on 2005-03-07
7
Medium Priority
?
10,124 Views
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.
0
Comment
Question by:lcor
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
7 Comments
 
LVL 25

Expert Comment

by:jrb1
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.

http://builder.com.com/5100-6388_14-5229553.html
0
 
LVL 11

Expert Comment

by:sujit_kumar
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).

Sujit
0
 
LVL 7

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.
0
Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

 

Expert Comment

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

RAW(size)
 Raw binary data of length size bytes.
You must specify size for a RAW value.
 Maximum size is 2000 bytes
 32767 bytes
   
LONG RAW
 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.
0
 
LVL 6

Accepted Solution

by:
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.

Bob
0
 
LVL 48

Expert Comment

by:schwertner
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.

E.g.:

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.
0
 

Author Comment

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

Featured Post

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

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

Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
Suggested Courses

801 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