FETCH_BULK_COLLECTION_RECORDS_UDF (OBJECT, FLOAT, ARRAY)

Important Notice: Migration of Documentation Website

Please be advised that our documentation website is currently undergoing a migration to a new platform. To ensure you have access to the most up-to-date information, we kindly request that you visit our new documentation website located at:

Official Snowflake Snowconvert Documentation

For any immediate assistance or if you encounter any issues, please contact our support team at [email protected].

Thank you for your understanding.

Definition

This user-defined function (UDF) is used to replicate the functionality of FETCH in Oracle. This is the variation where it receives the cursor, the limit, and the column names.

FETCH_BULK_COLLECTION_RECORDS_UDF(CURSOR OBJECT, LIMIT FLOAT, COLUMN_NAMES ARRAY)

Parameters

CURSOR OBJECT

The cursor that is processed and filled with the data in the fetch bulk.

LIMIT FLOAT

The limit for the records to call.

COLUMN_NAMES ARRAY

The name associated with the column is not the initial name.

Returns

Returns an object with the records from the fetch bulk.

Usage example

Input:

CREATE OR REPLACE TABLE MY_TABLE (test VARCHAR(100));
INSERT INTO MY_TABLE VALUES ('TEST_A');

CREATE OR REPLACE PROCEDURE MY_PROCEDURE ()
RETURNS OBJECT
LANGUAGE SQL
EXECUTE AS CALLER
AS
$$
    DECLARE
        MY_CURSOR OBJECT := INIT_CURSOR_UDF('MY_CURSOR', '   SELECT * FROM
      MY_TABLE');

    BEGIN
        MY_CURSOR := (
            CALL OPEN_BULK_CURSOR_UDF(:MY_CURSOR)
        );
        MY_CURSOR := (
            CALL FETCH_BULK_COLLECTION_RECORDS_UDF(:MY_CURSOR, 1.0, NULL)
        );
        
        Return MY_CURSOR;
    END;
$$;

CALL MY_PROCEDURE();

Output:

{
  "FOUND": true,
  "ISOPEN": true,
  "NAME": "MY_CURSOR",
  "NOTFOUND": false,
  "QUERY": "   SELECT * FROM\n      MY_TABLE",
  "RESULT": [
    {
      "TEST": "TEST_A"
    }
  ],
  "ROWCOUNT": 1
}