Global Ignore List

The Global Ignore List pane specifies filters that determine which files and file types will not be used in any processing.

New Filter: A file name or file type that you want to add to the list of files and file types (in the Filter box) that SQL Developer will ignore during all processing (if the filter is enabled, or checked). You can exclude a particular file by entering its complete file name, such as mumble.txt, or you can exclude all files of the same type by entering a construct that describes the file type, such as *.txt.

Add: Adds the new filter to the list in the Filter box.

Remove: Deletes the selected filter from the list in the Filter box.

Restore Defaults: Restores the contents of the Filter box to the SQL Developer defaults.

Filter: Contains the list of files and file types. For each item, if it is enabled (checked), the filter is enforced and the file or file type is ignored by SQL Developer; but if it is disabled (unchecked), the filter is not enforced.

1.17.11 Migration

The Migration pane contains options that affect the behavior of SQL Developer when you migrate schema objects and data from third-party databases to an Oracle

database.

Default Repository: Migration repository to be used for storing the captured models and converted models. For information about migrating third-party databases to Oracle, including how to create a migration repository, see Chapter 2.

SQL Developer Preferences

Migration: Data Move Options

The Data Move Options pane contains options that affect the behavior when you migrate data from third-party databases to Oracle Database tables generated by the migration. This pane includes options that can be used for online data migration for all supported third-party databases, and for offline data migration for MySQL, SQL Server, and Sybase Adaptive Server.

Oracle Representation for Zero Length String: The value to which Oracle converts zero-length strings in the source data. Can be a space (' ') or a null value (NULL).

Specific notes:

■ For Microsoft Access offline migrations, a null value and a space are considered the same.

■ For Sybase offline migrations, '' is considered the same as a space (' ').

■ For MySQL offline migrations, a null value is exported as 'NULL', which is handled as type VARCHAR2. You can specify another escape character by using the --fields-escaped-by option with the mysqldump command (for example, specifying \N for null or \\ for \). For information about the mysqldump command, see Section 2.2.8.1.3, "Creating Data Files From MySQL".

For MySQL offline migrations, the data is exported to a file named table-name.txt;

so if you are moving data from two or more tables with the same name but in different schemas, rename files as needed so that they are all unique, and modify the SQL*Loader .ctl file accordingly.

Online: The online data move options determine the results of files created when you click Migration, then Migrate Data.

Number of Parallel Data Move Streams (online data moves): The number of internal connections created for simultaneous movement of data from the source database to the Oracle tables. Higher values may shorten the total time required, but will use more database resources during that time.

Number of Rows to Commit After (online data moves): During the data move operation, Oracle pauses to perform an automatic internal commit operation after each number of rows that you specify are moved from the source database to Oracle tables.

Lower values will cause a successful move operation to take more time; but if a failure occurs, it is likely that more source records will exist in the Oracle tables and that if the move operation is resumed, fewer source records will need to be moved. Higher values will cause a successful move operation to take less time; but if a failure occurs, it is likely that fewer source records will exist in the Oracle tables and that is the move operation is resumed, more source records will need to be moved.

Offline: The offline data move options determine the results of files created, such as when you click Tools, then Migration, then Create Database Capture Scripts.

End of Column Delimiter (offline data moves): String to indicate end of column.

End of Row Delimiter (offline data moves): String to indicate end of row.

Generic Date Mask (offline data moves): Format mask for dates, unless overridden by user-defined custom preferences.

Generic Timestamp Mask (offline data moves): Format mask for timestamps, unless overridden by user-defined custom preferences.

User-Defined Custom Preferences by Source Type (offline data moves): Lets you specify, for one or more source data types, a custom mapping for the function and format mask. Add one row for each mapping. For example, the following rows specify

SQL Developer Preferences

the Source Type, Function, and Mask for custom mappings for the Sybase data types datetime, smalldatetime, and time:

datetime TO_TIMESTAMP mon dd yyyy hh:mi:ss:ff3am smalldatetime TO_DATE Mon dd yyyy hh:miam time TO_TIMESTAMP hh:mi:ss:ff3am

Migration: Generation Options

The Generation Options pane contains options that are used when generating .sql script files for creating migrated database objects in the target schema.

One single file or A file per object: Determines how many files are created and their relative sizes. Having more files created might be less convenient, but may allow more flexibility with complex migration scenarios.

Generate Comments: Generates comments in the Oracle SQL statements.

