Learn for Life!

Migrate from Oracle to Microsoft (Views) – Part I


Sometimes you have to migrate from an Oracle database to a Microsoft SQL Server. I’m not going into the reason that could be behind. I had different projects where I had to achieve this goal. The issues that I faced is what I would like to share with you.
Different functions
When it comes to rewrite code from Ora-SQL to MS-SQL, in my case it were Oracle Views that had to be transfered to Microsoft Views, then the first thing you figure out is, that there’s not the same built in functions on both sides. For example the well know TO_NUMBER() or TO_CHAR() in Oracle dosen’t exist in Microsoft. So what you do?
Below you see a list of the common functions compared between the two technologies:




Description
Oracle
MS SQL Server


Smallest integer >= n
CEIL
CEILING


Modulus
MOD
%


Truncate number
TRUNC
<none>


Max or min number or string in list
GREATEST,LEAST
<none>


Translate NULL to n
NVL
ISNULL


Return NULL if two values are equal
DECODE
NULLIF


String concatenation
CONCAT(str1,str2)
str1 + str2


Convert ASCII… Read more

Sometimes you have to migrate from an Oracle database to a Microsoft SQL Server. I’m not going into the reason that could be behind. I had different projects where I had to achieve this goal. The issues that I faced is what I would like to share with you.
Different functions
When it comes to rewrite code from Ora-SQL to MS-SQL, in my case it were Oracle Views that had to be transfered to Microsoft Views, then the first thing you figure out is, that there’s not the same built in functions on both sides. For example the well know TO_NUMBER() or TO_CHAR() in Oracle dosen’t exist in Microsoft. So what you do?
Below you see a list of the common functions compared between the two technologies:

Description
Oracle
MS SQL Server

Smallest integer >= n
CEIL
CEILING

Modulus
MOD
%

Truncate number
TRUNC
<none>

Max or min number or string in list
GREATEST,LEAST
<none>

Translate NULL to n
NVL
ISNULL

Return NULL if two values are equal
DECODE
NULLIF

String concatenation
CONCAT(str1,str2)
str1 + str2

Convert ASCII…

Read more