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;