Skip Headers

Oracle Migration Workbench Reference Guide for Microsoft Access 2.0, 95, 97, 2000 Migrations
Release 9.2.0 for Microsoft Windows 98/2000 and Microsoft Windows NT

Part Number A97262-01
Go To Documentation Library
Home
Go To Product List
Book List
Go To Table Of Contents
Contents
Go To Index
Index

Master Index

Feedback

Go to previous page Go to next page

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 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 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

-

*


Go to previous page Go to next page
Oracle
Copyright © 1998, 2002 Oracle Corporation.

All Rights Reserved.
Go To Documentation Library
Home
Go To Product List
Book List
Go To Table Of Contents
Contents
Go To Index
Index

Master Index

Feedback