Table 2-1 Built-In Data Type Summary
Code Data Type Description
1
VARCHAR2(size [BYTE | CHAR])
Variable-length character string having maximum length size bytes or characters.
You must specify size for VARCHAR2. Minimum size is 1 byte or 1 character. Maximum
size is:
32767 bytes or characters if MAX_STRING_SIZE = EXTENDED
4000 bytes or characters if MAX_STRING_SIZE = STANDARD
Refer to Extended Data Types for more information on the MAX_STRING_SIZE
initialization parameter.
BYTE indicates that the column will have byte length semantics. CHAR indicates that
the column will have character semantics.
NVARCHAR2(size)
Variable-length Unicode character string having maximum length size characters. You
must specify size for NVARCHAR2. The number of bytes can be up to two times size
for AL16UTF16 encoding and three times size for UTF8 encoding. Maximum size is
determined by the national character set definition, with an upper limit of:
32767 bytes if MAX_STRING_SIZE = EXTENDED
4000 bytes if MAX_STRING_SIZE = STANDARD
Refer to Extended Data Types for more information on the MAX_STRING_SIZE
initialization parameter.
NUMBER [ (p [, s]) ]
Number having precision p and scale s. The precision p can range from 1 to 38. The
scale s can range from -84 to 127. Both precision and scale are in decimal digits.
A NUMBER value requires from 1 to 22 bytes.
FLOAT [(p)]
A subtype of the NUMBER data type having precision p. A FLOAT value is represented
internally as NUMBER. The precision p can range from 1 to 126 binary digits. A
FLOAT value requires from 1 to 22 bytes.
LONG
Character data of variable length up to 2 gigabytes, or 231 -1 bytes. Provided for
backward compatibility.
12
DATE
Valid date range from January 1, 4712 BC, to December 31, 9999 AD. The default
format is determined explicitly by the NLS_DATE_FORMAT parameter or implicitly by
the NLS_TERRITORY parameter. The size is fixed at 7 bytes. This data type contains
the datetime fields YEAR, MONTH, DAY, HOUR, MINUTE, and SECOND. It does not have
fractional seconds or a time zone.
100
BINARY_FLOAT
32-bit floating point number. This data type requires 4 bytes.
101
BINARY_DOUBLE
64-bit floating point number. This data type requires 8 bytes.
180
TIMESTAMP [(fractional_seconds_precision)]
Year, month, and day values of date, as well as hour, minute, and second values of
time, where fractional_seconds_precision is the number of digits in the fractional
part of the SECOND datetime field. Accepted values of fractional_seconds_precision
are 0 to 9. The default is 6. The default format is determined explicitly by the
NLS_TIMESTAMP_FORMAT parameter or implicitly by the NLS_TERRITORY parameter. The
size is 7 or 11 bytes, depending on the precision. This data type contains the
datetime fields YEAR, MONTH, DAY, HOUR, MINUTE, and SECOND. It contains fractional
seconds but does not have a time zone.
181
TIMESTAMP [(fractional_seconds_precision)] WITH TIME ZONE
All values of TIMESTAMP as well as time zone displacement value, where
fractional_seconds_precision is the number of digits in the fractional part of the
SECOND datetime field. Accepted values are 0 to 9. The default is 6. The default
date format for the TIMESTAMP WITH TIME ZONE data type is determined by the
NLS_TIMESTAMP_TZ_FORMAT initialization parameter. The size is fixed at 13 bytes.
This data type contains the datetime fields YEAR, MONTH, DAY, HOUR, MINUTE, SECOND,
TIMEZONE_HOUR, and TIMEZONE_MINUTE. It has fractional seconds and an explicit time
zone.
231
TIMESTAMP [(fractional_seconds_precision)] WITH LOCAL TIME ZONE
All values of TIMESTAMP WITH TIME ZONE, with the following exceptions:
Data is normalized to the database time zone when it is stored in the database.
When the data is retrieved, users see the data in the session time zone.
The default format is determined explicitly by the NLS_TIMESTAMP_FORMAT parameter
or implicitly by the NLS_TERRITORY parameter. The size is 7 or 11 bytes, depending
on the precision.
182
INTERVAL YEAR [(year_precision)] TO MONTH
Stores a period of time in years and months, where year_precision is the number of
digits in the YEAR datetime field. Accepted values are 0 to 9. The default is 2.
The size is fixed at 5 bytes.
183
INTERVAL DAY [(day_precision)] TO SECOND [(fractional_seconds_precision)]
Stores a period of time in days, hours, minutes, and seconds, where
day_precision is the maximum number of digits in the DAY datetime field. Accepted
values are 0 to 9. The default is 2.
fractional_seconds_precision is the number of digits in the fractional part of the
SECOND field. Accepted values are 0 to 9. The default is 6.
The size is fixed at 11 bytes.
23
RAW(size)
Raw binary data of length size bytes. You must specify size for a RAW value.
Maximum size is:
32767 bytes if MAX_STRING_SIZE = EXTENDED
2000 bytes if MAX_STRING_SIZE = STANDARD
Refer to Extended Data Types for more information on the MAX_STRING_SIZE
initialization parameter.
24
LONG RAW
Raw binary data of variable length up to 2 gigabytes.
69
ROWID
Base 64 string representing the unique address of a row in its table. This data
type is primarily for values returned by the ROWID pseudocolumn.
208
UROWID [(size)]
Base 64 string representing the logical address of a row of an index-organized
table. The optional size is the size of a column of type UROWID. The maximum size
and default is 4000 bytes.
96
CHAR [(size [BYTE | CHAR])]
Fixed-length character data of length size bytes or characters. Maximum size is
2000 bytes or characters. Default and minimum size is 1 byte.
BYTE and CHAR have the same semantics as for VARCHAR2.
96
NCHAR[(size)]
Fixed-length character data of length size characters. The number of bytes can be
up to two times size for AL16UTF16 encoding and three times size for UTF8 encoding.
Maximum size is determined by the national character set definition, with an upper
limit of 2000 bytes. Default and minimum size is 1 character.
112
CLOB
A character large object containing single-byte or multibyte characters. Both
fixed-width and variable-width character sets are supported, both using the
database character set. Maximum size is (4 gigabytes - 1) * (database block size).
112
NCLOB
A character large object containing Unicode characters. Both fixed-width and
variable-width character sets are supported, both using the database national
character set. Maximum size is (4 gigabytes - 1) * (database block size). Stores
national character set data.
113
BLOB
A binary large object. Maximum size is (4 gigabytes - 1) * (database block size).
114
BFILE
Contains a locator to a large binary file stored outside the database. Enables byte
stream I/O access to external LOBs residing on the database server. Maximum size is
4 gigabytes.
BFILE Data Type
The BFILE data type enables access to binary file LOBs that are stored in file
systems outside Oracle Database. A BFILE column or attribute stores a BFILE
locator, which serves as a pointer to a binary file on the server file system. The
locator maintains the directory name and the filename.
You can change the filename and path of a BFILE without affecting the base table by
using the BFILENAME function. Refer to BFILENAME for more information on this
built-in SQL function.
Binary file LOBs do not participate in transactions and are not recoverable.
Rather, the underlying operating system provides file integrity and durability.
BFILE data can be up to 264-1 bytes, although your operating system may impose
restrictions on this maximum.
The database administrator must ensure that the external file exists and that
Oracle processes have operating system read permissions on the file.
The BFILE data type enables read-only support of large binary files. You cannot
modify or replicate such a file. Oracle provides APIs to access file data. The
primary interfaces that you use to access file data are the DBMS_LOB package and
Oracle Call Interface (OCI).
See Also:
Oracle Database SecureFiles and Large Objects Developer's Guide and Oracle Call
Interface Programmer's Guide for more information about LOBs and CREATE DIRECTORY
BLOB Data Type
The BLOB data type stores unstructured binary large objects. BLOB objects can be
thought of as bitstreams with no character set semantics. BLOB objects can store
binary data up to (4 gigabytes -1) * (the value of the CHUNK parameter of LOB
storage). If the tablespaces in your database are of standard block size, and if
you have used the default value of the CHUNK parameter of LOB storage when creating
a LOB column, then this is equivalent to (4 gigabytes - 1) * (database block size).
BLOB objects have full transactional support. Changes made through SQL, the
DBMS_LOB package, or Oracle Call Interface (OCI) participate fully in the
transaction. BLOB value manipulations can be committed and rolled back. However,
you cannot save a BLOB locator in a PL/SQL or OCI variable in one transaction and
then use it in another transaction or session.
CLOB Data Type
The CLOB data type stores single-byte and multibyte character data. Both fixed-
width and variable-width character sets are supported, and both use the database
character set. CLOB objects can store up to (4 gigabytes -1) * (the value of the
CHUNK parameter of LOB storage) of character data. If the tablespaces in your
database are of standard block size, and if you have used the default value of the
CHUNK parameter of LOB storage when creating a LOB column, then this is equivalent
to (4 gigabytes - 1) * (database block size).
CLOB objects have full transactional support. Changes made through SQL, the
DBMS_LOB package, or Oracle Call Interface (OCI) participate fully in the
transaction. CLOB value manipulations can be committed and rolled back. However,
you cannot save a CLOB locator in a PL/SQL or OCI variable in one transaction and
then use it in another transaction or session.
NCLOB Data Type
The NCLOB data type stores Unicode data. Both fixed-width and variable-width
character sets are supported, and both use the national character set. NCLOB
objects can store up to (4 gigabytes -1) * (the value of the CHUNK parameter of LOB
storage) of character text data. If the tablespaces in your database are of
standard block size, and if you have used the default value of the CHUNK parameter
of LOB storage when creating a LOB column, then this is equivalent to (4 gigabytes
- 1) * (database block size).
NCLOB objects have full transactional support. Changes made through SQL, the
DBMS_LOB package, or OCI participate fully in the transaction. NCLOB value
manipulations can be committed and rolled back. However, you cannot save an NCLOB
locator in a PL/SQL or OCI variable in one transaction and then use it in another
transaction or session.
Extended Data Types
Beginning with Oracle Database 12c, you can specify a maximum size of 32767 bytes
for the VARCHAR2, NVARCHAR2, and RAW data types. You can control whether your
database supports this new maximum size by setting the initialization parameter
MAX_STRING_SIZE as follows:
If MAX_STRING_SIZE = STANDARD, then the size limits for releases prior to Oracle
Database 12c apply: 4000 bytes for the VARCHAR2 and NVARCHAR2 data types, and 2000
bytes for the RAW data type. This is the default.
If MAX_STRING_SIZE = EXTENDED, then the size limit is 32767 bytes for the VARCHAR2,
NVARCHAR2, and RAW data types.