When converting Oracle database code to Microsoft SQL Server, I often face the problem of correctly mapping Oracle data types to SQL Server data types. I regularly use SQL Server Migration Assistant (SSMA) to automate Oracle database code conversion to SQL Server. However, SSMA’s default data type mapping for Oracle procedure and function parameters uses the maximum possible size for each specific data type to prevent data loss. This approach causes unintended consequences with application code.
The Problem
In Oracle, you cannot specify the length, precision, scale of procedure, or function parameters. For example, you can use VARCHAR2 data type but you cannot use the following definition: VARCHAR2(10). This results in the inaccurate conversion of Oracle data types to SQL Server. (See the Oracle Database help topic Coding PL/SQL Subprograms and Packages for more information).
Possible Risks
Another issue relates to using third-party applications like ETL tools or reporting systems. For example, Informatica or IBM InfoSphere DataStage treats VARCHAR (max) data type as if they are LOB. So, you must review the data types mapping, especially when SSMA converts VARCHAR2 to VARCHAR(max) or NUMBER to FLOAT (53). Also, if you rely on the default type mapping from SSMA you may need to recreate target tables and rewrite the code later.
The Solution
I realized that the best solution is to define real arguments length or precision based on the table’s columns size. To do so, you must perform a thorough database analysis.
Below are the data types mapping best practices that work on multiple 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) |
For the practices above, you will need to analyze the source code and discover the solution that fits your needs. For example, you can set the length of varchar in SQL Server in the range from 1 to 8000. At the same time, the max value indicates that the maximum storage size is set to 2 GB.
Converting NUMBER data type from Oracle, you can use int, smallint, and bigint data types. And you can also use the float data type. However, not all values in the data type range can be correctly represented. So, you should analyze the source code and then select the right SQL Server data type.
Finally, the datetime2 data type combines the date with the time. This type is based on a 24-hour clock and the dates range includes the following values: from 0001-01-01 to 9999-12-31. The time range goes all the way from 00:00:00 to 23:59:59.9999999. Depending on your application, you should consider the precision needed for the datetime2 data type. Please refer to the help topic for datetime2 for more information.
