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 the following table.

Table 6-2 ANSI Datatypes Converted to Oracle Datatypes

ANSI SQL Datatype	Oracle Datatype
`CHARACTER(n)` `CHAR(n)`	`CHAR(n)`
`CHARACTER VARYING(n)` `CHAR VARYING(n)`	`VARCHAR2(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)]` (Note a)	`NUMBER(p,s)`
`INTEGER` `INT` `SMALLINT`	`NUMBER(38)`
`FLOAT` (Note b) `DOUBLE PRECISION` (Note c) `REAL` (Note d)	`FLOAT(126)` `FLOAT(126)` `FLOAT(63)`

Notes:

1. The NUMERIC and DECIMAL datatypes can specify only fixed-point numbers. For those datatypes, the scale (s) defaults to 0.
2. 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.
3. The DOUBLE PRECISION datatype is a floating-point number with binary precision 126.
4. 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
`CHARACTER(n)`	`CHAR(n)`
`VARCHAR(n)`	`VARCHAR(n)`
`LONG VARCHAR`	`LONG`
`DECIMAL(p,s)` (a)	`NUMBER(p,s)`
`INTEGER` `SMALLINT`	`NUMBER(38)`
`FLOAT` (b)	`NUMBER`

Notes:

1. The DECIMAL datatype can specify only fixed-point numbers. For this datatype, s defaults to 0.
2. 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.