Migration from oracle to SQL Server

When converting Oracle queries to SQLServer, you must be especially careful about some problems that are not easy to notice. We know that T-SQL is the language engine of SQLServer, while the language engine of Oracle is PLSQL. Both query languages have extended the ANSISQL-92 standard to provide additional support. Almost all the applications you create use these supplements.

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 the applications you create use these supplements.

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. In OracleDatabaseThere is a special table DUAL. 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.
China Network Management Alliance bitsCN.com

　　
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
OracleDatabaseThere is 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 Network Management Forum bbs_bitsCN_com
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 \" Network Administrator downloads dl.bitscn.com
　　
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 ()))