Database Reference
In-Depth Information
•
NVARCHAR2
: A variable length character string that contains
UNICODE
formatted data. An
NVARCHAR2(10)
may contain between 0 and 10
characters
of information. An
NVARCHAR2
may store up to 4,000 bytes of information. Starting with Oracle 12
c
, an
NVARCHAR2
can be
configured to store up to 32,767 bytes of information (see the “Extended Datatypes” section in
this chapter for further details).
•
RAW
: A variable length binary datatype, meaning that no character set conversion will take
place on data stored in this datatype. It is considered a string of binary bytes of information
that will simply be stored by the database. A
RAW
may store up to 2,000 bytes of information.
Starting with Oracle 12
c
, a
RAW
can be configured to store up to 32,767 bytes of information
(see the “Extended Datatypes” section in this chapter for further details).
•
NUMBER
: This datatype is capable of storing numbers with up to 38 digits of precision. These
numbers may vary between 1.0x10(-130) and up to but not including 1.0x10(126). Each
number is stored in a
variable length
field that varies between 0 bytes (for
NULL
) and 22 bytes.
Oracle
NUMBER
types are very precise—much more so than normal
FLOAT
and
DOUBLE
types
found in many programming languages.
•
BINARY_FLOAT
: This is a type available only in Oracle 10
g
Release 1 and above. This is a 32-bit
single-precision floating-point number. It can support at least 6 digits of precision and will
consume 5 bytes of storage on disk.
•
BINARY_DOUBLE
: This is a type available only in Oracle 10
g
Release 1 and above. This is a 64-bit
double-precision floating-point number. It can support at least 15 digits of precision and will
consume 9 bytes of storage on disk.
•
LONG
: This type is capable of storing up to 2GB of character data (2
gigabytes,
not characters,
as each character may take multiple bytes in a multibyte character set).
LONG
types have many
restrictions (I'll discuss later) that are provided for backward compatibility, so it is strongly
recommended you do not use this type in new applications. When possible, convert from
LONG
to
CLOB
types in existing applications.
•
LONG
RAW
: The
LONG RAW
type is capable of storing up to 2GB of binary information. For the
same reasons as noted for
LONG
s, it is recommended you use the
BLOB
type in all future
development and, when possible, in existing applications as well.
•
DATE
: This is a fixed-width 7-byte date/time datatype. It will always contain the seven
attributes of the century, the year within the century, the month, the day of the month, the
hour, the minute, and the second.
•
TIMESTAMP
: This is a fixed-width 7- or 11-byte date/time datatype (depending on the precision).
It differs from the
DATE
datatype in that it may contain fractional seconds; up to 9 digits to the
right of the decimal point may be preserved for
TIMESTAMP
s with fractional seconds.
•
TIMESTAMP WITH TIME
ZONE
: This is a fixed-width 13-byte date/time datatype, but it also
provides for
TIME ZONE
support. Additional information regarding the time zone is stored with
the
TIMESTAMP
in the data, so the
TIME ZONE
originally inserted is preserved with the data.
•
TIMESTAMP WITH LOCAL TIME
ZONE
: This is a fixed-width 7- or 11-byte date/time datatype
(depending on the precision), similar to the
TIMESTAMP
; however, it is time zone sensitive.
Upon modification in the database, the
TIME ZONE
supplied with the data is consulted, and the
date/time component is normalized to the local database time zone. So, if you were to insert
a date/time using the time zone U.S./Pacific and the database time zone was U.S./Eastern,
the final date/time information would be converted to the Eastern time zone and stored as a
TIMESTAMP
. Upon retrieval, the
TIMESTAMP
stored in the database would be converted to the
time in the session's time zone.
