8/13

Beta Draft

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

- Oracle Data Types

- Jet Data Types for Microsoft Access

- Default Data Type Mappings

- Oracle Reserved Words

- Microsoft Access Queries

- Microsoft Access TRANSFORM Statements That Create Pivot Tables

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

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

- In Oracle, using the view is a two-step process: setting the parameter and then calling the view. In Access, the user can be prompted for the parameter value. For more information about queries with parameters. see "Microsoft Access Queries that Use Parameters".

- The views are created with the `FORCE` option, so that they are created even if objects that they depend on do not yet exist. (This is necessary because the views might depend on other views or tables to be created later.)

  Therefore, after the migration you must recompile the views the views to see which are valid, or build them without the `FORCE` option to see information about any errors or issues.

- Oracle is more strict than Microsoft Access about column names. If there is a duplicate column name, an error (ORA-00957: duplicate column name) is generated. To avoid this problem, you can add an alias to one of the duplicate column names.

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, the Migration Workbench 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.