Oracle and SQL Server Data Type Mapping

2.2 Data Types

This chapter provides detailed descriptions of the differences in data types used by Microsoft SQL Server and Oracle databases. Specifically, this chapter contains the following information:

• A table showing the base Microsoft SQL Server data types available and how they are mapped to Oracle data types

• Recommendations based on the information listed in the table

2.2.1 Data Types Table

Table 2-2 Data Types in Oracle and Microsoft SQL Server

Microsoft SQL Server Description Oracle Comments

INTEGER	Four-byte integer, 31 bits, and a sign. May be abbreviated as "INT" (this abbreviation was required prior to version 5).	NUMBER(10)	It is possible to place a table constraint on columns of this type (as an option) to force values between -2^31 and2^31. Or, place appropriate constraints such as: STATE_NO between 1 and 50
SMALLINT	Two-byte integer, 15 bits, and a sign.	NUMBER(6)	It is possible to place a table constraint on columns of this type (optionally) to force values between -2^15 and 2^15. Or, place appropriate constraints such as: STATE_NO between 1 and 50
TINYINT	One byte integer, 8 bits and no sign. Holds whole numbers between 0 and 255.	NUMBER(3)	You may add a check constraint of (x between 0 and 255) where x is column name.
REAL	Floating point number. Storage is four bytes and has a binary precision of 24 bits, a 7-digit precision. Data can range from –3.40E+38 to 3.40E+38.	FLOAT	The ANSI data type conversion to Oracle for REAL is FLOAT(63). By default, the Oracle Migration Workbench maps REAL to FLOAT(24) that stores up to 8 significant decimal digits in Oracle. The Oracle NUMBER data type is used to store both fixed and floating-point numbers in a format that is compatible with decimal arithmetic. You may want to add a check constraint to constrain range of values. Also, you get different answers when performing operations on this data type as the Oracle NUMBER type is more precise and portable than REAL. Floating-point numbers can be specified in Oracle in the following format: FLOAT[(b)]. Where [(b)] is the binary precision b and can range from 1 to 126. [(b)] defaults to 126. To check what a particular binary precision is in terms of decimal precision, multiply [(b)] by 0.30103 and round up to the next whole number.
FLOAT	A floating point number. This column has 15-digit precision.	FLOAT	The ANSI data type conversion to Oracle for FLOAT(p) is FLOAT(p). The ANSI data type conversion to Oracle for DOUBLE PRECISION is FLOAT(126). By default, the Oracle Migration Workbench maps FLOAT to FLOAT(53), that stores up to 16 significant decimal digits in Oracle. The Oracle NUMBER data type is used to store both fixed and floating-point numbers in a format compatible with decimal arithmetic.You get different answers when performing operations on this type due to the fact that the Oracle NUMBER type is much more precise and portable than FLOAT, but it does not have the same range. The NUMBER data type data can range from -9.99.99E+125 to 9.99.99E+125 (38 nines followed by 88 zeros). NOTE: If you try to migrate floating point data greater than or equal to 1.0E+126 then Migration Workbench will fail to insert this data in the Oracle database and1 will return an error.This also applies to negative values less than or equal to -1.0E+126.
			Floating-point numbers can be specified in Oracle using FLOAT[(b)], where [(b)] is the binary precision [(b)] and can range from 1 to 126. [(b)] defaults to 126.To check what a particular binary precision is in terms of decimal precision multiply [(b)] by 0.30103 and round up to the next whole number. If they are outside of the range, large floating-point numbers will overflow, and small floating-point numbers will underflow.
BIT	A Boolean 0 or 1 stored as one bit of a byte. Up to 8-bit columns from a table may be stored in a single byte, even if not contiguous. Bit data cannot be NULL, except for Microsoft SQL Server 7.0, where null is allowed by the BIT data type.	NUMBER(1)	In Oracle, a bit is stored in a number(1) (or char). In Oracle, it is possible to store bits in a char or varchar field (packed) and supply PL/SQL functions to set / unset / retrieve / query on them.
CHAR(n)	Fixed-length string of exactly n 8-bit characters, blank padded. Synonym for CHARACTER. 0 < n < 256 for Microsoft SQL Server. 0 < n < 8000 for Microsoft SQL Server 7.0.	CHAR(n)	Pro*C client programs must use mode=ansi to have characters interpreted correctly for string comparison, mode=oracle otherwise. A CHAR data type with a range of 2001 to 4000 is invalid. SQL Developer automatically converts a CHAR datatype with this range to VARCHAR2.
VARCHAR(n)	Varying-length character string. 0 < n < 256 for Microsoft SQL Server. 0 < n < 8000 for Microsoft SQL Server 7.0.	VARCHAR2(n)
TEXT	Character string of 8-bit bytes allocated in increments of 2k pages. "TEXT" is stored as a linked-list of 2024-byte pages, blank padded. TEXT columns can hold up to (231-1) characters.	CLOB	The CLOB field can hold up to 4GB.
IMAGE	Binary string of 8-bit bytes. Holds up to (231-1) bytes of binary data.	BLOB	The BLOB field can hold up to 4GB.
BINARY(n)	Fixed length binary string of exactly n 8-bit bytes. 0 < n < 256 for Microsoft SQL Server. 0 < n < 8000 for Microsoft SQL Server 7.0.	RAW(n)/BLOB
VARBINARY(n)	Varying length binary string of up to n 8-bit bytes. 0 < n < 256 for Microsoft SQL Server. 0 < n < 8000 for Microsoft SQL Server 7.0.	RAW(n)/BLOB
DATETIME	Date and time are stored as two 4-byte integers. The date portion is represented as a count of the number of days offset from a baseline date (1/1/1900) and is stored in the first integer. Permitted values are legal dates between 1st January, 1753 AD and 31st December, 9999 AD. Permitted values in the time portion are legal times in the range 0 to 25920000. Accuracy is to the nearest 3.33 milliseconds with rounding downward. Columns of type DATETIME have a default value of 1/1/1900.	DATE	The precision of DATE in Oracle and DATETIME in Microsoft SQL Server is different. The DATETIME data type has higher precision than the DATE data type. This may have some implications if the DATETIME column is supposed to be UNIQUE. In Microsoft SQL Server, the column of type DATETIME can contain UNIQUE values because the DATETIME precision in Microsoft SQL Server is to the hundredth of a second. In Oracle, however, these values may not be UNIQUE as the date precision is to the second. You can replace a DATETIME column with two columns, one with data type DATE and another with a sequence, in order to get the UNIQUE combination. It is preferable to store hundredths of seconds in the second column. The Oracle TIMESTAMP data type can also be used. It has a precision of 1/10000000th of a second.
SMALL-DATETIME	Date and time stored as two 2-byte integers. Date ranges from 1/1/1900 to 6/6/2079. Time is the count of the number of minutes since midnight.	DATE	With optional check constraint to validate the smaller range.
MONEY	A monetary value represented as an integer portion and a decimal fraction, and stored as two 4-byte integers. Accuracy is to the nearest 1/10,000. When inputting Data of this type it should be preceded by a dollar sign ($). In the absence of the "$" sign, Microsoft SQL Server creates the value as a float. Monetary data values can range from -922,337,203,685,477.5808 to 922,337,203,685,477.5807, with accuracy to a ten-thousandth of a monetary unit. Storage size is 8 bytes.	NUMBER(19,4)	Microsoft SQL Server inputs MONEY data types as a numeric data type with a preceding dollar sign ($) as in the following example, select * from table_x where y > $5.00 You must remove the "$" sign from queries. Oracle is more general and works in international environments where the use of the "$" sign cannot be assumed. Support for other currency symbols and ISO standards through NLS is available in Oracle.
NCHAR(n)	Fixed-length character data type which uses the UNICODE UCS-2 character set. n must be a value in the range 1 to 4000. SQL Server storage size is two times n. Note: Microsoft SQL Server storage size is two times n. The Oracle Migration Workbench maps columns sizes using byte semantics, and the size of Microsoft SQL Server NCHAR data types appear in the Oracle Migration Workbench Source Model with "Size" specifying the number of bytes, as opposed to the number of Unicode characters. Thus, a SQL Server column NCHAR(1000) will appear in the Source Model as NCHAR(2000).	CHAR(n*2)
NVARCHAR(n)	Fixed-length character data type which uses the UNICODE UCS-2 character set. n must be a value in the range 1 to 4000. SQL Server storage size is two times n. Note: Microsoft SQL Server storage size is two times n. The Oracle Migration Workbench maps columns sizes using byte semantics, and the size of Microsoft SQL Server NVARCHAR data types appear in the Oracle Migration Workbench Source Model with "Size" specifying the number of bytes, as opposed to the number of Unicode characters. Thus, a SQL Server column NVARCHAR(1000) will appear in the Source Model as NVARCHAR(2000).	VARCHAR(n*2)
SMALLMONEY	Same as MONEY except monetary data values from -214,748.3648 to +214,748.3647, with accuracy to one ten-thousandth of a monetary unit. Storage size is 4 bytes.	NUMBER(10,4)	Since the range is -214,748.3648 to 214,748.364, NUMBER(10,4) suffices for this field.
TIMESTAMP	TIMESTAMP is defined as VARBINARY(8) with NULL allowed. Every time a row containing a TIMESTAMP column is updated or inserted, the TIMESTAMP column is automatically increment by the system. A TIMESTAMP column may not be updated by users.	NUMBER	You must place triggers on columns of this type to maintain them. In Oracle you can have multiple triggers of the same type without having to integrate them all into one big trigger. You may want to supply triggers to prevent updates of this column to enforce full compatibility.
SYSNAME	VARCHAR(30) in Microsoft SQL Server. NVARCHAR(128) in Microsoft SQL Server 7.0.	VARCHAR2(30) and VARCHAR2(128) respectively.

<!-- class="tblformal" -->

TEXT and IMAGE data types in Microsoft SQL Server follow these rules:

• The column of these data types cannot be indexed.

• The column cannot be a primary key.

• The column cannot be used in the GROUP BY, ORDER BY, HAVING, and DISTINCT clauses.

• IMAGE and TEXT data types can be referred to in the WHERE clause with the LIKE construct.

• IMAGE and TEXT data types can also be used with the SUBSTR and LENGTH functions.

In Microsoft SQL Server, only columns with variable-length data types can store NULL values. When you create a column that allows NULLs with a fixed-length data type, the column is automatically converted to a system variable-length data type, as illustrated in Table 2-3. These variable-length data types are reserved system data types, and users cannot use them to create columns

Table 2-3 Data Type Conversion for NULL Values

Fixed-Length Data Type Variable-Length Data Type

CHAR	VARCHAR
NCHAR	NVARCHAR
BINARY	VARBINARY
DATETIME, SMALLDATETIME	DATETIMN
FLOAT	FLOATN
INT, SMALLINT, TINYINT	INTN
DECIMAL	DECIMALN
NUMERIC	NUMERICN
MONEY, SMALLMONEY	MONEYN

<!-- class="tblformal" -->

Note:

The Oracle Migration Workbench Source Model will display table system data types for each column.

Recommendations

In addition to the data types listed in Table 2-2, users can define their own data types in Microsoft SQL Server databases. These user-defined data types translate to the base data types that are provided by the server. They do not allow users to store additional types of data, but can be useful in implementing standard data types for an entire application.

You can map data types from Microsoft SQL Server to Oracle with the equivalent data types listed in Table 2-3. SQL Developer converts user-defined data types to their base type. You can defined how the base type is mapped to an Oracle type in the Data Type Mappings page in the Options dialog.

<!-- class="sect2" -->