6 Datatypes

This chapter presents datatypes recognized by Oracle and available for use within SQL.

This chapter includes the following section:

Datatypes

Datatypes

A datatype is a classification of a particular type of information or data. Each value manipulated by Oracle has a datatype. The datatype of a value associates a fixed set of properties with the value. These properties cause Oracle to treat values of one datatype differently from values of another.

Table 6-1 shows the datatypes recognized by Oracle.

Table 6-1 Datatypes Recognized by Oracle

Datatype	Syntax
ANSI-supported datatypes	{ CHARACTER [VARYING] (size) \| { CHAR \| NCHAR } VARYING (size) \| VARCHAR (size) \| NATIONAL { CHARACTER \| CHAR } [VARYING] (size) \| { NUMERIC \| DECIMAL \| DEC } [ (precision [, scale ]) ] \| { INTEGER \| INT \| SMALLINT } \| FLOAT [ (size) ] \| DOUBLE PRECISION \| REAL }
Oracle built-in datatypes	{ character_datatypes \| number_datatypes \| long_and_raw_datatypes \| datetime_datatypes \| large_object_datatypes \| rowid_datatypes }
Oracle-supplied types	{ any_types \| XML_types \| spatial_types \| media_types \| expression_filter_type }
user-defined datatypes	use Oracle built-in datatypes and other user-defined datatypes to model the structure and behavior of data in applications

Oracle Built-In Datatypes

Table 6-2 identifies the types of Oracle built-in datatypes.

Table 6-2 Oracle Built-in Datatypes

Built-In Datatype	Syntax
character_datatypes	{ CHAR [ (size [ BYTE \| CHAR ]) ] \| VARCHAR2 (size [ BYTE \| CHAR ]) \| NCHAR [ (size) ] \| NVARCHAR2 (size) }
datetime_datatypes	{ DATE \| TIMESTAMP [ (fractional_seconds_precision) ] [ WITH [ LOCAL ] TIME ZONE ]) \| INTERVAL YEAR [ (year_precision) ] TO MONTH \| INTERVAL DAY [ (day_precision) ] TO SECOND [ (fractional_seconds_precision) ] }
large_object_datatypes	{ BLOB \| CLOB \| NCLOB \| BFILE }
long_and_raw_datatypes	{ LONG \| LONG RAW \| RAW (size) }
number_datatypes	{ NUMBER [ (precision [, scale ]) ] \| BINARY_FLOAT \| BINARY_DOUBLE }
rowid_datatypes	{ ROWID \| UROWID [ (size) ] }

Table 6-3 summarizes Oracle built-in datatypes. The codes listed for the datatypes are used internally by Oracle Database. The datatype code of a column or object attribute is returned by the DUMP function.

Table 6-3 Built-In Datatype Summary

Code	Built_in Datatype	Description
1	`VARCHAR2(``size` `[BYTE \| CHAR])`	Variable-length character string having maximum length `size` bytes or characters. Maximum `size` is 4000 bytes or characters, and minimum is 1 byte or 1 character. You must specify `size` for `VARCHAR2`. `BYTE` indicates that the column will have byte length semantics; `CHAR` indicates that the column will have character semantics.
1	`NVARCHAR2(``size)`	Variable-length character string having maximum length `size` characters. Maximum `size` is determined by the national character set definition, with an upper limit of 4000 bytes. You must specify `size` for `NVARCHAR2`.
2	`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.
8	`LONG`	Character data of variable length up to 2 gigabytes, or 2³¹ -1 bytes.
12	`DATE`	Valid date range from January 1, 4712 BC to December 31, 9999 AD.
21	`BINARY_FLOAT`	32-bit floating point number. This datatype requires 5 bytes, including the length byte.
22	`BINARY_DOUBLE`	64-bit floating point number. This datatype requires 9 bytes, including the length byte.
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.
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.
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.
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.
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.
23	`RAW(``size``)`	Raw binary data of length `size` bytes. Maximum `size` is 2000 bytes. You must specify `size` for a `RAW` value.
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 datatype 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. 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. 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.

Converting to Oracle Datatypes

SQL statements that create tables and clusters can also use ANSI datatypes and datatypes from the IBM products SQL/DS and DB2. Oracle recognizes the ANSI or IBM datatype name that differs from the Oracle datatype name, records it as the name of the datatype of the column, and then stores the column data in an Oracle datatype based on the conversions shown in Table 6-4 and Table 6-5.

Table 6-4 ANSI Datatypes Converted to Oracle Datatypes

ANSI SQL Datatype	ANSI SQL Datatype	Notes
CHARACTER(n) CHAR(n)	CHAR(n)
CHARACTER VARYING(n) CHAR VARYING(n)	VARCHAR(n)
NATIONAL CHARACTER(n) NATIONAL CHAR(n) NCHAR(n)	NCHAR(n)
NATIONAL CHARACTER VARYING(n) NATIONAL CHAR VARYING(n) NCHAR VARYING(n)	NVARCHAR2(n)
NUMERIC(p,s) DECIMAL(p,s)^a	NUMBER(p,s)	^aThe NUMBERIC and DECIMAL datatypes can specify only fixed-point numbers. For those datatypes, s defaults to 0.
INTEGER INT SMALLINT	NUMBER(38)
FLOAT(b)^b DOUBLE PRECISION^c REAL^d	NUMBER	^bThe FLOAT datatype is a floating-point number with a binary precision b. The default precision for this datatypes is 126 binary, or 38 decimal. ^cThe DOUBLE PRECISION datatype is a floating-point number with binary precision 126. ^dThe REAL datatype is a floating-point number with a binary precision of 63, or 18 decimal.

Table 6-5 SQL/DS and DB2 Datatypes Converted to Oracle Datatypes

SQL/DS or DB2 Datatype	Oracle Datatype	Notes
`CHARACTER(n)`	`CHAR(n)`
VARCHAR(n)	`VARCHAR(n)`
LONG VARCHAR(n)	`LONG`
`DECIMAL(p,s)`	`NUMBER(p,s)`	The `DECIMAL` datatype can specify only fixed-point numbers. For this datatype, `s` defaults to 0.
`INTEGER` `SMALLINT`	`NUMBER(38)`
`FLOAT(b)`	`NUMBER`	The `FLOAT` datatype is a floating-point number with a binary precision `b`. The default precision for this datatype is 126 binary or 38 decimal.

Do not define columns with the following SQL/DS and DB2 datatypes, because they have no corresponding Oracle datatype:

GRAPHIC
LONG VARGRAPHIC
VARGRAPHIC
TIME

Note that data of type TIME can also be expressed as Oracle datetime data.