About Ispirer Systems

Ispirer Home Page Database Migration Application Conversion Downloads


Sqlways.ini File - Section [ORACLE]

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

Option name Description
BIN Specifies the directory where 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 Oracle database. If this option is empty the latest Oracle database version will be used.
Note: According to the version number conversion results may differ. 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.
By 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.
By default the trigger that automatically inserts value into 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 to “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 to “Yes”, DECIMAL function will be converted to TRUNC.
With the option set to “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. Please set a number in this option, so that it will mean that all the CHAR data types which are longer than the 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 to Oracle database.
GENERATE_AUTO_INCREMENT_COLUMN This option defines how IDENTITY columns will be converted into Oracle database. If this option is set to “ALWAYS”, “GENERATED ALWAYS AS IDENTITY” will be used in Oracle, if “ON NULL”, then “GENERATED BY DEFAULT ON NULL AS IDENTITY”, and if we set this option to “DEFAULT”, then “GENERATED BY DEFAULT AS IDENTITY” will be used in table definition in Oracle.
Possible values - “ALWAYS”, “ON NULL” and “DEFAULT”. Default value - “DEFAULT”.
OVERLOAD_FN_TO_PKG This option controls the convertion of overloaded functions into Oracle database. If this option is set to “Yes”, these functions will be converted to a package with converted overloaded functions.
Possible values - “Yes”, “No” or Empty.
Default value - “No”.
DML_COMMIT If this option is set to “Yes”, our tool will add “COMMIT;” statement after each DML statement (INSERT, UPDATE, DELETE) in the converted Oracle code.
Possible values - “Yes”, “No” or Empty.
Default values - “No” or Empty.
CONVERT_ROUTINE_TO_SP_RESULTSET If it's set to “Yes”, that means that all the functions and procedures that return values must be converted to stored procedures that return resultset via the SELECT statement.
The default value is “No” or Empty. Possible values - “Yes”, “No”.
OUT_REFCUR_FIRST_POSITION This option works together with CONVERT_ROUTINE_TO_SP_RESULTSET and puts OUT parameter to the first position in the parameter list. Please also note, that this option will work only for Oracle version 11 and lower. For Oracle version 12 and higher OUT parameter will not be generated and PIPELINE functionality will be used.
Possible values - “Yes”, “No” or Empty.
Default values - “No” or Empty.
GEN_TRIG_DATETIME_PART If this option is set to “Yes”, SQLWays Wizard will generate trigger to store the required part of the date.
Possible values - “Yes”, “No” or Empty.
Default values - “No” or Empty.
INTERVAL_TYPE_TO_NUMBER This options converts Informix INTERVAL data type to NUMBER and DATE data types in Oracle, when it is set to “Yes”. If this option is set to “No”, SQLWays Wizard will convert INTERVAL data types into appropriate INTERVAL data types in Oracle.
Possible values - “Yes”, “No” or Empty.
Default values - “No” or Empty.
MAX_ERRORS Defines maximum number of errors that are allowable after which the loader process exits. When this option is empty default value that is equal to 50 will be used.
BINDSIZE The BINDSIZE parameter specifies the maximum size (in bytes) of the bind array for SQLLDR utility.
STREAMSIZE The STREAMSIZE parameter specifies the size (in bytes) of the data stream sent from the client to the server.
OPTIONS_CLAUSE In the OPTIONS_CLAUSE option can be specified additional SQLLDR options that will be used during the data import process. Empty by default.
LOAD_UNRECOVERABLE If this option is set to “Yes”, data load process will be performed in the UNRECOVERABLE mode.
Possible values - “Yes”, “No” or Empty.
Default value - “No” or Empty.
TABLESPACE In this option can be specified tablespace name that will be used in CREATE TABLE statements in Oracle.
Default value - Empty.
INDEX_TABLESPACE In this option you can specify tablespace name that will be used in CREATE INDEX statements in Oracle.
Default value - Empty.
USE_SOURCE_TABLESPACE_NAMES This option defines whether the original tablespace names will be used in the converted object definitions.
Possible values - “Yes”, “No” or Empty.
Default value - “No” or Empty.
PREFETCH_SIZE This option identifies the number of rows you want to read from the datafile before data save for Direct path loads. Default is to read all the rows and save data at the end of the load.
CONVERT_LONG_RAW_TO_BLOB This option controls whether to convert LONG RAW data type to BLOB.
Possible values - “Yes”, “No” or Empty.
Default value - “Yes”.
SP_RESULTSET_AS_OUT_REFCURSOR Specifies how the results sets should be returned from procedure. If No specified, the SYS_REFCURSOR variables will be declared, if Yes - the OUT SYS_REFCURSOR parameters will be add for procedure.
The default value: No.
RETURN_TABLE_SQL_MACRO By default, IspirerToolkit converts table-returning functions to pipelined ones.
This option changes conversion of return value to RETURN varchar2 SQL_MACRO(TABLE).
Possible values - Yes, No and <empty>.
Default value - <empty>. Usage example
CONCAT_NULL_STRINGS_AS_NULL By default, IspirerToolkit converts string concatenation as is. But in order to reproduce the same behavior in the case of null strings in Oracle and PostgreSQL, we can add a check for the presence of nulls among the concatinating strings. And if there are null values, the resulting string becomes null.
Possible values - Yes, No and <empty>.
Default value - <empty>. Usage example.
AUTONOMOUS_TRANS To avoid the error “ORA-14551: cannot perform a DML operation inside a query” you should use the PRAGMA AUTONOMOUS_TRANSACTION to perform the DML operation.
If you don't need to add PRAGMA, please set this option to No.
Possible values - Yes, No and <empty>.
Default value - <empty>. Usage example.

Example:

[oracle]
convert_clustered_index_to=IOT