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