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;