3
Data Types, Reserved Words, and Functions

This chapter provides information about data types, reserved words, and functions. It includes the following sections:

Oracle Data Types

Table 3-1 describes the Oracle data types that the Migration Workbench supports:

Table 3-1 Supported Oracle Data Types

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 you can represent date and time information 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`.

For more information about Oracle data types, see the Oracle9i SQL Reference, Release 1 (9.0.1).

Jet Data Types for Microsoft Access

Table 3-2 illustrates the Jet data types for Microsoft Access 97 and Microsoft Access 2000:

Table 3-2 Jet Data Types for Microsoft Access

Data Type	Description	Minimum	Maximum
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 10³⁸	3.4 x 10³⁸
Number Double	8 bytes storage	-1.8 x 10³⁰⁸	1.8 x 10³⁰⁸
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 Object	OLE, graphics other complex data	1	1.2 gigabytes

Default Data Type Mappings

Table 3-3 illustrates the default settings used by the Migration Workbench to convert data types from Microsoft 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.

For more information about changing the default data type mappings, see the Oracle Migration Workbench Online Help.

Table 3-3 Default Data Type Mappings Used by the Migration Workbench

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

Oracle Reserved Words

The following table lists words that 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

Microsoft Access Functions

Default values and validation rules in Microsoft Access can contain Microsoft 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 Microsoft Access function/operator and Oracle.
REPLACE IN PLACE	The Microsoft Access function/operator is directly replaced by the equivalent Oracle function/operator.
CODE	An Oracle function is written to duplicate the Microsoft Access functional capabilities. This function is created in an Oracle server during the migration.
*	Oracle does not handle this function. Therefore, you must change the Microsoft Access application so this function is not required in the default value or field validation rule.

Table 3-4 contains a list of Microsoft Access functions and explains how they are converted to Oracle.

Table 3-4 Conversion of Microsoft Access Functions to Oracle

Microsoft Access	Oracle	Conversion Action
Abs	Abs	NO ACTION
Asc	Ascii	REPLACE IN PLACE
Atn	-	*
CCur	CCUR	CODE
CDbl	CDBL	CODE
Chr	Chr	NO ACTION
Chr$	Chr	NO ACTION
CInt	CINT	CODE
CLng	CLNG	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	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	INTN	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	LEFT	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$	MID	CODE
Minute	-	*
Month	-	*
Now	SYSDATE	REPLACE IN PLACE
Oct	-	*
Oct$	-	*
RGB	-	*
Right	RIGHT	CODE
Right$	SUBSTR	CODE
Rnd	RND	CODE
RTrim	RTRIM	NO ACTION
RTrim$	RTRIM	NO ACTION
Second	-	*
Sgn	SIGN	REPLACE IN PLACE
Sin	SIN	NO ACTION
Space	SPACE	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	TIME_1	CODE
Time	TIME_2	CODE
Time$	SYSDATE	CODE
Timer	-	*
TimeSerial	-	*
TimeValue	-	*
Trim	TRIM	CODE
Trim$	TRIM	NO ACTION
UCase	UPPER	REPLACE IN PLACE
UCase$	UPPER	REPLACE IN PLACE
Val	TO_NUMBER	REPLACE IN PLACE
Weekday	-	*
Year	-	*

Table 3-5 contains a list of Microsoft Access operators and how they are converted to Oracle.

Table 3-5 Conversion of Microsoft Access Operators to Oracle

Microsoft Access	Oracle	Conversion Action
^	Power(m,n)	REPLACE IN PLACE
<	LTN	CODE
>	GTN	CODE
<=	LTEN	CODE
>=	GTEN	CODE
<>	NOTEQN	CODE
=	EQN	CODE
and	ANDN	CODE
or	ORN	CODE
not	NOTN	CODE
divide operator (/)	INTDIV	CODE
Eqv	-	*
Imp	-	*
Mod	MOD(m,n)	REPLACE IN PLACE
Xor	-	*

3 Data Types, Reserved Words, and Functions