MSCEWI3130

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.

• If you need more support, you can email us at [email protected]