Please, note, that Ispirer SQLWays 6.0 is no longer supported and provided to clients.

You can try out automated conversion of databases and applications with Ispirer Toolkit for free. Download free trial.

Check out the relevant toolkit documentation.

Ispirer SQL Server to Oracle Migration overview.

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;