[SPCONV-ERR(<NN>)]:[Parsing(<NAME>)]('SCROLL CURSOR') is not supported in Oracle

SCROLL cursor is not supported by Oracle. Cursor DECLARE, OPEN, and FETCH statements are likely to require alteration.

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

/*say you want to take a sample ie total ever 3rd order*/
create proc totalorders
@customeridin numeric,
@toprint varchar(40) OUTPUT
as
declare @globaltotal numeric(18,2)
declare @totalout numeric(18,2)
declare @orderid NUMERIC(18)
declare @extraparam varchar(40)
DECLARE C1 SCROLL CURSOR FOR select O_ORDERKEY from orders
 where O_CUSTKEY=@customeridin
DECLARE @endc1 INTEGER
select @endc1=0
select @totalout=0
select @globaltotal=0
open c1
while @endc1=0
 begin
 fetch RELATIVE 3 from c1 into @orderid
 if (@@fetch_status < -1)
  BEGIN
  select @totalout=sum(lineitem.l_unitprice*lineitem.l_quantity) from
  lineitem where lineitem.l_orderkey=@orderid
   select @globaltotal=@globaltotal+@totalout
  END
 else
  begin
  select @endc1=1
  end
end
close c1
deallocate c1
select @toprint=str(@globaltotal,18,2)
GO
 

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

CREATE OR REPLACE PROCEDURE totalorders(
customeridin  NUMERIC ,
toprint  IN OUT VARCHAR2)
AS
StoO_selcnt INTEGER;
StoO_error  INTEGER;
StoO_rowcnt INTEGER;
StoO_errmsg VARCHAR2(255);
StoO_sqlstatus INTEGER;
globaltotal  NUMBER(18, 2);
totalout  NUMBER(18, 2);
orderid  NUMBER(18);
extraparam  VARCHAR2(40);
/* SCROLL CURSOR is not supported in Oracle. */
 CURSOR C1 IS SELECT  O_ORDERKEY
  FROM orders
 WHERE O_CUSTKEY = totalorders.customeridin;

endc1  INTEGER;
/*say you want to take a sample ie total ever 3rd order*/

BEGIN
 totalorders.endc1 :=  0;
 totalorders.totalout :=  0;
 totalorders.globaltotal :=  0;
 open c1;

 <<i_loop1
 WHILE  totalorders.endc1 = 0 LOOP
 BEGIN
  /*[SPCONV-ERR(19)] FETCH RELATIVE is not supported in Oracle. */
  fetch c1 into totalorders.orderid;

  IF c1%NOTFOUND THEN
   StoO_sqlstatus := 2;
  ELSE
   StoO_sqlstatus := 0;
  END IF;
  IF  ( c1%FOUND) THEN
  BEGIN
   BEGIN
    StoO_rowcnt := 0;
    StoO_selcnt := 0;
    StoO_error  := 0;

    SELECT   SUM(lineitem.l_unitprice * lineitem.l_quantity)
    INTO totalorders.totalout FROM lineitem
    WHERE lineitem.l_orderkey = totalorders.orderid;
    StoO_rowcnt := SQL%ROWCOUNT;

    EXCEPTION
     WHEN TOO_MANY_ROWS THEN
      StoO_rowcnt := 2;
     WHEN OTHERS THEN
      StoO_rowcnt := 0;
      StoO_selcnt := 0;
      StoO_error := SQLCODE;
      StoO_errmsg := SQLERRM;
   END;
   totalorders.globaltotal :=  totalorders.globaltotal + totalorders.totalout;
  END;
  ELSE
  BEGIN
   totalorders.endc1 :=  1;
  END;
  END IF;
 END;
 END LOOP;
 close c1;
 

 totalorders.toprint :=  TO_CHAR(totalorders.globaltotal);
END totalorders;

This problem can be avoided by doing three fetches instead of one as follows:

CREATE OR REPLACE PROCEDURE totalorders(
customeridin  NUMERIC ,
toprint  IN OUT VARCHAR2)
AS
StoO_selcnt INTEGER;
StoO_error  INTEGER;
StoO_rowcnt INTEGER;
StoO_errmsg VARCHAR2(255);
StoO_sqlstatus INTEGER;
globaltotal  NUMBER(18, 2);
totalout  NUMBER(18, 2);
orderid  NUMBER(18);
extraparam  VARCHAR2(40);
CURSOR C1 IS SELECT  O_ORDERKEY
  FROM orders
 WHERE O_CUSTKEY = totalorders.customeridin;

endc1  INTEGER;
/*say you want to take a sample ie total ever 3rd order*/

BEGIN
 totalorders.endc1 :=  0;
 totalorders.totalout :=  0;
 totalorders.globaltotal :=  0;
 open c1;

 <<i_loop1
 WHILE  totalorders.endc1 = 0 LOOP
 BEGIN
      /*[SPCONV-ERR(19)] FETCH RELATIVE is not supported in Oracle. */
    fetch c1 into totalorders.orderid;
    IF c1%NOTFOUND THEN
      StoO_sqlstatus := 2;
    ELSE
      fetch c1 into totalorders.orderid;
      IF c1%NOTFOUND THEN
        StoO_sqlstatus := 2;
      ELSE
        fetch c1 into totalorders.orderid;
        IF c1%NOTFOUND THEN
          StoO_sqlstatus := 2;
        ELSE
          StoO_sqlstatus := 0;
        END IF;
      END IF;
    END IF;
  IF  ( c1%FOUND) THEN
  BEGIN
   BEGIN
    StoO_rowcnt := 0;
    StoO_selcnt := 0;
    StoO_error  := 0;

    SELECT   SUM(lineitem.l_unitprice * lineitem.l_quantity)
    INTO totalorders.totalout FROM lineitem
    WHERE lineitem.l_orderkey = totalorders.orderid;
    StoO_rowcnt := SQL%ROWCOUNT;

    EXCEPTION
     WHEN TOO_MANY_ROWS THEN
      StoO_rowcnt := 2;
     WHEN OTHERS THEN
      StoO_rowcnt := 0;
      StoO_selcnt := 0;
      StoO_error := SQLCODE;
      StoO_errmsg := SQLERRM;
   END;
   totalorders.globaltotal :=  totalorders.globaltotal + totalorders.totalout;
  END;
  ELSE
  BEGIN
   totalorders.endc1 :=  1;
  END;
  END IF;
 END;
 END LOOP;
 close c1;
 

 totalorders.toprint :=  TO_CHAR(totalorders.globaltotal);
END totalorders;