Ispirer Ispirer
 


Ispirer Home Page Ispirer SQLWays Product Page Request SQLWays

Sqlways.ini File - Section [ORACLE]

This article describes sqlways.ini file [ORACLE] section and the options it contains.

Option name Description
BIN Specifies the directory where the Oracle utilities like SQL Plus and SQL Loader are located.
SQLLOADER The name of the executable module of the Oracle SQL Loader. The default value is sqlldr.exe.
SQLPLUS The name of the executable module of the Oracle SQL Plus. The default value is sqlplus.exe.
USER Specifies the user name that is used in generated scripts for the Oracle SQL Plus and SQL Loader.
PWD Specifies the user password that is used in generated scripts for the Oracle SQL Plus and SQL Loader.
SERVICE_NAME Specifies the service name for the Oracle Net protocol that is used in generated scripts for the Oracle SQL Plus and SQL Loader.
TARGET_VERSION Specifies the target version of the Oracle database. If this option is empty the latest Oracle database version will be used.
Note: According to the version number conversion results may be different. For example if we set 12.1 or lower version, SQLWays will cut identifier names that exceed maximum length of 30 characters. But if we leave this option empty or set 12.2 or higher, SQLWays will leave the names as is.
SQLPLUS_EXIT_CMD If Yes is specified, the SQL Plus EXIT command is generated at the end of DDL scripts.
This allows to execute DDL scripts from multiple files in batch mode.
Possible values - Yes, No. The default value is Yes.
SQLPLUS_TO_DATE If Yes is specified, the TO_DATE function with the corresponding date format is generated for DATE columns
in SQL INSERT statements for SQL Plus. This allows to insert rows that contain
DATE columns when the date format differs from the default date format for Oracle database.
If No is specified, the TO_DATE function is not generated in SQL INSERT statements. Possible values - Yes, No. The default value is Yes.
DIRECT Specifies the Oracle SQL Loader method to use either conventional or direct path. Possible values - Yes, No.
Yes specifies a direct path load. No specifies a conventional path load.
The default value is No.
LOAD_OPTION Specifies loading option in the generated SQL Loader control file.
Possible values - Insert, Append, Replace, Truncate.
The default value is Insert.
DATE - Specifies the format string in SQL Loader control files for loading dates (year, month, day) into DATE columns from the text files. The default value is “YYYY-MM-DD”.
If any value is specified in the DATE_FORMAT option in the [Data] subsection, it overrides the DATE option.
TIME Specifies the format string in SQL Loader control files for loading times (hours, minutes, seconds) into DATE columns from the text files.
The default value is “HH24:MI:SS”.
If any value is specified in the TIME_FORMAT option in the [Data] subsection, it overrides the TIME option.
TIMESTAMP Specifies 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 value 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.
TIMESTAMP9i Specifies 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 value is “YYYY-MM-DD HH24:MI:SS.FF”.
FIXFMT_DECODE_NULLCHAR Specifies generating DECODE function in the SQL Loader control file for CHAR NOT NULL columns for fixed-length text file. Possible values - Yes, No. The default value is No.
If you want 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. Set this option to Yes and SQLWays will generate DECODE function to convert NULLs to blanks.
CONVERT_CLUSTERED_INDEX_TO Specifies how to convert clustered indexes to Oracle.
As the default the clustered index is converted to simple index.
With the option set as IOT the clustered index is converted to Index Organized Tables in Oracle
SEQUENCE_WITHOUT_TRIGGER Specifies how to convert serial column from Informix to Oracle.
As the default the trigger that automatically inserts value in to the sequence column is created. And there is no need to modify INSERT INTO statements in order to add sequence.NEXTVAL in table.
When the option is set as YES the trigger is not created and all the INSERT INTO statements are modified with the sequence.NEXTVAL function.
DB2_DECIMAL_FN_TO_TRUNC Specifies how to convert DECIMAL function from DB2 to Oracle.
When the option is set as YES, DECIMAL function will be converted to TRUNC.
With the option set as NOT the DB2 DECIMAL function will be converted to TO_NUMBER. Default value for this option is YES.
MAX_STRING_SIZE Starting from Oracle 12c version, a maximum length of 32767 bytes for the VARCHAR2, NVARCHAR2, and RAW data types can be used. If this option is empty, not set or specified STANDART, standart limits will be used: for VARCHAR2 and NVARCHAR2 it is 4000 bytes and for ROW it is 2000 bytes. If this option is set to EXTENDED limit of 32767 bytes will be used for VARCHAR2, NVARCHAR2, and RAW data types.
Possible values - Standart, Extended. Default value - Standart.
This option works for MS SQL Server, DB2, PostgreSQL, MySql, Sybase ASE to Oracle directions.
NLS_LENGTH_SEMANTICS This option controls length semantics for character data type columns in Oracle.
Possible values - “CHAR”, “BYTE”.
EXCEPTION_FOR_CURSORS This option will add exception block that will check whether the cursor was closed inside the procedure. Default value - “No”.
Possible values - “Yes”, “No”.
INITRANS This option set the INITRANS number for tables. If some value was assigned to this option, SQLWays Wizard will add INITRANS clause with the specified value to the tables definition. If this option is empty, INITRANS clause will not be added.
INDEX_INITRANS This option set the INITRANS number for indexes. If some value was assigned to this option, SQLWays Wizard will add INITRANS clause with the specified value to the indexes definition. If this option is empty, INITRANS clause will not be added.
IMPLICIT_CURSOR_USAGE This option controls the cursor declaration. If this option is set to “Yes”, implicit cursor declaration will be used for FOR LOOP construction. If this option is not set (empty) or set to “No”, explicit cursor declaration will be used.
Possible values - “Yes”, “No” or empty. Default value - “No” or empty.
GL_TEMP_TAB_OUTSIDE_SP If this option is set to “Yes” - all the “create global temporary table” statements will be moved outside the procedure body.
Possible values - “Yes”, “No” or Empty. Default value - “No” or Empty.
USE_CONVERT_CHAR_TO_VARCHAR This option controls whether the CHAR data type should be converted to VARCHAR2 in Oracle database. This options is used together with CONVERT_CHAR_TO_VARCHAR option, that defines the minimum length of char data type that will be converted to VARCHAR2.
Possible values - “Yes”, “No” or Empty. Default value - “No” or Empty.
CONVERT_CHAR_TO_VARCHAR This option is used together with the USE_CONVERT_CHAR_TO_VARCHAR option. In this option should be set a number that will mean that all the CHAR data types which length is greater than specified number, will be converted to VARCHAR2.
SQLLDR_CHARACTER_SET This option defines the encoding that will be used to store the extracted data in the files in export folder and then load this data in Oracle database.

Example:

[oracle]
convert_clustered_index_to=IOT


Ispirer Home Page Ispirer SQLWays Product Page Request SQLWays

sqlways/command-line/sqlways-ini/oracle-section.txt · Last modified: July 19, 2017, 12:45:41 AM by anastasiya.shpakava
 
© 1999-2018, Ispirer Systems Ltd.
All Rights Reserved.  Privacy Statement