Oracle® Database SQL Quick Reference 10g Release 2 (10.2) Part Number B14195-02 |
|
|
View PDF |
This chapter presents datatypes that are recognized by Oracle and available for use within SQL.
This chapter includes the following sections:
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.
The datatypes recognized by Oracle are:
{ 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 }
{ character_datatypes | number_datatypes | long_and_raw_datatypes | datetime_datatypes | large_object_datatypes | rowid_datatypes }
{ 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
This section describes the kinds of Oracle built-in datatypes.
{ CHAR [ (size [ BYTE | CHAR ]) ] | VARCHAR2 (size [ BYTE | CHAR ]) | NCHAR [ (size) ] | NVARCHAR2 (size) }
{ DATE | TIMESTAMP [ (fractional_seconds_precision) ] [ WITH [ LOCAL ] TIME ZONE ]) | INTERVAL YEAR [ (year_precision) ] TO MONTH | INTERVAL DAY [ (day_precision) ] TO SECOND [ (fractional_seconds_precision) ] }
{ BLOB | CLOB | NCLOB | BFILE }
{ LONG | LONG RAW | RAW (size) }
{ NUMBER [ (precision [, scale ]) ] | BINARY_FLOAT | BINARY_DOUBLE }
{ ROWID | UROWID [ (size) ] }
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-1 Built-in Datatype Summary
Code | Datatype | Description |
---|---|---|
1 |
|
Variable-length character string having maximum length
|
1 |
|
Variable-length Unicode character string having maximum length |
2 |
|
Number having precision |
8 |
|
Character data of variable length up to 2 gigabytes, or 231 -1 bytes. Provided for backward compatibility. |
12 |
|
Valid date range from January 1, 4712 BC to December 31, 9999 AD. The default format is determined explicitly by the |
21 |
|
32-bit floating point number. This datatype requires 5 bytes, including the length byte. |
22 |
|
64-bit floating point number. This datatype requires 9 bytes, including the length byte. |
180 |
|
Year, month, and day values of date, as well as hour, minute, and second values of time, where |
181 |
|
All values of |
231 |
|
All values of
The default format is determined explicitly by the |
182 |
|
Stores a period of time in years and months, where |
183 |
|
Stores a period of time in days, hours, minutes, and seconds, where
The size is fixed at 11 bytes. |
23 |
|
Raw binary data of length |
24 |
|
Raw binary data of variable length up to 2 gigabytes. |
69 |
|
Base 64 string representing the unique address of a row in its table. This datatype is primarily for values returned by the |
208 |
|
Base 64 string representing the logical address of a row of an index-organized table. The optional |
96 |
|
Fixed-length character data of length
|
96 |
|
Fixed-length character data of length |
112 |
|
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 |
|
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 |
|
A binary large object. Maximum size is (4 gigabytes - 1) * (database block size). |
114 |
|
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. |
This section describes the kinds of Oracle-supplied datatypes.
{ SDO_Geometry | SDO_Topo_Geometry |SDO_GeoRaster }
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 the following table.
Table 6-2 ANSI Datatypes Converted to Oracle Datatypes
ANSI SQL Datatype | Oracle Datatype |
---|---|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
Notes:
The NUMERIC
and DECIMAL
datatypes can specify only fixed-point numbers. For those datatypes, s defaults to 0.
The FLOAT
datatype is a floating-point number with a binary precision b. The default precision for this datatypes is 126 binary, or 38 decimal.
The DOUBLE PRECISION
datatype is a floating-point number with binary precision 126.
The REAL
datatype is a floating-point number with a binary precision of 63, or 18 decimal.
Table 6-3 SQL/DS and DB2 Datatypes Converted to Oracle Datatypes
SQL/DS or DB2 Datatype | Oracle Datatype |
---|---|
|
|
|
|
|
|
|
|
|
|
|
|
Notes:
The DECIMAL
datatype can specify only fixed-point numbers. For this datatype, s
defaults to 0..
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.
Do not define columns with the following SQL/DS and DB2 datatypes, because they have no corresponding Oracle datatype: