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.
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.
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.
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
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 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.
VARCHAR(30) in Microsoft SQL Server.
NVARCHAR(128) in Microsoft SQL Server 7.0.
VARCHAR2(30) and VARCHAR2(128) respectively.
