Database Reference
In-Depth Information
Chapter 12
Datatypes
Choosing the right datatype seems so easy and straightforward, but many times I see it done incorrectly. The most
basic decision—what type you use to store your data in—will have repercussions on your applications and data for
years to come. Thus, choosing the appropriate datatype is paramount. It is also hard to change after the fact—in other
words, once you implement it, you might be stuck with it for quite a while.
In this chapter, we'll take a look at all of the Oracle basic datatypes available and discuss how they are
implemented and when each might be appropriate to use. We won't examine user-defined datatypes as they're simply
compound objects derived from the built-in Oracle datatypes. We'll investigate what happens when you use the
wrong datatype for the job—or even just the wrong parameters to the datatype (length, precision, scale, and so on).
By the end of this chapter, you'll have an understanding of the types available to you, how they're implemented,
when to use each type and, as important, why using the right type for the job is key.
An Overview of Oracle Datatypes
Oracle provides 22 different SQL datatypes. Briefly, they are as follows:
CHAR
: A fixed-length character string that will be blank padded with spaces to its maximum
length. A non-null
CHAR(10)
will always contain 10
bytes
of information using the default
National Language Support (NLS) settings. We will cover NLS implications in more detail
shortly. A
CHAR
field may store up to 2,000
bytes
of information.
•
NCHAR
: A fixed-length character string that contains
UNICODE
formatted data. Unicode is a
character-encoding standard developed by the Unicode Consortium with the aim of providing
a universal way of encoding characters of any language, regardless of the computer system
or platform being used. The
NCHAR
type allows a database to contain data in two different
character sets: the
CHAR
type and
NCHAR
type use the database's character set and the national
character set, respectively. A non-null
NCHAR(10)
will always contain 10
characters
of
information (note that it differs from the
CHAR
type in this respect). An
NCHAR
field may store up
to 2,000
bytes
of information.
•
VARCHAR2
: Also currently synonymous with
VARCHAR
. This is a variable length character
string that differs from the
CHAR
type in that it is not blank padded to its maximum length.
A
VARCHAR2(10)
may contain between 0 and 10
bytes
of information using the default NLS
settings. A
VARCHAR2
may store up to 4,000 bytes of information. Starting with Oracle 12
c
,
a
VARCHAR2
can be configured to store up to 32,767 bytes of information (see the “Extended
Datatypes” section in this chapter for further details).
•
