3
MySQL Data Types, Reserved Words, and Operators

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:

Supported Oracle Data Types

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:

Numeric Types

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.

ABORT	ACCEPT
ACCESS	ADD
ALL	ALTER
AND	ANY
ARRAY	ARRAYLEN
AS	ASC
ASSERT	ASSIGN
AT	AUDIT
AUTHORIZATION	AVG
BASE_TABLE	BEGIN
BETWEEN	BINARY_INTEGER
BODY	BOOLEAN
BY	CASE
CHAR	CHAR_BASE
CHECK	CLOSE
CLUSTER	CLUSTERS
COLAUTH	COLUMN
COMMENT	COMMIT
COMPRESS	CONNECT
CONSTANT	CRASH
CREATE	CURRENT
CURRVAL	CURSOR
DATA_BASE	DATABASE
DATE	DBA
DEBUGOFF	DEBUGON
DECIMAL	DECLARE
DEFAULT	DEFINITION
DELAY	DELETE
DESC	DIGITS
DISPOSE	DISTINCT
DO	DROP
ELSE	ELSIF
END	ENTRY
EXCEPTION	EXCEPTION_INIT
EXCLUSIVE	EXISTS
EXIT	FALSE
FETCH	FILE
FLOAT	FOR
FORM	FROM
FUNCTION	GENERIC
GOTO	GRANT
GROUP	HAVING
IDENTIFIED	IF
IMMEDIATE	IN
INCREMENT	INDEX
INDEXES	INDICATOR
INITIAL	INSERT
INTEGER	INTERFACE
INTERSECT	INTO
IS	LEVEL
LIKE	LIMITED
LOCK	LONG
LOOP	MAX
MAXEXTENTS	MIN
MINUS	MLSLABEL
MOD	MODE
MODIFY	NATURAL
NATURALN	NETWORK
NEW	NEXTVAL
NOAUDIT	NOCOMPRESS
NOT	NOWAIT
NULL	NUMBER
NUMBER_BASE	OF
OFFLINE	ON
ONLINE	OPEN
OPTION	OR
ORDER	OTHERS
OUT	PACKAGE
PARTITION	PCTFREE
PLS_INTEGER	POSITIVE
POSITIVEN	PRAGMA
PRIOR	PRIVATE
PRIVILEGES	PROCEDURE
PUBLIC	RAISE
RANGE	RAW
REAL	RECORD
REF	RELEASE
REMR	RENAME
RESOURCE	RETURN
REVERSE	REVOKE
ROLLBACK	ROW
ROWID	ROWLABEL
ROWNUM	ROWS
ROWTYPE	RUN
SAVEPOINT	SCHEMA
SELECT	SEPERATE
SESSION	SET
SHARE	SIGNTYPE
SIZE	SMALLINT
SPACE	SQL
SQLCODE	SQLERRM
START	STATEMENT
STDDEV	SUBTYPE
SUCCESSFUL	SUM
SYNONYM	SYSDATE
TABAUTH	TABLE
TABLES	TASK
TERMINATE	THEN
TO	TRIGGER
TRUE	TYPE
UID	UNION
UNIQUE	UPDATE
USE	USER
VALIDATE	VALUES
VARCHAR	VARCHAR2
VARIANCE	VIEW
VIEWS	WHEN
WHENEVER	WHERE
WHILE	WITH
WORK	WRITE
XOR

3 MySQL Data Types, Reserved Words, and Operators