3
Data Types, Reserved Words, and Functions

This chapter includes the following sections:

• Oracle Data Types

• Jet Data Types for MS Access 97

• Default Data Type Mappings

• Oracle Reserved Words

• MS Access Functions

Oracle Data Types

The following table 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	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	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	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	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	Raw binary data of length size bytes. Maximum size is 2000 bytes. You must specify size for a RAW value.
VARCHAR	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.

Refer to Oracle8i SQL Reference, Release 2 (8.1.6) (Part Number: A76989-01) for more information about Oracle data types.

Jet Data Types for MS Access 97

The following table illustrates the Jet data types for MS Access 97:

Table 3-2 Jet Data Types for MS Access 97

Data Type	Description	Min	Max
Text	Stores variable length text	1	255
Memo	Large variable length text	1	64,000 bytes
Number Byte	1 byte storage	0	255
Number Integer	2 bytes storage	-32,768	32,767
Number Long Integer	4 bytes storage	-2,147,483,648	2,147,483,647
Number Single	4 bytes storage	-3.4 x 1038	3.4 x 1038
Number Double	8 bytes storage	-1.8 x 10308	1.8 x 10308
Currency	8 bytes storage - monetary values	-922337203685477.5808	922337203685477.5808
Counter Yes/No	4 bytes - AutoIncrement Field 1 bit storage - Boolean Value	0	2,147,483,647
Date/Time	8 bytes storage
OLE	OLE, graphics other complex data	1	1.2 gigabytes
Binary	Binary Data	1	1.2 gigabytes

Default Data Type Mappings

The following table illustrates the default settings used by the Migration Workbench to convert data types from MS Access 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-3 Default Data Type Mappings Used by Oracle Migration Workbench

MS Access Data Type	Oracle Data Type
Boolean	NUMBER(1, 0)
Byte	NUMBER(3, 0)
Currency	NUMBER(15, 4)
Date	DATE
Double	FLOAT
Integer	NUMBER(5, 0)
Long	NUMBER(11, 0)
LongBinary	BLOB
Memo	CLOB
Single	FLOAT
Text	VARCHAR2

Oracle Reserved Words

The following table lists those words that are reserved in Oracle. The Migration Workbench appends an underscore to any object names that conflict with these reserved words.

Table 3-4 Oracle 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

MS Access Functions

Table-based default values and field validation rules in MS Access can contain MS Access functions and operators. Corresponding functions and operators are generated as triggers in Oracle to support the operation of these default values and field validation rules whenever possible.

The Migration Workbench uses one of the following techniques to convert these functions and operators to Oracle:

Technique	Description
NO ACTION	There is a direct 1:1 mapping between the MS Access function/operator and Oracle.
REPLACE IN PLACE	The MS Access function/operator is directly replaced by the equivalent Oracle function/operator.
CODE	An Oracle function is written to duplicate the MS Access functionality. This function is created in an Oracle server during the migration.
*	This is unhandled which means that you must change your MS Access application so this function is not required in the default value or field validation rule.

The following is a list of MS Access functions and how they are converted to Oracle:

Table 3-5 Conversion of MS Access Functions to Oracle

MS Access	Oracle	Conversion Action
Abs	Abs	NO ACTION
Asc	Ascii	REPLACE IN PLACE
Atn	-	*
CCur	To_Number	CODE
CDbl	To_Number	CODE
Chr	Chr	NO ACTION
Chr$	Chr	NO ACTION
CInt	To_Number	CODE
CLng	To_Number	CODE
Command	-	*
Command$	-	*
Cos	COS	NO ACTION
CSng	To_Number	REPLACE IN PLACE
CStr	To_Char	REPLACE IN PLACE
CVar	To_Char	REPLACE IN PLACE
CVDate	-	*
Date	SYSDATE	REPLACE IN PLACE
Date$	SYSDATE	REPLACE IN PLACE
DateAdd		CODE
DateDiff	-	*
DatePart	-	*
DateSerial	-	*
DateValue	-	*
To_Date	-	*
Day	-	*
Environ	-	*
Environ$	-	*
Exp	EXP	NO ACTION
Fix	Trunc	REPLACE IN PLACE
Format	-	*
Format$	-	*
Hex	-	*
Hex$	-	*
Hour	-	*
In	-	CODE (not supported in def)
InStr	InStr	NO ACTION
Int	Trunc	CODE
Is Not Null		CODE (not supported in def)
Is Null		CODE (not supported in def)
IsDate	-	*
LCase	LOWER	REPLACE IN PLACE
LCase$	LOWER	REPLACE IN PLACE
Left	SUBSTR	CODE
Left$	SUBSTR	CODE
Len	LENGTH	REPLACE IN PLACE
Like	*
Log	LOG	NO ACTION
LTrim	LTRIM	NO ACTION
LTrim$	LTRIM	NO ACTION
Mid	SUBSTR	CODE
Mid$	SUBSTR	CODE
Minute	-	*
Month	-	*
Now	SYSDATE	REPLACE IN PLACE
Oct	-	*
Oct$	-	*
RGB	-	*
Right	SUBSTR	CODE
Right$	SUBSTR	CODE
Rnd	Rnd	REPLACE IN PLACE
RTrim	RTRIM	NO ACTION
RTrim$	RTRIM	NO ACTION
Second	-	*
Sgn	SIGN	REPLACE IN PLACE
Sin	SIN	NO ACTION
Space	LPAD/RPAD	CODE
Space$	LPAD/RPAD	CODE
Sqr	SQRT	REPLACE IN PLACE
Str	TO_Char	REPLACE IN PLACE
Str$	TO_Char	REPLACE IN PLACE
StrComp	-	*
String	LPAD/RPAD	CODE
String$	LPAD/RPAD	CODE
Tan	TAN	NO ACTION
Time	SYSDATE	CODE
Time$	SYSDATE	CODE
Timer	-	*
TimeSerial	-	*
TimeValue	-	*
Trim	TRIM	NO ACTION
Trim$	TRIM	NO ACTION
UCase	UPPER	REPLACE IN PLACE
UCase$	UPPER	REPLACE IN PLACE
Val	TO_NUMBER	REPLACE IN PLACE
Weekday	-	*
Year	-	*

The following is a list of MS Access operators and how they are converted to Oracle:

Table 3-6 Conversion of MS Access Operators to Oracle

MS Access	Oracle	Conversion Action
^	Power(m,n)	REPLACE IN PLACE
&	-	CODE
Between	-	CODE
Eqv	-	*
Imp	-	*
Mod	MOD(m,n)	REPLACE IN PLACE
Xor	-	*