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 to char CHR CHAR
Capitalize first letters of words INITCAP <none>
Find string in string INSTR CHARINDEX
Find pattern in string INSTR PATINDEX
String length LENGTH DATALENGTH
Pad string with blanks LPAD,RPAD <none>
Trim leading or trailing chars other than blanks LTRIM(str,chars),RTRIM(str,chars) <none>
Replace chars in string REPLACE STUFF
Convert number to string TO_CHAR STR, CAST
Convert string to number TO_NUMBER CAST
Get substring from string SUBSTR SUBSTRING
Char for char translation in string TRANSLATE <none>
Date addition ADD_MONTH or + DATEADD
Date subtraction MONTHS_BETWEEN or – DATEDIFF
Last day of month LAST_DAY <none>
Time zone conversion NEW_TIME <none>
Next specified weekday after date NEXT_DAY <none>
Convert date to string TO_CHAR DATENAME, CONVERT
Convert string to date TO_DATE CAST
Convert date to number TO_NUMBER(TO_CHAR(d)) DATEPART
Date round ROUND CONVERT
Date truncate TRUNC CONVERT
Current date SYSDATE GETDATE
Convert hex to binary HEXTORAW CAST
Convert binary to hex RAWTOHEX CONVERT
If statement in an expression DECODE CASE … WHENor COALESCE
User’s login id number or name UID, USER SUSER_ID, SUSER_NAME
User’s database id number or name UID, USER USER_ID, USR_NAME
Current user USER USER

Source: dba-oracle.com

Examples

TO_CHAR ersetzt durch CONVERT

Oracle:

SQL Server:

TO_NUMBER ersetzt durch CONVERT(float,..), CAST()

As you can see in the list above, you can use CAST, also CONVERT is possible. But attention, it sounds like a very easy translation, but you have to be careful, for instance when it comes to decimals. Because the built-in TO_NUMBER() function rounds at the 16th decimal place and if you use CONVERT, the behavior is a bit different.

I used CONVERT(numeric,..) for my needs, but as you can see below, the rounding of decimals is not the same:

Combined with Modulo (mod / %) you have to keep this in mind:

See more oracle built-in function conversion to SQL-Server in the attachment below.

Continue reading about performance in my part II Migrate from Oracle to Microsoft (Views) – Part II.

Attachment

 

 

389 total views, 2 views today


Leave a Reply