FETCH_BULK_COLLECTIONS_UDF (OBJECT, FLOAT)

Definition

This user-defined function (UDF) is used to replicate the functionality of fetching bulk for collections in Oracle. This function version receives the cursor and the limit value for the row count.

FETCH_BULK_COLLECTIONS_UDF(CURSOR OBJECT, LIMIT FLOAT)

Parameters

CURSOR OBJECT

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

LIMIT FLOAT

The limit for the records to call.

Returns

Returns an object with information related to the logic of fetching bulk collections.

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_COLLECTIONS_UDF(:MY_CURSOR, 1.0)
        );
        
        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_A"
    ]
  ],
  "ROWCOUNT": 1
}