MSCCP0005

This statement has usages of cursor fetch bulk operations

This is a deprecated version of the SnowConvert documentation, please visit the official site HERE.

Severity

Low

Description

This warning is used to indicate that the statement has usages of cursor fetch bulk operations. A cursor fetch bulk operation is a way to retrieve multiple rows of data from a cursor in a single fetch operation, rather than fetching rows one at a time. This reduces the overhead of multiple round trips between client and server.

This scenario could be considered a complex pattern if it is used improperly. E.g. fetching too many rows at once can consume a large amount of memory, so it's important to balance the number of rows fetched with available memory.

Code Example

Oracle

Input

CREATE OR REPLACE PROCEDURE oracle_cursor_fetch_bulk AS
--cursor and variable declarations
BEGIN
    OPEN c1;
    FETCH c1 BULK COLLECT INTO col1;
    CLOSE c1;
END;

Output

CREATE OR REPLACE PROCEDURE oracle_cursor_fetch_bulk ()
RETURNS VARCHAR
LANGUAGE SQL
EXECUTE AS CALLER
AS
--cursor and variable declarations
$$
    BEGIN
        OPEN c1;
        /*** MSC-WARNING - MSCCP0005 - THIS STATEMENT HAS USAGES OF CURSOR FETCH BULK OPERATIONS ***/
        c1 := (
            CALL FETCH_BULK_COLLECTION_RECORDS(:c1)
        );
        col1 := :c1:RESULT;
        CLOSE c1;
    END;
$$;

Recommendations

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