Datasheet

16 Chapter 1

Introducing SQL
the column. Oracle has several built-in datatypes to store different kinds of data. In this
section, I will go over the built-in datatypes available in Oracle 11g. Detailed discussion on
datatypes as well as creating and maintaining tables are discussed in Chapter 6, “Creating
Tables and Constraints.”
When you create a table to store data in the database, you need to specify a datatype
for all the columns you define in the table. Oracle has many datatypes to suit application
requirements. Oracle 11g also supports ANSI and DB2 datatypes. The Oracle built-in
datatypes can be broadly classified as shown in Table 1.2.
TABLE 1.2 Oracle Built-in Datatypes
Category Datatypes
Character CHAR, NCHAR, VARCHAR2, NVARCHAR2
Number NUMBER, FLOAT, BINARY_FLOAT, BINARY_DOUBLE
Long and raw LONG, LONG RAW, RAW
Date and time DATE, TIMESTAMP, TIMESTAMP WITH TIME ZONE, TIMESTAMP WITH
LOCAL TIME ZONE, INTERVAL YEAR TO MONTH, INTERVAL DAY TO
SECOND
Large object CLOB, NCLOB, BCLOB, BFILE
Row ID ROWID, UROWID
In the following sections, I will discuss only a few of the built-in datatypes to get you
started with SQL. I discuss all the datatypes and their usage in detail in Chapter 6.
CHAR(<size>)
The CHAR datatype is a fixed-length alphanumeric string, which has a maximum length
in bytes (to specify length in characters, use the
CHAR keyword inside parentheses along with
a size; see Chapter 6). Data stored in CHAR columns is space-padded to fill the maximum
length. Its size can range from a minimum of 1 byte to a maximum of 2,000 bytes. The
default size is 1.
When you create a column using the CHAR datatype, the database will ensure that all
data placed in this column has the defined length. If the data is shorter than the defined
length, it is space-padded on the right to the specified length. If the data is longer, an error
is raised.
95127c01.indd 16 2/18/09 6:37:08 AM