Business
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.
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:
SELECT ‘x’
Concatenation
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
SELECT to_char(EXPIRY_DATE,'DDMONYYYY') 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"
Dates
Each system has its own format for retrieving the current date and time.
Oracle does so as follows:
SYSDATE
SQL Server uses this approach:
GETDATE()
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()))
Summary
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.
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:
SELECT ‘x’
Concatenation
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
SELECT to_char(EXPIRY_DATE,'DDMONYYYY') 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"
Dates
Each system has its own format for retrieving the current date and time.
Oracle does so as follows:
SYSDATE
SQL Server uses this approach:
GETDATE()
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()))
Summary
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.