Skip Headers
Oracle® SQL Developer Supplementary Information for Microsoft Access Migrations
Release 3.0

E18459-03
Go to Documentation Home
Home
Go to Book List
Book List
Go to Table of Contents
Contents
Go to Index
Index
Go to Feedback page
Contact Us

Go to previous page
Previous
Go to next page
Next
PDF · Mobi · ePub

3 Data Types, Reserved Words, and Other Database Objects

This chapter provides information about data types, reserved words, functions, queries, and TRANSFORM statements that create pivot tables. It includes the following sections:

3.1 Oracle Data Types

Table 3-1 describes the Oracle data types that SQL Developer 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 Oracle SQL Reference.

3.2 Jet Data Types for Microsoft Access

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

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


3.3 Default Data Type Mappings

Table 3-3 illustrates the default settings used by SQL Developer to convert data types from Microsoft Access to Oracle. SQL Developer allows you to change the default setting for certain data types by specifying an alternative type. You can do this by right-clicking the desired captured model in the Captured Objects navigator and selecting Set Data Mapping. Table 3-3 shows some of the default mappings.

For more information about changing the default data type mappings, see the SQL Developer online help.

Table 3-3 Default Data Type Mappings Used by SQL Developer

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


3.4 Oracle Reserved Words

The following table lists, in tabular format, words that are reserved in Oracle. SQL Developer 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.5 Microsoft Access Queries

Microsoft Access queries are converted into Oracle views. This section explains any considerations for migrating queries, and explains how certain Microsoft Access features are mapped to the Oracle Model.

The following considerations apply for migrating Microsoft Access queries:

Table 3-4 shows how specific Microsoft Access features are converted to the Oracle Model during the migration of a query.

Table 3-4 Microsoft Access Feature Conversion During Query Migration

Microsoft Access Feature Conversion to Oracle Feature

DISTINCTROW

DISTINCT, with a warning that DISTINCTROW eliminates duplicate records based on all record data, whereas DISTINCT eliminates duplicate rows based only on the selected data. To match the DISTINCTROW behavior, you may need to select more columns (to ensure the necessary level of uniqueness).

References to dates (using the # character)

Date based on a simple date mask. If this does not match the data, you must modify the VARCHAR2TODATE date conversion function in the utilities package in the Oracle Model.


If you want a Microsoft Access query that does not use parameters and that has been converted to an Oracle view to be accessible as a linked table in Microsoft Access, follow these steps:

  1. Create a Data Source Name (DSN) using an Oracle ODBC driver.

  2. With the database open in Microsoft Access, select Insert > Table.

  3. In the pop-up window, select Link Table.

  4. In the Files of Type dialog box, select ODBC Databases.

  5. Select the DSN created in step 1.

  6. Select logon_name.viewname. You may also want to check Save Password so the Microsoft Access application will not need to prompt the user for the password at run time.

  7. Specify a unique identifier.

3.5.1 Microsoft Access Queries that Use Parameters

Microsoft Access queries with parameters are converted to Oracle views that need to have the parameters set in the same session. The parameters are stored in per-session variables and are accessed using get and set methods, which get and set parameter values, respectively. The following example sets the input_name parameter to Smith in a migrated query named queryexample:

omwb_emulation.utilities.setvarchar2('QUERYEXAMPLE:input_name','Smith');

The following example sets the current_date parameter to the current system date (SYSDATE in Oracle) in a migrated query named queryexample:

omwb_emulation.utilities.setdata('QUERYEXAMPLE:current_date',SYSDATE);

Note that to have the data displayed in your preferred format, you may need to modify the format string in the omwb_emulation.utilities.varchar2todate function.

The relevant get methods are already included in the migrated queries. For example:

SELECT omwb_emulation.utilities.getvarchar2('QUERYEXAMPLE:input_name') FROM dual
SELECT omwb_emulation.utilities.getdate('QUERYEXAMPLE:current_date') FROM dual 

The maximum size of the parameter string is set by default at 256.

The following example sets a parameter in the same session. The Microsoft Access query is as follows:

Parameters [input name] text;
select telephone from example1 where name=[input name];

The generated Oracle view is as follows:

CREATE OR REPLACE FORCE VIEW queryexample  AS
SELECT  telephone
 FROM example1 WHERE name = 
  (SELECT omwb_emulation.utilities.getvarchar2('QUERYEXAMPLE:input_name') 
    FROM dual)

With the preceding example, the parameter must be set by a statement such as the following:

omwb_emulation.utilities.setvarchar2('QUERYEXAMPLE:input_name','Smith');

After the parameter is set, the view can be queried. For example:

SELECT * FROM queryexample;

The following example sets parameters on an open connection. It assumes that you are using ADO code, and using MSDASQL (Microsoft OLE driver for ODBC) and Oracle ODBC. For more information, see article ID 281998 in the Microsoft knowledge base.

Declarations:
  Dim con As ADODB.Connection
  Dim setstring As ADODB.Command
  Dim param1 As ADODB.Parameter
  Dim param2 As ADODB.Parameter
 
Code to set parameter over connection con:
 
  Set setstring = New ADODB.Command
  With setstring
     .ActiveConnection = con
     .CommandText = "omwb_emulation.utilities.setvarchar2"
     .CommandType = adCmdStoredProc
  End With
 
  Set param1 = setstring.CreateParameter(, adVarChar, adParamInput, 100, "QUERYEXAMPLE:input_name")
  setstring.Parameters.Append param1
 
  Set param2 = setstring.CreateParameter(, adVarChar, adParamInput, 100, "Smith")
  setstring.Parameters.Append param2
 
  setstring.Execute
  Set setstring = Nothing

After executing this code, you can execute the query earlier in this section (SELECT * FROM queryexample;) over this connection and use the result set.

3.6 Microsoft Access TRANSFORM Statements That Create Pivot Tables

Only simple Microsoft Access TRANSFORM statements to create pivot tables will be migrated. For example, Microsoft Access statement in the following format:

TRANSFORM grp_function(value)
SELECT a,b,c
FROM xxxx
WHERE yyyy
GROUP BY zzzz
PIVOT pivot_exp in (val1,val2,val3)

is converted to a statement in the following format in the Oracle Model:

SELECT a,b,c,
grp_function(decode(pivot_exp,val1,value,null)),
grp_function(decode(pivot_exp,val2,value,null)),
grp_function(decode(pivot_exp,val3,value,null))
FROM xxxx
WHERE yyyy
GROUP BY zzzz

In the converted statement, the decode lines are similar to case decode(pivot_exp,val1,value,null) is if pivot_exp is equal to val1 return value else return null.

Note that GROUP BY may require the use of ORDER BY in the Oracle database.

Oracle requires the columns to be defined, so if the number of columns depends on the data required for building a dynamic query, SQL Developer cannot determine which columns are needed. If the types and numbers of the columns are not known in advance, you probably need to use a REF cursor.