One of the most important steps of database migration from Oracle to Microsoft SQL server is processing views. Syntax of CREATE VIEW queries in Oracle and MS SQL is similar but not the same. The primary challenge of conversion process is that these two DBMS have distinguished sets of built-in functions. Consequently, each CREATE VIEW statement must be converted into the appropriate equivalent before importing into the destination database. This article discovers the most important patterns of converting views from Oracle to SQL Server.
1) Oracle provides specific notation for LEFT OUTER JOIN, it is operator (+). In Microsoft SQL this operator must be replaced by LEFT JOIN
2) Oracle’s function TO_DATE that converts string expression to date/time must be replaced by CONVERT in Microsoft SQL
3) Function TRIM($string) that trims leading and trailing spaces in Oracle must be replaced by combination of MS SQL functions LTRIM(RTRIM($string))
4) Function nvl($var, $expr) that returns $expr if $var is NULL in Oracle, must be replaced by Microsoft SQL equivalent ISNULL($var, $expr)
5) Begin enhancement of the previous function, nvl2($var,$expr1,$expr2) returns $arg2 if $var is NULL and $arg1 if $var is not NULL. It must be replaced by the following expression in SQL Server:
CASE WHEN $var IS NOT NULL THEN $arg1 ELSE $arg2 END
6) Function SYSDATE returns the current date/time in Oracle, it must be replaced by GETDATE()
7) Oracle does not require aliases when using subqueries. Microsoft SQL applies stricter limitations, each subquery must have an alias. For example:
SELECT id,
(SELECT COUNT(*) FROM Queue WHERE Queue.id = Server.id) AS cnt
FROM Server
8) Function ADD_MONTHS($datetime, $n) that adds $n months to $datetime in Oracle. It must be replaced by DATEADD($month, $n, datetime) in SQL Server
9) Oracle’s function EXTRACT that can get specified part of date must be replaced in SQL Server as follows:
EXTRACT(YEAR FROM datetime) -> YEAR(datetime)
EXTRACT(MONTH FROM datetime) -> MONTH(datetime)
EXTRACT(DAY FROM datetime) -> DAY(datetime)
10) Oracle’s function INSTR get position of substring in the specified string, it has direct equivalent in MS SQL called CHARINDEX
For those who find manual converting Oracle queries into MS SQL format too complicated, it is reasonable to use special tools to automate the procedure. One of such migration tools is Oracle to MS SQL converter offered by Intelligent Converters, software vendor working in database migration field more than 15 years. The tool handles more than 80% of all possible syntax constructions used in CREATE VIEW queries. The price for the product is $59 that includes lifetime license, 1-year subscription for updates and 24/7 support.
More details about Oracle to SQL Server converter can be found on the official page: https://www.convert-in.com/ora2mss.htm
