Converting to Oracle Data Types

SQL statements that create tables and clusters can also use ANSI data types and data types from the IBM products SQL/DS and DB2. Oracle recognizes the ANSI or IBM data type name that differs from the Oracle data type name, records it as the name of the data type of the column, and then stores the column data in an Oracle data type based on the conversions shown in the following table.

Table 6-2 ANSI Data Types Converted to Oracle Data Types

ANSI SQL Data Type Oracle Data Type

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 1)

NUMBER(p,s)

INTEGER

INT

SMALLINT

NUMBER(p,0)

FLOAT (Note 2)

DOUBLE PRECISION (Note 3)

REAL (Note 4)

FLOAT(126)

FLOAT(126)

FLOAT(63)


Notes:

  1. The NUMERIC and DECIMAL data types can specify only fixed-point numbers. For those data types, the scale (s) defaults to 0.

  2. The FLOAT data type is a floating-point number with a binary precision b. The default precision for this data type is 126 binary, or 38 decimal.

  3. The DOUBLE PRECISION data type is a floating-point number with binary precision 126.

  4. The REAL data type is a floating-point number with a binary precision of 63, or 18 decimal.

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

  • GRAPHIC

  • LONG VARGRAPHIC

  • VARGRAPHIC

  • TIME

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