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:

Section 3.1, "Oracle Data Types"
Section 3.2, "Jet Data Types for Microsoft Access"
Section 3.3, "Default Data Type Mappings"
Section 3.4, "Oracle Reserved Words"
Section 3.5, "Microsoft Access Queries"
Section 3.6, "Microsoft Access TRANSFORM Statements That Create Pivot Tables"

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

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:

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:

Create a Data Source Name (DSN) using an Oracle ODBC driver.
With the database open in Microsoft Access, select Insert > Table.
In the pop-up window, select Link Table.
In the Files of Type dialog box, select ODBC Databases.
Select the DSN created in step 1.
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.
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.