In this case, <STRING can be LAST, ABSOLUTE, or RELATIVE. As these are not supported, manual conversion is required. FETCH NEXT is supported. FETCH FIRST is supported by closing and opening the cursor. The converted code does a FETCH NEXT.
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 i.e. 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 code is the PL/SQL code produced by the parser:
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;