Converting Oracle Schemas (OracleToSQL)
After you have connected to Oracle, connected 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 SSMA metadata. It does not 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.
Setting Conversion Options
Before converting objects, review the project conversion options in the Project Settings dialog box. By using this dialog box, you can set how SSMA converts functions and global variables. For more information, see Project Settings (Conversion) (OracleToSQL).
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 is used for calling the implementing stored procedure. |
| 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. |
| View 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 will fail if the materialized view includes one or more of the following constructs: User-defined function Nondeterministic field / function / 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 (incl. nested tables) COUNT(distinct <field>) FETCH OUTER joins (LEFT, RIGHT, or FULL) Subquery, other view OVER, RANK, LEAD, 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 using cursors. Compound triggers are converted to INSTEAD OF triggers. Multiple compound triggers are combined into 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 cannot be migrated and will be marked as errors. Synonyms are not created for Materialized views. |
| User Defined Types | SSMA does not 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 user defined type to a stored procedure or function is converted to VARCHAR(8000). Variable of 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. |
Converting Oracle Database Objects
To convert Oracle database objects, you first 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.
To 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.
Viewing Conversion Problems
Some Oracle objects might not be converted. You can determine the conversion success rates by viewing the summary conversion report.
To 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 have been 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.
To view individual conversion problems
In Oracle Metadata Explorer, expand Schemas.
Expand the schema that shows a red error icon.
Under the schema, expand a folder that has a red error icon.
Select the object that has a red error icon.
In the right pane, click the Report tab.
At the top of the Report tab is a drop-down list. If the list shows Statistics, change the selection to Source.
SSMA will display the source code and several buttons immediately above the code.
Click the Next Problem button. This is a red error icon with an arrow that points to the right.
SSMA will highlight the first problematic source code it finds in the current object.
For each item that could not be converted, you have to determine 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 will have to update the metadata. For more information, see Connecting to Oracle Database (OracleToSQL).
You can exclude the object from migration. In SQL Server Metadata Explorer and Oracle Metadata Explorer, clear the check box next to the item before loading the objects into SQL Server and migrating data from Oracle.
Next Step
The next step in the migration process is to Load the converted objects into SQL Server.