Conversion of Assignment Statement from Microsoft SQL Server to Oracle

Ispirer SQLWays Database Migration Software
Conversion of Assignment Statement from Microsoft SQL Server to Oracle

SQL Server SELECT | SET @local_variable assignment statements allow specifying a subquery to assign a value to a variable, while the Oracle assignment operator (:=) does not allow specifying a subquery.

a) Expression is any expression, except a scalar subquery.

In this case SQLWays converts the SELECT and SET clause to the Oracle assignment operator (:=).

Examples:
TABLE 30. Expression is any expression, except a scalar subquery
Microsoft SQL Server

Oracle
SELECT @A=5+7 
v_A:=5+7; 
SET @B='String' 
v_B:='String'; 
b) Expression is a scalar subquery.

If SQL Server expression is a scalar subquery, SQLWays converts the SELECT and SET clause to the Oracle SELECT INTO statement that allows assigning a SQL query result to a variable.

Examples:
TABLE 31. Expression is a scalar subquery
Microsoft SQL Server

Oracle
SELECT @D = (SELECT col1 FROM tab1) 
SELECT col1 INTO v_D FROM tab1; 
SELECT @C = (SELECT col2 FROM tab2) 
SELECT col2 INTO v_C FROM tab2; 
c) SELECT | SET containing several assignment clauses.

The SQL Server SELECT @local_variable assignment statement can contain several assignments while Oracle allows only one assignment. If SQL Server SELECT contains multiple assignments, SQLWays converts them to multiple assignment operators in Oracle.

Examples:
TABLE 32. SELECT | SET which contains several assign clauses
Microsoft SQL Server

Oracle
SELECT @E = (SELECT col3 FROM tab3), 
@G = 9.8 
SELECT col3 INTO v_E FROM tab3;
v_G:=9.8; 
SELECT @F= (SELECT col4 FROM tab4), 
@H= (SELECT col5 FROM tab5)	 
SELECT col4 INTO v_F FROM tab4;
SELECT col5 INTO v_H FROM tab5;  
SELECT @K = `Test string',
@L = 10 
v_K:= `Test string';
v_L:=10; 

Microsoft SQL Server	Oracle
SELECT @D = (SELECT col1 FROM tab1)	SELECT col1 INTO v_D FROM tab1;
SELECT @C = (SELECT col2 FROM tab2)	SELECT col2 INTO v_C FROM tab2;