Note
Access to this page requires authorization. You can try signing in or changing directories.
Access to this page requires authorization. You can try changing directories.
After you connect to Oracle, connect to SQL Server, and set project and data mapping options, you can convert Oracle database objects to SQL Server database objects.
The conversion process
Converting database objects takes the object definitions from Oracle, converts them to similar SQL Server objects, and then loads this information into the metadata for Microsoft SQL Server Migration Assistant (SSMA) for Oracle. It doesn't load the information into the instance of SQL Server. You can then view the objects and their properties by using the SQL Server Metadata Explorer.
During the conversion, SSMA prints output messages to the Output pane and error messages to the Error List pane. Use the output and error information to determine whether you have to modify your Oracle databases or your conversion process to obtain the desired conversion results.
Set conversion options
Before converting objects, review the project conversion options in the Project Settings dialog box. By using this dialog box, you can determine how SSMA converts functions and global variables. For more information, see Project Settings (Conversion).
Conversion results
The following table shows which Oracle objects are converted, and the resulting SQL Server objects:
| Oracle objects | Resulting SQL Server objects |
|---|---|
| Functions | If the function can be directly converted to Transact-SQL, SSMA creates a function. In some cases, the function must be converted to a stored procedure. In this case, SSMA creates a stored procedure and a function that calls the stored procedure. |
| Procedures | If the procedure can be directly converted to Transact-SQL, SSMA creates a stored procedure. In some cases, a stored procedure must be called in an autonomous transaction. In this case, SSMA creates two stored procedures: one that implements the procedure, and another that calls the procedure that implements. |
| Packages | SSMA creates a set of stored procedures and functions that are unified by similar object names. |
| Sequences | SSMA creates sequence objects (SQL Server 2012 or SQL Server 2014) or emulates Oracle sequences. |
| Tables with dependent objects such as indexes and triggers | SSMA creates tables with dependent objects. |
| Views with dependent objects, such as triggers | SSMA creates views with dependent objects. |
| Materialized views | SSMA creates indexed views on SQL Server with some exceptions. Conversion fails if the materialized view includes one or more of the following constructs: User-defined function. Nondeterministic field, function, or expression in SELECT, WHERE, or GROUP BY clauses.Usage of Float column in SELECT*, WHERE, or GROUP BY clauses (special case of previous issue).Custom data type (including nested tables). COUNT (distinct <field>).FETCH.OUTER joins (LEFT, RIGHT, or FULL).Subquery, other view. OVER, RANK, LEAD, or LOG.MIN, MAX.UNION, MINUS, INTERSECT.HAVING. |
| Trigger | SSMA creates triggers based on the following rules:BEFORE triggers are converted to INSTEAD OF triggers.AFTER triggers are converted to AFTER triggers.INSTEAD OF triggers are converted to INSTEAD OF triggers. Multiple INSTEAD OF triggers defined on the same operation are combined into one trigger.Row-level triggers are emulated by using cursors. Compound triggers are converted to INSTEAD OF triggers. Multiple compound triggers are combined into a single trigger.Cascading triggers are converted into multiple individual triggers. |
| Synonyms | Synonyms are created for the following object types: Tables and object tables. Views and object views. Stored procedures. Functions. Synonyms for the following objects are resolved and replaced by direct object references: Sequences. Packages. Java class schema objects. User-defined object types. Synonyms for another synonym can't be migrated and are marked as errors. Synonyms aren't created for materialized views. |
| User-defined types | SSMA doesn't provide support for conversion of user-defined types. User-defined types, including its usage in PL/SQL programs, are marked with special conversion errors guided by the following rules: Table column of a user-defined type is converted to VARCHAR(8000).Argument of a user-defined type to a stored procedure or function is converted to VARCHAR(8000).Variable of a user-defined type in PL/SQL block is converted to VARCHAR(8000).Object table is converted to a standard table. Object view is converted to a standard view. |
Convert Oracle database objects
To convert Oracle database objects, select the objects that you want to convert, and then have SSMA perform the conversion. To view output messages during the conversion, on the View menu, select Output.
Convert Oracle objects to SQL Server syntax
In Oracle Metadata Explorer, expand the Oracle server, and then expand Schemas.
Select objects to convert:
- To convert all schemas, select the check box next to Schemas.
- To convert or omit a database, select the check box next to the schema name.
- To convert or omit a category of objects, expand a schema, and then select or clear the check box next to the category.
- To convert or omit individual objects, expand the category folder, and then select or clear the check box next to the object.
To convert all selected objects, right-click Schemas and select Convert Schema.
You can also convert individual objects or categories of objects by right-clicking the object or its parent folder, and then selecting Convert Schema.
View conversion problems
Some Oracle objects might not be converted. You can determine the conversion success rates by viewing the summary conversion report.
View a summary report
In Oracle Metadata Explorer, select Schemas.
In the right pane, select the Report tab.
This report shows the summary assessment report for all database objects that were assessed or converted. You can also view a summary report for individual objects.
- To view the report for an individual schema, select the schema in Oracle Metadata Explorer.
- To view the report for an individual object, select the object in Oracle Metadata Explorer. Objects that have conversion problems have a red error icon.
For objects that failed conversion, you can view the syntax that resulted in the conversion failure.
View individual conversion problems
In Oracle Metadata Explorer, expand Schemas.
Expand the schema with a red error icon.
Under the schema, expand a folder with a red error icon.
Select the object with a red error icon.
In the right pane, select the Report tab.
There's a dropdown list at the top of the Report tab. If the list shows Statistics, change the selection to Source. SSMA displays the source code and several buttons immediately above the code.
Select the Next Problem button, which displays a red error icon with an arrow that points to the right. SSMA highlights the first problematic source code it finds in the current object.
For each item that couldn't be converted, choose what you want to do with that object:
- You can modify the source code for procedures on the SQL tab.
- You can modify the object in the Oracle database to remove or revise problematic code. To load the updated code into SSMA, you have to update the metadata. For more information, see Connecting to Oracle Database.
- You can exclude the object from migration. In SQL Server Metadata Explorer and Oracle Metadata Explorer, clear the check box next to the item. Then load the objects into SQL Server and migrate data from Oracle.