This chapter provides information about data types, reserved words, functions, queries, and TRANSFORM statements that create pivot tables. It includes the following sections:
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 |
DATE |
The |
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 |
RAW |
Raw binary data of length size bytes. Maximum size is 2000 bytes. You must specify size for a RAW value. |
VARCHAR |
The |
For more information about Oracle data types, see Oracle SQL Reference.
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 |
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.
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 |
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 |
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.
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.
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.