Be particularly careful about issues that are not easy to notice when converting Oracle queries to SQL Server. T-SQL, as we know, is the language engine of Plsql, and Oracle's language engine is. Both of these query languages extend the ANSI SQL-92 standard to provide additional support. Almost all of the applications you create use these supplemental features. This article describes the most common, non-standard Oracle extensions, along with how to transform these extensions for use in SQL Server environments.
Selection of columns
When executing a data query with Plsql, the FROM clause is required, as is the case with SQL Server. The SELECT statement must select the data table for. There is a special table dual within the Oracle database. The dual table is created by Oracle along with the data dictionary, and all users can access the table by name dual. This table has only one column dummy, which is defined as the VARCHAR2 (1) type, with a row value of X.
Selecting data from the dual table is often used to compute a constant expression through a SELECT statement, and because dual has only one row of data, the constant returns only once.
Oracle's dual query looks like this:
SELECT ' x ' from dual
The equivalent SQL Server query is the following:
SELECT ' x '
Connection
Oracle Use | | Symbol as a connector, and SQL Server's connector is a plus sign: +.
The Oracle query looks like this:
Select ' Name ' | | ' Last Name ' from TableName
The corresponding SQL Server query looks like this:
Select ' name ' + ' last Name '
Digital Trade-offs
There is a trunc function in the Oracle database that returns n bits of M-bit decimal digits, or 0 digits if I omit M. The value of M can be negative, indicating that the number of m digits is truncated to the left of the decimal point.
You can use round or floor under SQL Server.
The following is an Oracle query:
Select TRUNC (15.79,1) "Truncate" from DUAL;
The following is a version of SQL Server for similar queries:
SELECT ROUND (15.79, 0) rounded, ROUND (15.79, 0,1) truncated
SELECT FLOOR (ROUND (15.79, 0)), FLOOR (ROUND (15.79, 0,1))
Digital conversion
Oracle's To_char function converts n-bit number data types to VARCHAR2 data types, with an optional numeric format.
SQL Server returns character data after a numeric conversion through the STR function. However, the function does not have a convenient format parameter.
Oracle queries are as follows:
SELECT To_char (123.45, 99999999999999) from tab
SELECT to_char (expiry_date, ' ddmonyyyy ') from tab
The following is a SQL Server version of the query:
SELECT STR (123.45, 14)
SELECT STR (Round (123.455, 2), 12,2)
SELECT CAST ((CONVERT (varchar (), expirydate,), ', ') as varchar (9))
Length and Len
The following is an Oracle query:
Select Length (' Sqlmag ') "Length in characters" from DUAL;
The above query is written under SQL Server:
Select LEN (' Sqlmag ') "Length in Characters"
Date
Both of these systems have their own current date and time formats.
Oracle acquisition date and in the following manner:
Sysdate
SQL Server is like this:
GETDATE ()
You can use all kinds of grammar to manipulate dates. The following code formats the month in the Oracle Date value (the return date plus n months):
Select add_months (sysdate,12) from dual
SQL Server completes the same functionality as follows:
Select DateAdd (Mm,12,getdate ())
The subtraction of the data is also different. The following code subtracts data directly from Oracle:
SELECT sysdate-add_months (sysdate,12) from dual
This is done by SQL Server:
SELECT DateDiff (DD, GetDate (), DateAdd (Mm,12,getdate ())
Summary
Luckily, I didn't have too much of a problem migrating from Oracle to SQL Server 2000. Of course, I knew from the beginning that there was definitely a significant difference between the systems. I hope the differences listed in this article will help you avoid some common problems.