When converting an Oracle query to an SQL Server, you must be careful about some issues that are not easy to notice. We know that T-SQL is the language engine of SQL Server, while Oracle's language engine is PLSQL. Both query languages have extended the ANSI SQL-92 standard to provide additional support. Almost all applications you create use these complementary features. This article describes the most common and non-standard Oracle extensions. It also describes how to convert these extensions for use in the SQL Server environment.
Column Selection
When PLSQL is used to perform data queries, the FROM clause is required, which is the same as that of SQL Server. The SELECT statement must SELECT the target data table. There is a special table DUAL in the Oracle database. The DUAL table is created by Oracle along with the data dictionary. All users can access the table with the name DUAL. There is only one DUMMY column in this table, which is defined as VARCHAR2 (1) type and has a row value of X.
Selecting data from the DUAL table is often used to calculate the constant expression through the SELECT statement. Because DUAL only has one row of data, the constant is returned only once.
The DUAL query in Oracle is as follows:
SELECT 'x' FROM dual
The equivalent SQL Server Query looks like this:
SELECT 'x'
Connection
Oracle uses the | symbol as the connector, while SQL Server uses the plus sign: +.
The Oracle query is as follows:
Select 'name' | 'last name' From tableName
The corresponding SQL Server query is as follows:
Select 'name' + 'last name'
Number trade-offs
Oracle Database has a TRUNC function, which returns n digits of the m-bit decimal number. If m is omitted, n is 0 digits. The m value can be negative, indicating that the m digit on the left of the decimal point is truncated.
You can use Round or Floor in SQL Server.
Oracle query:
Select trunc (15.79, 1) "Truncate" from dual;
The following are SQL Server versions of similar queries:
Select round (15.79, 0) rounded, ROUND (15.79,) truncated
Select floor (ROUND (15.79, 0), FLOOR (ROUND (15.79 ))
Digital Conversion
Oracle's TO_CHAR function can convert the n-bit NUMBER data type to the VARCHAR2 data type and use an optional numeric format.
SQL Server Returns the character data after numeric conversion through the STR function. However, this function does not have a convenient Format parameter.
Oracle query:
SELECT to_char (123.45, 99999999999999) from tab
SELECT to_char (EXPIRY_DATE, DDMONYYYY) from tab
The following are SQL Server version queries:
Select str (123.45, 14)
Select str (round (123.455, 2)
Select cast (REPLACE (CONVERT (varchar (12), EXPIRYDATE, 106),) as varchar (9 ))
LENGTH and LEN
Oracle queries are as follows:
Select length (SQLMAG) "Length in characters" from dual;
The preceding query is written in the following way in SQL Server:
Select len (SQLMAG) "Length in characters"
Date
Both systems have their own current date and time formats.
The date obtained by Oracle is as follows:
SYSDATE
SQL Server is like this:
GETDATE ()
You can use various syntaxes to operate dates. The following code adjusts the format of the month in the Oracle date value (return date plus n months ):
Select add_months (sysdate, 12) from dual
SQL Server performs the same functions as follows:
Select dateadd (mm, 12, getdate ())
Data Subtraction is also different. The following code directly deletes data in Oracle:
SELECT sysdate-add_months (sysdate, 12) FROM dual
SQL Server does this:
SELECT datediff (dd, GetDate (), dateadd (mm, 12, getdate ()))
Summary
Fortunately, I have not encountered any difficult problems during the migration from Oracle to SQL Server 2000. Of course, at the beginning, I knew that there must be significant differences between systems. I hope the differences listed in this article will help you avoid some common problems.