Set the following options to control the conversion process.
All Oracle objects created by Scriptoria are qualified with the User (or Schema) name. For example, a Access table named Categories would be created as NORTHWIND.CATEGORIES, where NORTHWIND is the name specified in the combo box. Either select an owner from the combo box or enter a new one. Scriptoria generates a CREATE USER statement and assigns it password "Oracle". A GRANT CONNECT, RESOURCE TO statement is also issued. The logged user must have appropriate privileges to create new users.
An Oracle SQL*PLUS compatible script is always generated when tables and/or queries are converted to Oracle. You can choose to use Scriptoria's ODBC deployment mechanism or deploy using SQL*PLUS. The ODBC method lacks the robust error handling of SQL*PLUS. Some ODBC drivers are also problematic.
When SQL*PLUS is specified, Scriptoria builds and shells a DOS batch file that executes SQL*PLUS. The log file is spooled and can be displayed after completion using the SQL button on the Status page.
Specify Deploy Immediately to immediately compile the generated script or Create Script Only to generate a script file for later deployment.
When one or more existing Oracle tables are selected to be converted, any Oracle views, procedures and functions that use the tables will become invalid since the Oracle tables will be dropped before they are recreated.
When the Recompile box is checked, Scriptoria will regenerate the associated MS-Access queries and recompile them. If you do not wish to recompile Oracle objects after tables are dropped, remove the check from this box. Note that any dependant views, procedures and functions will become invalid and unusable until you use the Query Conversion Facility to recompile them.
For more demanding migrations, it may be desirable to break the project down into three or four steps. First, all table definitions are created, compiled and checked for errors. Then data is exported using the SQL*Loader Wizard and the message log checked for errors. After successfully completing the previous tasks, indexes are now built. Relationships can be created as a final step, allowing some testing to be performed first and any incremental design changes to be implemented.
You can also create all table definitions, indexes, relationships and load data all in one step. In addition, Scriptoria fully supports incremental changes. Select one or more tables to convert and Scriptoria will automatically drop existing tables, load data and reinstate any relationships.
To accommodate these needs, Scriptoria is capable of creating separate table, index, and relationship scripts.
Tables: Select this option to include Oracle table definitions in the generated script. The table definition will specify:
Data types, as defined in the Data Type Defaults form
NOT NULL when specified or implied by the associated Access field
DEFAULT clauses that can be translated from the associated Access Default property
CHECK clauses that can be translated from the associated Access field and table Validation Rule properties.
Table and Index table space definitions when specified. (see the Assign Storage section)
The following DDL commands may follow the table definition:
Primary and Unique key declarations using the ALTER TABLE construct
Triggers that implement the functionality of Access Auto-Number fields.
Triggers that implement Validation Rules that specify user defined Validation Text.
Indexes: Check this box to include indexes in the generated script. Access indexes are translated into equivalent Oracle indexes, including the unique property. Access index names are renamed to valid Oracle names. Although Access allows the creation of duplicate index definitions, Scriptoria ensures index singularity.
Data: Check this box to load Access table data using SQL*Loader. This option is not available when the ODBC options is selected.
Relationships: Oracle declarative relationships are generated when Access relations specify the Enforce option. The Oracle relationship will include the ON CASCADE DELETE option when specified by the associated Access relationship.
When the Access relationship includes the Cascade Update option, Scriptoria must replicate the functionality of the Access relationship using triggers to satisfy the cascade update requirement.
Indexes that specify the same fields in the same order as another index that has an equal number or more fields are redundant. For example, if index Inx1 includes fields A and B and Inx2 includes fields A, B, and C; then Inx1 is redundant. Redundant indexes add overhead when inserting and deleting rows and may confuse the Oracle query optimizer.
You can instruct Scriptoria to either Ignore (the default) or create redundant indexes. Warning messages are issued and written to the Conversion Warnings table when Scriptoria encounters redundant indexes. The message includes the table name, the redundant index name, and the name of the index that covers the superfluous index.
When two indexes include the same number of fields in the same order, primary and unique indexes take precedence over non-unique indexes. When two unique (not primary) indexes include the same number of fields in the same order, one is arbitrarily deemed redundant.