Varray Type Definition

This is a translation reference to convert the Oracle Varray Declaration to Snowflake

This section is a work in progress, information may change in the future.

This section is for the PL/SQL Version of the Varrays, for the Standalone Version please see Array Type Definition.

Some parts in the output code are omitted for clarity reasons.

Description

A varray (variable-size array) is an array whose number of elements can vary from zero (empty) to the declared maximum size.

To access an element of a varray variable, use the syntax variable_name(index). The lower bound of index is 1; the upper bound is the current number of elements. The upper bound changes as you add or delete elements, but it cannot exceed the maximum size. When you store and retrieve a varray from the database, its indexes and element order remain stable.

(Oracle PL/SQL Language Reference VARRAYS)

For the translation, the type definition is replaced by an ARRAY Semi-structured Data Type and then its usages are changed accordingly across any operations. Please note how the translation for Nested Tables and Varrays are the same.

In order to define a varray type, the syntax is as follows:

type_definition := { VARRAY | [VARYING] ARRAY } (size_limit) OF datatype
            [NOT NULL];

To declare a variable of this type:

variable_name collection_type;

Sample Source Patterns

Varray definitions

This illustrates how three different ways to create a varray, and how to migrate these definitions for the variables.

Oracle

IN -> Oracle_01.sql
CREATE OR REPLACE PROCEDURE associative_array_procedure
IS
    TYPE varray_typ IS ARRAY(10) OF INTEGER;
    TYPE varray_typ2 IS VARRAY(10) OF INTEGER;
    TYPE varray_typ3 IS VARYING ARRAY(10) OF INTEGER;

    array_variable varray_typ;
    array_variable2 varray_typ2;
    array_variable3 varray_typ3;
BEGIN
    NULL;
END;

Snowflake

OUT -> Oracle_01.sql
CREATE OR REPLACE PROCEDURE associative_array_procedure()
RETURNS VARCHAR
LANGUAGE SQL
EXECUTE AS CALLER
AS
$$
DECLARE
    -- NO LONGER NEEDED
    /*
    TYPE varray_typ IS ARRAY(10) OF INTEGER;
    TYPE varray_typ2 IS VARRAY(10) OF INTEGER;
    TYPE varray_typ3 IS VARYING ARRAY(10) OF INTEGER;
    */
    
    array_variable ARRAY;
    array_variable2 ARRAY;
    array_variable3 ARRAY;
BEGIN
    NULL;
END;
$$;

Varray iteration

Oracle

IN -> Oracle_02.sql
CREATE OR REPLACE PROCEDURE varray_iteration
IS
    TYPE varray_typ IS VARRAY(3) OF INTEGER;
    varray_variable varray_typ := varray_typ(10, 20, 30);
BEGIN
    FOR i IN 1..varray_variable.COUNT
    LOOP
        DBMS_OUTPUT.PUT_LINE(varray_variable(i));
    END LOOP;
    
    varray_variable(1) := 40;
    
    FOR i IN 1..varray_variable.COUNT
    LOOP
        DBMS_OUTPUT.PUT_LINE(varray_variable(i));
    END LOOP;
END;
/

CALL varray_iteration();

Snowflake

Note that a UDF was added to implement the functionality for the update of the element.

This UDF will be added in later revisions.

OUT -> Oracle_02.sql
CREATE OR REPLACE PROCEDURE PUBLIC.varray_iteration()
RETURNS VARCHAR
LANGUAGE SQL
EXECUTE AS CALLER
AS
$$
DECLARE
    varray_variable ARRAY := ARRAY_CONSTRUCT(10, 20, 30);
BEGIN
    FOR i IN 1 TO ARRAY_SIZE(varray_variable)
    LOOP
        CALL DBMS_OUTPUT.PUT_LINE(:varray_variable[:i-1]);
    END LOOP;

    varray_variable := INSERT_REPLACE_COLLECTION_ELEMENT_UDF(varray_variable, 1, 40);
    
    FOR i IN 1 TO ARRAY_SIZE(varray_variable)
    LOOP
        CALL DBMS_OUTPUT.PUT_LINE(:varray_variable[:i-1]);
    END LOOP;
END;
$$;

CALL PUBLIC.varray_iteration();
SELECT * FROM DBMS_OUTPUT.DBMS_OUTPUT_LOG;