Oracle to SQL Server: Migration traps

This article provides an introduction to the most commonly used, nonstandard extensions in Oracle and how you can convert them for SQL Server compliance.
Written by Brijesh Ammanath, Contributor
You must watch out for many subtle and not-so-subtle traps when translating Oracle queries to SQL Server. Just as T-SQL is the language engine of SQL Server, PLSQL is that of Oracle. Both these languages support several extensions to the ANSI SQL-92 standard to provide additional features. You’ll rarely build an application without taking advantage of these features. This article provides an introduction to the most commonly used, nonstandard extensions in Oracle and how you can convert them for SQL Server compliance.
Selecting columns
When you’re executing a query in PLSQL, the FROM clause is not optional, as it is in SQL Server. A SELECT has to be made from a table. A special table called DUAL exists in Oracle DB for this purpose. The DUAL table is automatically created by Oracle along with the data dictionary; all users can access it by the name DUAL. It has one column, DUMMY, defined to be VARCHAR2(1), and one row with a value X.
Selecting from the DUAL table is useful for computing a constant expression with the SELECT command. Because DUAL has only one row, the constant is returned only once.
The Oracle query is as follows:
SELECT  ‘x’ FROM dual
And here’s the SQL Server query:
Oracle uses double pipes (||) for concatenation, whereas SQL Server uses the plus (+) sign.
The Oracle query is as follows:
Select ‘Name’ || ‘Last Name’ From tableName
The corresponding SQL Server query looks like this:
Select ‘Name’ + ‘Last Name’
Truncating a number
Oracle has a TRUNC function that returns n truncated to m decimal places; if m is omitted, n is truncated to 0 places. The value of m can be negative to truncate (make zero) m digits left of the decimal point.
In SQL Server, you use either Round or Floor.
Here’s the Oracle query:
SELECT  TRUNC(15.79,1) "Truncate" FROM DUAL;
And here’s the SQL Server version:
SELECT ROUND(15.79, 0) rounded , ROUND(15.79, 0,1) truncated
SELECT FLOOR(ROUND(15.79, 0)), FLOOR(ROUND(15.79, 0,1) )
Number conversion
Oracle has a TO_CHAR function that converts n of NUMBER datatype to a value of VARCHAR2 datatype, using an optional number format.
SQL Server has the STR function, which returns character data converted from numeric data. Unfortunately, it has no convenient Format argument.
The Oracle query is as follows:
SELECT to_char(123.45 ,99999999999999) from tab
This is the SQL Server version:
SELECT STR(123.45, 14)
SELECT STR(round(123.455 , 2),12,2)
SELECT CAST(REPLACE((CONVERT(varchar(12) , EXPIRYDATE, 106 )),' ' , '') as varchar(9))
LENGTH becomes LEN
The following Oracle query:
SELECT LENGTH('SQLMAG') "Length in characters" FROM DUAL;
is writtent this way in SQL Server:
SELECT LEN('SQLMAG') "Length in characters"
Each system has its own format for retrieving the current date and time.
Oracle does so as follows:
SQL Server uses this approach:
You can manipulate dates using different syntax. The following adjusts the month portion of a date value in Oracle (returns the date plus n months):
Select   add_months(sysdate,12) from dual
SQL Server does the same thing as follows:
Select dateadd(mm,12,getdate())
Data subtraction is also different. The following performs direct date subtraction in Oracle:
SELECT sysdate -add_months(sysdate,12) FROM dual
SQL Server works like this:
SELECT  datediff(dd, GetDate(),dateadd(mm,12,getdate()))
Fortunately, my transition from Oracle to SQL Server 2000 has been smooth. Of course, I began my journey knowing the systems had various differences. I hope the differences listed in this article will help you avoid common mistakes; they should be handy in your next conversion project. I have included a sample function in Listing A for conversion from the Orace decode statement to the SQL Server case statement.
Editorial standards