Use caution when mapping Oracle data types for procedures and function parameters in SSMA
Mapping Oracle data types to SQL Server data types is often a problem during Oracle to Microsoft SQL Server database code conversions. A tried and trusted solution is to use an SQL Server database code conversion. Another reliable solution is to use SQL Server Migration Assistant. These solutions enable you to automate Oracle database conversions to SQL Server.
However, there might be some issues you should consider while using SSMA. For example, by default, SSMA applies the maximum available size for each specific data type when it comes to data type mapping for the Oracle procedure. Such an approach prevents data loss, but it may also lead to some changes in the application code.
The problem
A common issue is that Oracle does not allow you to specify the precision, length, and scale of procedure or function parameters. For instance, you might use the VARCHAR2 data type but there is no option to use such a definition as VARCHAR2(10). As a result, there may be problems with accurately converting Oracle data types to SQL Server. For more information on this problem, make sure to check out the Oracle database help topic Coding PL/SQL Subprograms and Packages.
Possible risks
There are also some risks pertaining to the use of third-party apps, such as reporting systems or ETL tools. Consider the example of Informatica or IBM InfoSphere DataStage. It treats VARCHAR (max) data types as if they are LOB. This means that you should always review the data types mapping. It is especially relevant when SSMA converts NUMBER to FLOAT (53) or VARCHAR2 to VARCHAR(max).
In addition, you may be required to recreate target tables and rewrite the code after SSMA conducts the default type mapping.
The solution
The best solution for such issues is to define real arguments precision or length according to the size of table columns. In this case, a thorough database analysis is a must.
Check the table below to see the best practices for data types mapping that fit diverse Oracle to SQL Server migration projects.
| Oracle data type | SSMA suggestion | SQL Server data type |
| VARCHAR2 | varchar(max) | varchar(8000) |
| NUMBER | float(53) | numeric (25,15) |
| DATE | datetime2(0) | datetime2(0) |
Analyze the source code to discover the most suitable solution for your needs to implement the above-mentioned practice.
When you convert NUMBER data types from Oracle, you might use int, smallint, and bigint data types. Also, you may use the float data types. Remember, you must analyze the source code to select the right SQL Server data type. The point is that not all data type values can be correctly represented during the conversion.
In addition, pay special attention to the datetime2 data type. It combines the date with the time. This data type is based on a 24-hour clock, while its date range includes the values from 0001-01-01 to 9999-12-31. Make sure to determine the precision needed for the datetime2 data type. Check out this link for more information on day and time data types for SQL Server 2019.
Final thoughts
Mapping Oracle data types to SQL Server data types is an essential step in your database migration. Meanwhile, you should also pay attention to the definition of a naming convention and the conversion of Oracle sequences to SQL Server.
Questions? Don’t hesitate to contact us for valuable advice and to see how we can facilitate your database migration.
