Oracle7 Server Concepts

ANSI, DB2, and SQL/DS Datatypes

In addition to Oracle datatypes, columns of tables in an Oracle database can be defined using ANSI, DB2, and SQL/DS datatypes. However, Oracle internally converts such datatypes to Oracle datatypes.

ANSI SQL Datatype Oracle Datatype
CHARACTER (n), CHAR(n) CHAR(n)
NUMERIC (p, s), DECIMAL (p, s) DEC (p, a) NUMBER (p, s)
INTEGER, INT, SMALLINT NUMBER (38)
FLOAT (p), REAL, DOUBLE PRECISION NUMBER
CHARACTER VARYING(n), CHAR VARYING(n) VARCHAR(n)

Table 6 - 3. ANSI Datatype Conversions to Oracle Datatypes

DB2 or SQL/DS Datatype Oracle Datatype
CHARACTER (n) CHAR(n)
VARCHAR (n) VARCHAR2 (n)
LONG VARCHAR LONG
DECIMAL (p, s) NUMBER (p, s)
INTEGER, SMALLINT NUMBER (38)
FLOAT (p) NUMBER
DATE DATE

Table 6 - 4. SQL/DS, DB2 Datatype Conversions to Oracle Datatypes

The IBM products SQL/DS and DB2 datatypes TIME, TIMESTAMP, GRAPHIC, VARGRAPHIC, and LONG VARGRAPHIC have no corresponding Oracle datatype, and cannot be used. The TIME and TIMESTAMP datatypes are subcomponents of the Oracle datatype DATE.

The ANSI datatypes NUMERIC, DECIMAL, and DEC can specify only fixed-point numbers. For these datatypes, s defaults to 0.

ANSI SQL Datatype	Oracle Datatype
CHARACTER (n), CHAR(n)	CHAR(n)
NUMERIC (p, s), DECIMAL (p, s) DEC (p, a)	NUMBER (p, s)
INTEGER, INT, SMALLINT	NUMBER (38)
FLOAT (p), REAL, DOUBLE PRECISION	NUMBER
CHARACTER VARYING(n), CHAR VARYING(n)	VARCHAR(n)

DB2 or SQL/DS Datatype	Oracle Datatype
CHARACTER (n)	CHAR(n)
VARCHAR (n)	VARCHAR2 (n)
LONG VARCHAR	LONG
DECIMAL (p, s)	NUMBER (p, s)
INTEGER, SMALLINT	NUMBER (38)
FLOAT (p)	NUMBER
DATE	DATE