Project Settings (Conversion) (OracleToSQL)
The Conversion page of the Project Settings dialog box contains settings that customize how SSMA converts Oracle syntax to SQL Server syntax.
The Conversion pane is available in the Project Settings and Default Project Settings dialog boxes:
To specify settings for all SSMA projects, on the Tools menu click Default Project Settings, select migration project type for which settings are required to be viewed or changed from Migration Target Version drop-down, then click General at the bottom of the left pane, and then click Conversion.
To specify settings for the current project, on the Tools menu click Project Settings, then click General at the bottom of the left pane, and then click Conversion.
Built-in functions and supplied packages
| Term | Definition |
|---|---|
| Convert COUNT function to COUNT_BIG | If your COUNT functions are likely to return values larger than 2,147,483,647, which is 231-1, you should convert the functions to COUNT_BIG.If you select Yes, SSMA will convert all uses of COUNT to COUNT_BIG.If you select No, the functions will remain as COUNT. SQL Server will return an error if the function returns a value larger than 231-1.When you select a conversion mode in the Mode box, SSMA applies the following setting: Default/Full Mode: Yes Optimistic Mode: No |
| Convert SUBSTR function calls to SUBSTRING function calls | SSMA can convert Oracle SUBSTR function calls into SQL Server substring function calls, depending on the number of parameters. If SSMA can't convert a SUBSTR function call, or the number of parameters isn't supported, SSMA will convert the SUBSTR function call into a custom SSMA function call.If you select Yes, SSMA will convert SUBSTR function calls that use three parameters into SQL Server substring. Other SUBSTR functions will be converted to call the custom SSMA function.If you select No, SSMA will convert the SUBSTR function call into a custom SSMA function call.When you select a conversion mode in the Mode box, SSMA applies the following setting: Default/Optimistic Mode: Yes Full Mode: No |
| Convert TO_CHAR(date, format) function calls | SSMA can convert Oracle TO_CHAR(date, format) into procedures from ssma_oracle schema.If you select Using TO_CHAR_DATE function, SSMA converts the TO_CHAR(date, format) into TO_CHAR_DATE function using of English language for conversion.If you select Using TO_CHAR_DATE_LS function (NLS care), SSMA converts the TO_CHAR(date, format) into TO_CHAR_DATE_LS function using of session language for conversionWhen you select a conversion mode in the Mode box, SSMA applies the following setting: Default/Optimistic Mode: Using TO_CHAR_DATE function Full Mode: Using TO_CHAR_DATE_LS function (NLS care) |
| Generate error for DBMS_SQL.PARSE | If you select Error, SSMA generates error at the conversion DBMS_SQL.PARSE.If you select Warning, SSMA generates warning at the conversion DBMS_SQL.PARSE.When you select a conversion mode in the Mode box, SSMA applies the following setting: Default/Optimistic/Full Mode: Error |
| Use ISNULL in CONCAT function calls | ISNULL statement is used in CONCAT function calls to emulate Oracle behavior. The following options are present for this setting:YES NO When you select a conversion mode in the Mode box, SSMA applies the following setting: Default/Optimistic Mode: No Full Mode: Yes |
| Use ISNULL in REPLACE function calls | ISNULL statement is used in REPLACE function calls to emulate Oracle behavior. The following options are present for this setting:YES NO When you select a conversion mode in the Mode box, SSMA applies the following setting: Default/Optimistic Mode: No Full Mode: Yes |
| Use native convert function when possible | If you select Yes, SSMA converts the TO_CHAR(date, format) into native convert function when possible.If you select No, SSMA converts the TO_CHAR(date, format) into TO_CHAR_DATE or TO_CHAR_DATE_LS (It's defined by Convert TO_CHAR(date, format) options).When you select a conversion mode in the Mode box, SSMA applies the following setting: Default/Optimistic Mode: Yes Full Mode: No |
Conversion Messages
| Term | Definition |
|---|---|
| Generate messages about issues | Specifies whether SSMA generates informational messages during conversion, displays them in the Output pane, and adds them to the converted code. When you select a conversion mode in the Mode box, SSMA applies the following setting: Default/Optimistic Mode: No Full Mode: No |
Miscellaneous Options
| Term | Definition |
|---|---|
| Cast ROWNUM expressions as integers | When SSMA converts ROWNUM expressions, it converts the expression into a TOP clause, followed by the expression. The following example shows ROWNUM in an Oracle DELETE statement:DELETE FROM Table1WHERE ROWNUM < expression and Field1 >= 2The following example shows the resulting Transact-SQL: DELETE TOP (expression-1)FROM Table1WHERE Field1>=2The TOP requires that the TOP clauses expression evaluates to an integer. If the integer is negative, the statement will produce an error.If you select Yes, SSMA casts the expression as an integer. If you select No, SSMA will mark all non-integer expressions as an error in the converted code. When you select a conversion mode in the Mode box, SSMA applies the following setting: Default/Full Mode: No Optimistic Mode: Yes |
| Default Schema Mapping | This setting specifies how Oracle schemas are mapped to SQL Server schemas. Two options are available in this setting: Schema to database: In this mode Oracle schema sch1 will be mapped by default to dbo SQL Server schema in SQL Server database sch1.Schema to schema: In this mode Oracle schema sch1 will be mapped by default to sch1 SQL Server schema in default SQL Server database provided in the connection dialog.When you select a conversion mode in the Mode box, SSMA applies the following setting: Default/Optimistic/Full Mode: Schema to database |
| Emulate Oracle null behavior in ORDER BY clauses | NULL values are ordered differently in SQL Server and Oracle:In SQL Server, NULL values are the lowest values in an ordered list. In an ascending list, NULL values will appear first.In Oracle, NULL values are the highest values in an ordered list. By default, NULL values appear last in an ascending-order list.Oracle has NULLS FIRST and NULLS LAST clauses, which enables you to change how Oracle orders NULLs.SSMA can emulate Oracle ORDER BY behavior by checking for NULL values. It then first orders by NULL values in the specified order, and then orders by other values.If you select Yes, SSMA will convert the Oracle statement in a way that emulates Oracle ORDER BY behavior.If you select No, SSMA will ignore Oracle rules and generate an error message when it encounters the NULLS FIRST and NULLS LAST clauses.When you select a conversion mode in the Mode box, SSMA applies the following setting: Default/Optimistic Mode: No Full Mode: Yes |
| Emulate row count exceptions in SELECT | If a SELECT statement with an INTO clause doesn't return any rows, Oracle raises a NO_DATA_FOUND exception. If the statement returns two or more rows, the TOO_MANY_ROWS exception is raised. The converted statement in SQL Server doesn't raise any exception if the row count is different from one.If you select Yes, SSMA adds call to special db_error_exact_one_row_check procedure after each SELECT statement. This procedure emulates the NO_DATA_FOUND and TOO_MANY_ROWS exceptions. This is the default and it allows reproducing Oracle behavior as close as possible. You should always choose Yes if the source code has exception handlers that process these errors. Note that if the SELECT statement occurs inside a user-defined function, this module will be converted to a stored procedure, because executing stored procedures and raising exceptions isn't compatible with SQL Server function context.If you select No, no exceptions will be generated. That can be useful when SSMA converts a user-defined function and you want it to remain a function in SQL Server When you select a conversion mode in the Mode box, SSMA applies the following setting: Default/Optimistic/Full Mode: Yes |
| Enable Fix Advisor | When enabled, SSMA will try to learn from the modifications you make in the target T-SQL code and suggest you potential code fixes in another places, where similar pattern can be applied. When you select a conversion mode in the Mode box, SSMA applies the following setting: Default/Optimistic/Full Mode: Yes |
| Generate constant expression column aliases | If expression in SELECT list is missing an alias, SSMA can generate constant alias (like expr1, expr2, etc.) or use expression itself as an alias. Since expressions can get pretty long and column name length is limited, it's safer to use constant base name for such aliases. Even though it's a safer option, sometimes it isn't possible, because there could be external dependencies on the resulting dataset. In those cases you may want to name columns according to their value expressions, similar to Oracle's behavior.When you select a conversion mode in the Mode box, SSMA applies the following setting: Default/Optimistic mode: Yes Full Mode: No |
| Omit Extended Properties | When enabled, SSMA will not add extended properties to the objects it creates in the target database. When you select a conversion mode in the Mode box, SSMA applies the following setting: Default/Optimistic/Full Mode: No |
| Translate error codes | When enabled, error number on target SQL Server side will be translated to Oracle error code if the mapping is found. When you select a conversion mode in the Mode box, SSMA applies the following setting: Default/Full Mode: Yes Optimistic Mode: No |
| Use full type specification for type references | When enabled, SSMA will respect full type specification (including scale and precision) for routine parameters and return values. Oracle doesn't allow data type arguments for routine parameters, but there are cases where they can be implicitly derived, for example when %TYPE and %ROWTYPE attributes are used. In such cases SSMA can use full type specification (including precision and scale) when converting it to SQL Server.When you select a conversion mode in the Mode box, SSMA applies the following setting: Default/Optimistic Mode: Yes Full Mode: No |
| Use ISNULL in string concatenation | Oracle and SQL Server return different results when string concatenations include NULL values. Oracle treats the NULL value like an empty character set. SQL Server returns NULL.If you select Yes, SSMA replaces the Oracle concatenation character (||) with the SQL Server concatenation character (+). SSMA also checks the expressions on both sides of the concatenation for NULL values.If you select No, SSMA replaces the concatenation characters, but doesn't check for NULL values.When you select a conversion mode in the Mode box, SSMA applies the following setting: Default/Optimistic/Full Mode: Yes |
Objects conversion
| Term | Definition |
|---|---|
| Convert foreign keys with SET NULL referential action on column that is NOT NULL | Oracle allows creating foreign key constraints, where a SET NULL action could not possibly be performed because NULLs are not permitted in the referenced column. SQL Server doesn't allow such foreign key configuration.If you select Yes, SSMA will generate referential actions as in Oracle, but you will need to make manual changes before loading the constraint to SQL Server. For example, you can choose NO ACTION instead of SET NULL.If you select No, the constraint will be marked as an error. When you select a conversion mode in the Mode box, SSMA applies the following setting: Default/Optimistic/Full Mode: No |
| Convert subtypes | SSMA can convert PL/SQL subtypes in two ways: If you select Yes, SSMA will create SQL Server user-defined type from a subtype and use it for each variable of this subtype. If you select No, SSMA will substitute all source declarations of the subtype with the underlying type and convert the result as usual. In this case, no additional types are created in SQL Server When you select a conversion mode in the Mode box, SSMA applies the following setting: Default/Optimistic/Full Mode: No |
| Convert synonyms | Synonyms for the following Oracle objects can be migrated to SQL Server: Tables and object tables Views and object views Stored procedures and functions Materialized views Synonyms for the following Oracle objects can be replaced by direct references to the objects: Sequences Packages Java class schema objects User-defined object types Other synonyms can't be migrated. SSMA will generate error messages for the synonym and all references that use the synonym. If you select Yes, SSMA will create SQL Server synonyms and direct object references according to the previous lists. If you select No, SSMA will create direct object references for all synonyms listed here. When you select a conversion mode in the Mode box, SSMA applies the following setting: Default/Optimistic/Full Mode: Yes |
| Local modules conversion | Defines the type of Oracle nested subprogram (declared in standalone stored procedure or function) conversion. If you select Inline, the nested subprogram calls will be replaced by its body. If you select Stored procedures, nested subprogram will be converted to a SQL Server stored procedure, and its calls will be replaced on this procedure call. When you select a conversion mode in the Mode box, SSMA applies the following setting: Default/Optimistic/Full Mode: Inline |
Records conversion
| Term | Definition |
|---|---|
| Convert record as a list of separates variables | SSMA can convert Oracle records into separates variables and into XML variables with specific structure. If you select Yes, SSMA converts the record into a list of separates variables when possible. If you select No, SSMA converts the record into XML variables with specific structure. When you select a conversion mode in the Mode box, SSMA applies the following setting: Default/Optimistic/Full Mode: Yes |
| Use SELECT...FOR XML when converting SELECT...INTO for record variable | Specifies whether to generate an XML result set when you select into a record variable. If you select Yes, the SELECT statement returns XML. If you select No, the SELECT statement returns a result set. When you select a conversion mode in the Mode box, SSMA applies the following setting: Default/Optimistic/Full Mode: No |
RETURNING Clause Conversion
| Term | Definition |
|---|---|
| Convert RETURNING clause in DELETE statement to OUTPUT | Oracle provides a RETURNING clause as a way to immediately obtain deleted values. SQL Server provides that functionality with the OUTPUT clause.If you select Yes, SSMA will convert RETURNING clauses in DELETE statements to OUTPUT clauses. Because triggers on a table can change values, the returned value might be different in SQL Server than it was in Oracle.If you select No, SSMA will generate a SELECT statement before DELETE statements to retrieve returned values.When you select a conversion mode in the Mode box, SSMA applies the following setting: Default/Optimistic/Full Mode: Yes |
| Convert RETURNING clause in INSERT statement to OUTPUT | Oracle provides a RETURNING clause as a way to immediately obtain inserted values. SQL Server provides that functionality with the OUTPUT clause.If you select Yes, SSMA will convert a RETURNING clause in an INSERT statement to OUTPUT. Because triggers on a table can change values, the returned value might be different in SQL Server than it was in Oracle.If you select No, SSMA emulates Oracle functionality by inserting and then selecting values from a reference table. When you select a conversion mode in the Mode box, SSMA applies the following setting: Default/Optimistic/Full Mode: Yes |
| Convert RETURNING clause in UPDATE statement to OUTPUT | Oracle provides a RETURNING clause as a way to immediately obtain updated values. SQL Server provides that functionality with the OUTPUT clause.If you select Yes, SSMA will convert RETURNING clauses in UPDATE statements to OUTPUT clauses. Because triggers on a table can change values, the returned value might be different in SQL Server than it was in Oracle.If you select No, SSMA will generate SELECT statements after UPDATE statements to retrieve returning values.When you select a conversion mode in the Mode box, SSMA applies the following setting: Default/Optimistic/Full Mode: Yes |
ROWID generation
| Term | Definition |
|---|---|
| Generate ROWID column | When SSMA creates tables in SQL Server, it can create a ROWID column. When data is migrated, each row obtains a new UNIQUEIDENTIFIER value generated by the newid() function.If you select Yes, the ROWID column is created on all tables and SQL Server generates GUIDs as your insert values. Always choose Yes if you are planning to use the SSMA Tester.If you select No, ROWID columns are not added to tables. Add ROWID column for tables with triggers add ROWID for the tables containing triggers.When you select a conversion mode in the Mode box, SSMA applies the following setting: Default/Optimistic Mode: Add ROWID column for tables with triggers Full Mode: Yes |
| Generate unique index on ROWID column | Specifies whether SSMA generates unique index column on the ROWID generated column or not. If the option is set to "YES", unique index is generated and if it's set to "NO", unique index isn't generated on the ROWID column.When you select a conversion mode in the Mode box, SSMA applies the following setting: Default/Optimistic/Full Mode: Yes |
Sequence and Identity Conversion
| Term | Definition |
|---|---|
| Convert identity as | Oracle provides multiple configuration options for identity columns. Some of these options are not supported by the identity feature in SQL Server. A method for preserving these options is to convert identity as a sequence. If you select Sequence, Oracle identity columns will no longer convert to SQL identity columns. Instead, a sequence will be created and used to generate default values for the column. If you select Identity, Oracle identity columns will be converted to SQL identity columns. Unsupported options will not be converted. If you select Best Fit, SSMA will determine the best fit conversion method (Identity or Sequence) depending on the configuration of the Oracle identity column. |
| Convert Sequence Generator | In Oracle, you can use a Sequence to generate unique identifiers. SSMA can convert Sequences to the following. Using SQL Server sequence generator. Using SSMA sequence generator. Using column identity. The default option is to use SQL Server sequence generator. However, SQL Server doesn't support obtaining current sequence value (such as that of Oracle sequence CURRVAL method). Refer to SSMA team blog site for guidance on migrating Oracle sequence CURRVAL method.SSMA also provides an option to convert Oracle sequence to SSMA sequence emulator. This is the default option when you convert to SQL Server prior to 2012 Finally, you can also convert sequence assigned to a column in table to SQL Server identity values. You must specify the mapping between the sequences to an identity column on Oracle Table tab |
| Convert CURRVAL outside triggers | Visible only when the Convert Sequence Generator is set to Using column identity. Because Oracle Sequences are objects separate from tables, many tables that use Sequences use a trigger to generate and insert a new sequence value. SSMA comments out these statements, or marks them as errors when the commenting out would generate errors. If you select Yes, SSMA will mark all references to outside triggers on the converted sequence CURRVAL with a warning.If you select No, SSMA will mark all references to outside triggers on the converted sequence CURRVAL with an error. |
Statements conversion
| Term | Definition |
|---|---|
| Conversion of MERGE statement | If you select Using INSERT, UPDATE, DELETE statement, SSMA converts MERGE statement into INSERT, UPDATE, DELETE statements.If you select Using MERGE statement, SSMA converts MERGE statement into MERGE statement in SQL Server.When you select a conversion mode in the Mode box, SSMA applies the following setting: Default/Optimistic/Full Mode: Using MERGE statement |
| Convert calls to subprograms that use default arguments | SQL Server functions do not support the omission of parameters in the function call. Also, SQL Server functions and procedures do not support expressions as default parameter values. If you select Yes and a function call omits parameters, SSMA will insert the keyword default into the function and call in the correct position. Then, it will mark the call with a warning. If you select No, SSMA will mark the function calls as errors. When you select a conversion mode in the Mode box, SSMA applies the following setting: Default/Optimistic/Full Mode: Yes |
| Convert FORALL statement to WHILE statement | Defines how SSMA will treat FORALL loops on PL/SQL collection elements.If you select Yes, SSMA creates a WHILE loop where collection elements are retrieved one by one.If you select No, SSMA generates a rowset from the collection using nodes() method and uses it as a single table. This is more efficient, but makes the output code less readable.When you select a conversion mode in the Mode box, SSMA applies the following setting: Default/Optimistic Mode: No Full Mode: Yes |
| Convert function calls to procedure calls | Some Oracle functions are defined as autonomous transactions or contain statements that would not be valid in SQL Server. In these cases, SSMA creates a procedure and a function that is a wrapper for the procedure. The converted function calls the implementing procedure. SSMA can convert calls to the wrapper function into calls to the procedure. This creates more readable code and can improve performance. However, the context doesn't always allow it; for example, you can't replace a function call in SELECT list with a procedure call. SSMA has a few options to cover the common cases:If you select Always, SSMA attempts to convert wrapper function calls into procedure calls. If the current context doesn't allow this conversion, an error message is produced. This way, no function calls are left in the generated code. If you select When possible, SSMA makes a move to procedure calls only if the function has output parameters. When the move isn't possible, parameter's output attribute is removed. In all other cases SSMA leaves function calls. If you select Never, SSMA will leave all function calls as function calls. Sometimes this choice may be unacceptable because of performance reasons. When you select a conversion mode in the Mode box, SSMA applies the following setting: Default/Optimistic/Full Mode: When possible |
| Convert LOCK TABLE statements | SSMA can convert many LOCK TABLE statements into table hints. SSMA can't convert any LOCK TABLE statements that contain PARTITION, SUBPARTITION, @dblink, and NOWAIT clauses, and will mark such statements with conversion error messages.If you select Yes, SSMA will convert supported LOCK TABLE statements into table hints.If you select No, SSMA will mark all LOCK TABLE statements with conversion error messages.The following table shows how SSMA converts Oracle lock modes: Oracle Lock Mode ROW SHAREROW EXCLUSIVESHARE UPDATE = ROW SHARESHARESHAREEXCLUSIVESQL Server Table Hint ROWLOCK, HOLDLOCKROWLOCK, XLOCK, HOLDLOCKROWLOCK, HOLDLOCKTABLOCK, HOLDLOCKTABLOCK, XLOCK, HOLDLOCKTABLOCKX, HOLDLOCKWhen you select a conversion mode in the Mode box, SSMA applies the following setting: Default/Optimistic/Full Mode: Yes |
| Convert OPEN-FOR statements for REF CURSOR OUT parameters | In Oracle, the OPEN .. FOR statement can be used to return a result set to a subprogram's OUT parameter of type REF CURSOR. In SQL Server, stored procedures directly return the results of SELECT statements.SSMA can convert many OPEN .. FOR statements into SELECT statements.If you select Yes, SSMA converts the OPEN .. FOR statement into a SELECT statement, which returns the result set to the client.If you select No, SSMA will generate an error message in the converted code and in the Output pane. When you select a conversion mode in the Mode box, SSMA applies the following setting: Default/Optimistic/Full Mode: Yes |
| Convert transaction processing statements | SSMA can convert Oracle transaction processing statements: If you select Yes, SSMA converts Oracle transaction processing statements to SQL Server statements. If you select No, SSMA marks the transaction processing statements as conversion errors. Note: Oracle opens transactions implicitly. To emulate this behavior on SQL Server, you must add BEGIN TRANSACTION statements manually where you want your transactions to start. Alternatively, you can execute the SET IMPLICIT_TRANSACTIONS ON command at the beginning of your session. SSMA adds SET IMPLICIT_TRANSACTIONS ON automatically when converting subroutines with autonomous transactions.When you select a conversion mode in the Mode box, SSMA applies the following setting: Default/Optimistic/Full Mode: Yes |
See Also
Feedback
Coming soon: Throughout 2024 we will be phasing out GitHub Issues as the feedback mechanism for content and replacing it with a new feedback system. For more information see: https://aka.ms/ContentUserFeedback.
Submit and view feedback for