For Loop and Fetch into a variable cannot be used on the same cursor
This is a deprecated version of the SnowConvert documentation, please visit the official site HERE.
Severity
Medium
Description
This warning appears when the same cursor declaration is used in a fetch statement into a variable, a loop, or a fetch into records. In this case, the use of fetch into variables is commented out.
Example Code
Input Code
CREATE OR REPLACE PROCEDURE PROC01
AS
var1 NUMBER;
record_product products%ROWTYPE;
CURSOR c_product IS SELECT price FROM products;
BEGIN
OPEN c_product;
FETCH c_product INTO var1;
CLOSE c_product;
OPEN c_product;
LOOP
FETCH c_product INTO record_product;
EXIT WHEN c_product%NOTFOUND;
INSERT INTO sold_items VALUES record_product;
END LOOP;
CLOSE c_product;
END;
Output Code
CREATE OR REPLACE PROCEDURE PUBLIC.PROC01 ()
RETURNS VARCHAR
LANGUAGE SQL
EXECUTE AS CALLER
AS
$$
DECLARE
var1 NUMBER(38, 18);
record_product OBJECT /*** MSC-WARNING - MSCEWI1036 - ROWTYPE DATA TYPE CONVERTED TO OBJECT ***/ := OBJECT_CONSTRUCT();
c_product CURSOR
FOR
SELECT
OBJECT_CONSTRUCT('PRICE', price) sc_cursor_record FROM
PUBLIC.products;
BEGIN
OPEN c_product;
/*** MSC-ERROR - MSCEWI3130 - FOR LOOP AND FETCH INTO VARIABLE CAN NOT BE USED ON THE SAME CURSOR ***/
FETCH c_product INTO
:var1;
CLOSE c_product;
OPEN c_product;
LOOP
FETCH c_product INTO record_product;
IF (record_product IS NULL) THEN
EXIT ;
END IF;
INSERT INTO PUBLIC.sold_items
SELECT
:record_product:NAME,
: record_product:PRICE;
END LOOP;
CLOSE c_product;
END;
$$;
Recommendations
To support both scenarios, duplicating the cursor declaration is recommended so that each statement will use a separate cursor.
