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.

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

Query

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

Query

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

Query

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();

Result

DBMS OUTPUT
-----------
10
20
30
40
20
30

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.

Query

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;

    /*** MSC-WARNING - MSCEWI1020 - CUSTOM UDF 'INSERT_REPLACE_COLLECTION_ELEMENT_UDF' INSERTED. ***/    
    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;

UDF

CREATE OR REPLACE FUNCTION PUBLIC.INSERT_REPLACE_COLLECTION_ELEMENT_UDF(varray ARRAY, position INTEGER, newValue VARIANT)
RETURNS ARRAY
LANGUAGE SQL
AS
$$
    ARRAY_CAT(
        ARRAY_APPEND(ARRAY_SLICE(varray, 0, (position)-1), newValue),
        ARRAY_SLICE(varray, position, ARRAY_SIZE(varray)))
$$;

Result

DBMS OUTPUT
-----------
10
20
30
40
20
30

Known Issues

1. They are currently not being converted

SnowConvert does not support translating these elements.

2. Indexing needs to be modified

Oracle's indexes start at 1, on Snowflake they will begin at 0.

3. Array Density may not match original

Since the ARRAY datatype can become sparse, care should be taken when performing additions or deletions of the array. Using ARRAY_COMPACT() after such operations can be helpful if the density is a concern.

Related EWIs

1. MSCEWI1073: Pending Functional Equivalence Review.

2. MSCEWI1056: Custom Types Not Supported.

3. MSCEWI1058: Functionality is not currently supported by Snowflake Scripting.

4. ​MSCEWI1062​: Custom type usage changed to variant.