[SPCONV-ERR(<NN>)]:INSENSITIVE CURSOR is not supported in Oracle

INSENSITIVE cursor is not available in Oracle. The INSENSITIVE keyword is ignored.

The following example illustrates how the T-SQL to PL/SQL parser in the Migration Workbench could produce this error:

CREATE PROC proc1
AS
declare @mycol1 varchar(30)
declare mycursor insensitive cursor for
select col1
FROM table1, table2
WHERE table1.id = table2.id
fetch next from mycuror into @mycol1
close mycursor

The following code is the PL/SQL code produced by the parser:

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);
/*[SPCONV-ERR(4)]: INSENSITIVE CURSOR is not supported in Oracle. */
 CURSOR mycursor IS SELECT  col1
  FROM table1, table2
 WHERE table1.id = table2.id;

BEGIN
 fetch mycuror into proc1.mycol1;

 IF mycuror%NOTFOUND THEN
  StoO_sqlstatus := 2;
 ELSE
  StoO_sqlstatus := 0;
 END IF;
 close mycursor;

END proc1;

This problem could be avoided by placing the content of the cursor in a table or a PL/SQL table. Alternatively, a read-only transaction could be used so that the database view is consistent.

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);
set transaction read only;
/*[SPCONV-ERR(4)]: INSENSITIVE CURSOR is not supported in Oracle. */
 CURSOR mycursor IS SELECT  col1
  FROM table1, table2
 WHERE table1.id = table2.id;

BEGIN
 fetch mycuror into proc1.mycol1;

 IF mycuror%NOTFOUND THEN
  StoO_sqlstatus := 2;
 ELSE
  StoO_sqlstatus := 0;
 END IF;
 close mycursor;
commiy;
/*use mycol1 value in next transaction*/
END proc1;