This chapter describes the data types used within Oracle. It shows the MySQL data types and what is the Oracle equivelent. It also provides you with a list of reserved words within Oracle. It includes information on the following:
Table 3-1 describes the Oracle data types supported by the Migration Workbench.
Table 3-1 Oracle Data Types Supported by Oracle Migration Workbench
Data Type
Description
BLOB
A binary large object. Maximum size is 4 gigabytes.
CHAR (SIZE)
Fixed-length character data of length size bytes. Maximum size is 2000 bytes. Default and minimum size is 1 byte.
CLOB
A character large object containing single-byte characters. Both fixed-width and variable-width character sets are supported, both using the CHAR database character set. Maximum size is 4 gigabytes.
DATE
The DATE data type stores date and time information. Although date and time information can be represented in both CHAR and NUMBER data types, the DATE data type has special associated properties. For each DATE value, Oracle stores the following information: century, year, month, day, hour, minute, and second.
FLOAT
Specifies a floating-point number with decimal precision 38, or binary precision 126.
LONG (SIZE)
Character data of variable length up to 2 gigabytes, or 231 -1 bytes.
LONG RAW
Raw binary data of variable length up to 2 gigabytes.
NCHAR (SIZE)
Fixed-length character data of length size characters or bytes, depending on the choice of national character set. Maximum size is determined by the number of bytes required to store each character, with an upper limit of 2000 bytes. Default and minimum size is 1 character or 1 byte, depending on the character set.
NCLOB
A character large object containing multibyte characters. Both fixed-width and variable-width character sets are supported, both using the NCHAR database character set. Maximum size is 4 gigabytes. Stores national character set data.
NUMBER
Number having precision p and scale s. The precision p can range from 1 to 38. The scale s can range from -84 to 127.
NVARCHAR2 (SIZE)
Variable-length character string having maximum length size characters or bytes, depending on the choice of national character set. Maximum size is determined by the number of bytes required to store each character, with an upper limit of 4000 bytes. You must specify size for NVARCHAR2.
RAW (SIZE)
Raw binary data of length size bytes. Maximum size is 2000 bytes. You must specify size for a RAW value.
VARCHAR (SIZE)
The VARCHAR data type is currently synonymous with the VARCHAR2 data type. Oracle recommends that you use VARCHAR2 rather than VARCHAR. In the future, VARCHAR might be defined as a separate data type used for variable-length character strings compared with different comparison semantics. The maximum size is 4000 and the minimum of 1 is the default.
Refer to Oracle9i SQL Reference, Release 1 (9.0.1) for more information about Oracle data types.
Default Data Type Mappings
Table 3-2 shows the default settings used by the Migration Workbench to convert data types from MySQL to Oracle. The Migration Workbench allows you to change the default setting for certain data types by specifying an alternative type. You can do this in the Capture Wizard or in the Data Type Mappings page of the Options dialog box.
Refer to the Oracle Migration Workbench Online Help for more information about changing the default data type mappings.
Table 3-2 Default Data Type Mappings Used by Oracle Migration Workbench
MySQL Data Type
Oracle Data Type
TINYINT
NUMBER(3, 0)
SMALLINT
NUMBER(5, 0)
MEDIUMINT
NUMBER(7, 0)
INT
NUMBER(10, 0)
INTEGER
NUMBER(10, 0)
BIGINT
NUMBER(19, 0)
FLOAT
FLOAT
DOUBLE
FLOAT (24)
DOULBE PRECISION
FLOAT (24)
REAL
FLOAT (24)
DECIMAL
FLOAT (24)
NUMERIC
NUMBER
DATE
DATE
DATETIME
DATE
TIMESTAMP
NUMBER
TIME
DATE
YEAR
NUMBER
CHAR
CHAR
VARCHAR
VARCHAR2
TINYBLOB
RAW
TINYTEXT
VARCHAR2
BLOB
BLOB, RAW
TEXT
VARCHAR2, CLOB
MEDIUMBLOB
BLOB, RAW
MEDIUMTEXT
RAW, CLOB
LONGBLOB
BLOB, RAW
LONGTEXT
RAW, CLOB
ENUM
VARCHAR2, set to 100 by default
SET
VARCHAR2, set to 100 by default
Note:
The Enum data type has no direct mapping in Oracle. The Migration Workbench maps Enum columns in MySQL to Varchar2 columns in Oracle. It then adds a constraint to those columns to ensure that only values that were allowed by the Enum data type are allowed in the column it was mapped to in Oracle.
The Set data type has no direct mapping in Oracle. The current version of the Migration Workbench maps Set columns in MySQL to Varchar2 columns in Oracle.
Comparing MySQL to Oracle
The following tables represent the mappings of the datatypes between MySQL and Oracle. For some MySQL datatypes there is more than one alternative Oracle datatype. The tables include information on the following:
In the case of MySQL data types that map to numeric datatypes in Oracle the following conditions apply:
If there is no precision or scale defined for the destination Oracle data type then precision and scale are taken from the MySQL source data type.
If there is a precision or scale defined for the destination data type then these values are compared to the equivalent values of the source data type and the maximum value is selected.
The following table compares the numeric types of MySQL to Oracle:
MySQL
Size
Oracle
TINYINT
1 Byte
NUMBER(3,0)
SMALLINT
2 Bytes
NUMBER(5,0)
MEDIUMINT
3 Bytes
NUMBER (7,0)`
INT
4 Bytes
NUMBER (10,0)
INTEGER
4 Bytes
NUMBER (10,0)
BIGINT
8 Bytes
NUMBER (19,0)
FLOAT(X<=24)
4 Bytes
FLOAT(0)
FLOAT(25<=X <=53)
8 Bytes
FLOAT(24)
DOUBLE
8 Bytes
FLOAT(24)
DOUBLE PRECION
8 Bytes
FLOAT(24)
REAL
8 Bytes
FLOAT(24)
DECIMAL
M Bytes(D+2, if M<D)
FLOAT(24)
NUMERIC
M Bytes(D+2, if M<D)
NUMBER
Date and Time Types
The following table compares the date and time types of MySQL to Oracle:
MySQL
Size
Oracle
DATE
3 Bytes
DATE
DATETIME
8 Bytes
DATE
TIMESTAMP
4 Bytes
NUMBER
TIME
3 Bytes
DATE
YEAR
1 Byte
NUMBER
String Types
In the case of MySQL data types that map to character data types in Oracle, the following conditions apply:
If there is no length defined for the destination data type then the length is taken from the source datatype.
If there is a length defined for the destination data type then the maximum value of the two lengths is taken.
Note:
Reference to M indicates the maximum display size. The maximum legal display size is 255. While a reference to L applies to a floating point types and indicates the number of digits following the decimal point.
The following compares the string types of MySQL to Oracle:
MySQL
Size
Oracle
CHAR(m)
M Bytes, 1<=M<=255
CHAR
VARCHAR(m)
L+1 Bytes whereas L<=M and 1<=M<=255
VARCHAR2
TINYBLOB
L + 1 Bytes whereas L<2 ^8
RAW, BLOB
BLOB
L + 2 Bytes whereas L<2^16
RAW, BLOB
TEXT
L + 2 Bytes whereas L<2^16
RAW, BLOB
MEDIUMBLOB
L + 3 Bytes whereas L < 2^ 24
RAW, BLOB
MEDIUMTEXT
L + 3 Bytes whereas L < 2^ 24
RAW, BLOB
LONGBLOB
L + 4 Bytes whereas L < 2 ^ 32
RAW, BLOB
LONGTEXT
L + 4 Bytes whereas L < 2 ^ 32
RAW, BLOB
ENUM (VALUE1, VALUE2, ...)
1 or 2 Bytes depending on the number of enum. values (65535 values max)
SET (VALUE1, VALUE2, ...)
1, 2, 3, 4 or 8 Bytes depending on the number of set members (64 members maximum)
Oracle Reserved Words
The words are reserved in Oracle. The Migration Workbench appends an underscore to any object names that conflict with these reserved words.
扫一扫
166
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
