The Migration Workbench currently provides limited support for global variables. @@FETCH_STATUS, @@SQLSTATUS, @@ROWCOUNT, @@ERROR, and @@VERSION are supported. Other global variables are considered as normal variables so that parsing can continue and the above warning is added. Click here for information about specific global variables.
The following T-SQL example shows how this error may typically be produced:
CREATE PROC proc1
AS
declare @mycol1 varchar(30)
insert into table1
values (10,20,30)
select @mycol1=@@identity
The following PL/SQL code is produced by the T-SQL to PL/SQL parser in the Migration Workbench. In this case the error is written to the log only; it should also be written to the text output.
CREATE OR REPLACE
PROCEDURE proc1
AS
StoO_selcnt INTEGER;
StoO_error INTEGER;
StoO_rowcnt INTEGER;
StoO_errmsg VARCHAR2(255);
StoO_sqlstatus INTEGER;
mycol1 VARCHAR2(30);
BEGIN
BEGIN
StoO_error
:= 0;
StoO_rowcnt
:= 0;
INSERT INTO
table1 VALUES (10, 20, 30);
StoO_rowcnt := SQL%ROWCOUNT;
EXCEPTION
WHEN OTHERS THEN
StoO_error := SQLCODE;
StoO_errmsg := SQLERRM;
END;
proc1.mycol1
:= proc1.IDENTITY;
END proc1;
This example suggests how the PL/SQL code may be rewritten by hand. Identity columns are converted to Oracle triggers and sequences.
CREATE OR REPLACE
PROCEDURE proc1
AS
StoO_selcnt INTEGER;
StoO_error INTEGER;
StoO_rowcnt INTEGER;
StoO_errmsg VARCHAR2(255);
StoO_sqlstatus INTEGER;
mycol1 VARCHAR2(30);
BEGIN
BEGIN
StoO_error
:= 0;
StoO_rowcnt
:= 0;
INSERT INTO
table1 VALUES (10, 20, 30);
StoO_rowcnt := SQL%ROWCOUNT;
EXCEPTION
WHEN OTHERS THEN
StoO_error := SQLCODE;
StoO_errmsg := SQLERRM;
END;
proc1.mycol1
:= table1SEQ.CURRVAL;
END proc1;
The following section outlines how specific global variables are handled by the Migration Workbench:
@@SERVERNAME
Teated as a normal
variable. The Oracle equivalent may be based on the name of the
database to which you are connected.
@@IDENTITY
Treated as a normal
variable. The current IDENTITY value may need to be retrieved from
the column in the appropriate table. The IDENTITY column is replaced
by a sequence and a trigger in Oracle.
@@SPID
Treated as a normal
variable. The Oracle equivalent may be based on the v$sessions table.
Note that a user might not have a dedicated server process in Oracle.
@@TRANSTATE
Treated as a normal variable. There is no Oracle equivalent. Transactions in Oracle will either complete or fail. Errors in Oracle are handled via exceptions and SQLCODE. For more information, see the PL/SQL User's Guide and Reference, Release 2 (8.1.6) (Part Number A77069-01). for more information in this regard.