Generate Controlling Script: Generates a "master" script for running all the required files.

Least Privilege Schema Migration: For migrating schema objects in a converted model to Oracle, causes CREATE USER, GRANT, and CONNECT statements not to be generated in the output scripts. You must then ensure that the scripts are run using a connection with sufficient privileges. You can select this option if the database user and connection that you want to use to run the scripts already exist, or if you plan to create them.

Generate Data Move User: For data move operations, creates an additional database user with extra privileges to perform the operation. It is recommended that you delete this user after the operation. This option is provided for convenience, and is suggested unless you want to perform least privilege migrations or unless you want to grant privileges manually to a user for the data move operations. This option is especially recommended for multischema migrations, such as when not all tables belong to a single user.

Generate Failed Objects: Causes objects that failed to be converted to be included in the generation script, so that you can make any desired changes and then run the script. If this option is not checked, objects that failed to be converted are not included in the generation script.

Generate Stored Procedure for Migrate Blobs Offline: Causes a stored procedure named CLOBtoBLOB_sqldeveloper (with execute access granted to public) to be created if the schema contains a BLOB (binary large object); this procedure is automatically called if you perform an offline capture. If this option is not checked, you will need to use the manual workaround described in Section 2.2.8.1.4,

"Populating the Destination Database Using the Data Files". (After the offline capture, you can delete the CLOBtoBLOB_sqldeveloper procedure or remove execute access from public.)

Generate Separate Emulation User: Causes a separate database user named EMULATION to be created. The emulation package is created in the new

EMULATION schema and is referenced by all other migrated users. If this option is not checked, no separate EMULATION user is created, and the emulation package is created within each migrated user. Generating a separate emulation user is usually the best practice because the emulation package is defined in one place, rather than having multiple copies of it. However, if you prefer each migrated user to be standalone and not need to reference anything from another user, then uncheck this option.

(Sybase) To Index-Organized Tables: Controls whether Sybase clustered unique

SQL Developer Preferences

NONE causes neither to be converted to index-organized tables (they are converted to Oracle unique constraints and primary keys, respectively). From Clustered Unique Constraints converts each clustered constraint into a primary key and creates an index-organized table if there is no primary key already. From Clustered Primary Keys creates an index-organized table if the primary key is clustered (the Sybase default).

Migration: Identifier Options

The Identifier Options pane contains options that apply to object identifiers during migrations.

Prepended to All Identifier Names (Microsoft Access, Microsoft SQL Server, and Sybase Adaptive Server migrations only): A string to be added at the beginning of the name of migrated objects. For example, if you specify the string as XYZ_, and if a source table is named EMPLOYEES, the migrated table will be named XYZ_

EMPLOYEES. (Be aware of any object name length restrictions if you use this option.) Is Quoted Identifier On (Microsoft SQL Server and Sybase Adaptive Server

migrations only): If this option is enabled, quotation marks (double-quotes) can be used to refer to identifiers (for example, SELECT "Col 1" from "Table 1"); if this option is not enabled, quotation marks identify string literals. Important: The setting of this option must match the setting in the source database to be migrated, as explained in Section 2.2.4.2, "Before Migrating From Microsoft SQL Server or Sybase Adaptive Server".

Migration: Translators

The Translators pane contains options that relate to conversion of stored procedures and functions from their source database format to Oracle format. (These options apply only to migrations from Microsoft Access, Microsoft SQL Server, and Sybase Adaptive Server.)

Default Source Date Format: Default date format mask to be used when casting string literals to dates in stored procedures and functions.

Variable Name Prefix: String to be used as the prefix in the names of resulting variables.

In Parameter Prefix: String to be used as the prefix in the names of resulting input parameters.

Query Assignment Translation: Option to determine what is generated for a query assignment: only the assignment, assignment with exception handling logic, or assignment using a cursor LOOP ... END LOOP structure to fetch each row of the query into variables.

Display AST: If this option is checked, the abstract syntax tree (AST) is displayed in the Source Tree pane of the Translation Scratch Editor window (described in

Section 2.3.5) if you perform a translation.

Generate Compound Triggers: If this option is checked, then depending on the source code, SQL Developer can convert a Sybase or SQL Server trigger to an Oracle

compound trigger, which uses two temporary tables to replicate the inserted and deleted tables in Sybase and SQL Server. In such cases, this can enable the conversion of logic that cannot otherwise be converted.