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 19. 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 20. 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 21. 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;