Oracle Advanced Options

This dialog box specifies advanced options for the Oracle target database. The following options are available:

In the BIN directory box, enter the path to the folder where the Oracle utilities like SQL Plus and SQL Loader are located. You can click (...) to indicate this folder.

In the SQL Loader box:

In the Executable box, enter the name of the executable module of the Oracle SQL Loader. The default name is sqlldr.exe.

In the Data option box, select the loading option in the generated SQL Loader control file. Possible options are Insert, Append, Replace, Truncate. The default option is Insert.

If Use direct path load check box is selected, it specifies a direct path load. By default the Use direct path load check box is clear, which specifies a conventional path load.

If Use DECODE for empty CHARs check box is selected, SQLWays generates DECODE function in the SQL Loader control file for CHAR NOT NULL columns for fixed-length text file to convert NULLs to blanks. If you what to load fields that contain only blanks into CHAR NOT NULL columns and the text file has the fixed length format, you have to use DECODE function, because SQL Loader treats blanks as NULLs.

In the SQL Plus box:

In the Executable box, enter the name of the executable module of the Oracle SQL Plus. The default name is sqlplus.exe.

The SQL Loader column formats box specifies the data type that represents the datetime datatypes in Oracle. Datetime data consist of valid date and time combinations. Depending on the database this is the DATE data type in Oracle, TIMESTAMP data type in IBM DB2 and DATETIME data type in Microsoft SQL Server and Sybase. SQLWays will convert the datetime datatypes to the specified data type. By default the datetime data type are converted to DATE. For example, when TIMESTAMP(6) is specified, the datetime datatypes will be converted to TIMESTAMP(6) in Oracle. The TIMESTAMP data type is available in Oracle 9i Release 1 (9.0.1) or later.

In the DATE box, enter the format string in SQL Loader control files for loading dates (year, month, day) into DATE columns from the text files. The default format is "YYYY-MM-DD". If any value is specified in the DATE_FORMAT option in the [Data] subsection, it overrides the DATE option.

In the TIME box, enter the format string in SQL Loader control files for loading times (hours, minutes, seconds) into DATE columns from the text files. The default format is "HH24:MI:SS". If any value is specified in the TIME_FORMAT option in the [Data] subsection, it overrides the TIME option.

In the TIMESTAMP box, enter the format string in SQL Loader control files for loading timestamps (year, month, day, hours, minutes, seconds) into DATE columns from the text files. The default format is "YYYY-MM-DD HH24:MI:SS". If any value is specified in the DATETIME_FORMAT option in the [Data] subsection, it overrides the TIMESTAMP option.

In the TIMESTAMP9i box, enter the format string in SQL Loader control files for loading Oracle 9i TIMESTAMP data type (year, month, day, hours, minutes, seconds, fractional seconds precision) from the text files. The default format is "YYYY-MM-DD HH24:MI:SS.FF".

By default the Generate the EXIT command check box is selected, so the SQL Plus EXIT command is generated at the end of DDL scripts. This allows executing DDL scripts from multiple files in batch mode.

By default the Use the TO_DATE function check box is selected, so the TO_DATE function with the corresponding date format is generated for DATE columns in SQL INSERT statements for SQL Plus. This allows inserting rows that contain DATE columns when the date format differs from the default date format for Oracle database. If the Use the TO_DATE function check box is clear, the TO_DATE function is not generated in SQL INSERT statements.

Ispirer SQLWays Database Migration Software

Oracle Advanced Options