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

Query

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

Query

OUT -> Oracle_01.sql

CREATE OR REPLACE PROCEDURE associative_array_procedure ()
RETURNS VARCHAR
LANGUAGE SQL
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},"attributes":{"component":"oracle"}}'
EXECUTE AS CALLER
AS
$$
    DECLARE
--        !!!RESOLVE EWI!!! /*** SSC-EWI-0058 - FUNCTIONALITY FOR 'PL COLLECTION TYPE DEFINITION' IS NOT CURRENTLY SUPPORTED BY SNOWFLAKE SCRIPTING ***/!!!
--        TYPE varray_typ IS ARRAY(10) OF INTEGER;
--        !!!RESOLVE EWI!!! /*** SSC-EWI-0058 - FUNCTIONALITY FOR 'PL COLLECTION TYPE DEFINITION' IS NOT CURRENTLY SUPPORTED BY SNOWFLAKE SCRIPTING ***/!!!
--        TYPE varray_typ2 IS VARRAY(10) OF INTEGER;
--        !!!RESOLVE EWI!!! /*** SSC-EWI-0058 - FUNCTIONALITY FOR 'PL COLLECTION TYPE DEFINITION' IS NOT CURRENTLY SUPPORTED BY SNOWFLAKE SCRIPTING ***/!!!
--        TYPE varray_typ3 IS VARYING ARRAY(10) OF INTEGER;

        array_variable VARIANT !!!RESOLVE EWI!!! /*** SSC-EWI-0062 - CUSTOM TYPE 'varray_typ' USAGE CHANGED TO VARIANT ***/!!!;
        array_variable2 VARIANT !!!RESOLVE EWI!!! /*** SSC-EWI-0062 - CUSTOM TYPE 'varray_typ2' USAGE CHANGED TO VARIANT ***/!!!;
        array_variable3 VARIANT !!!RESOLVE EWI!!! /*** SSC-EWI-0062 - CUSTOM TYPE 'varray_typ3' USAGE CHANGED TO VARIANT ***/!!!;
    BEGIN
        NULL;
    END;
$$;

Varray iteration

Oracle

Query

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

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

OUT -> Oracle_02.sql

CREATE OR REPLACE PROCEDURE varray_iteration ()
RETURNS VARCHAR
LANGUAGE SQL
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},"attributes":{"component":"oracle"}}'
EXECUTE AS CALLER
AS
$$
    DECLARE
--        !!!RESOLVE EWI!!! /*** SSC-EWI-0058 - FUNCTIONALITY FOR 'PL COLLECTION TYPE DEFINITION' IS NOT CURRENTLY SUPPORTED BY SNOWFLAKE SCRIPTING ***/!!!
--        TYPE varray_typ IS VARRAY(3) OF INTEGER;
        varray_variable VARIANT !!!RESOLVE EWI!!! /*** SSC-EWI-0062 - CUSTOM TYPE 'varray_typ' USAGE CHANGED TO VARIANT ***/!!! := varray_typ(10, 20, 30);
    BEGIN
        FOR i IN 1 TO 0 /*varray_variable.COUNT*/!!!RESOLVE EWI!!! /*** SSC-EWI-0073 - PENDING FUNCTIONAL EQUIVALENCE REVIEW FOR 'VARRAY CUSTOM TYPE EXPRESSION' NODE ***/!!! LOOP
            --** SSC-FDM-OR0035 - CHECK UDF IMPLEMENTATION FOR DBMS_OUTPUT.PUT_LINE_UDF. **
            CALL DBMS_OUTPUT.PUT_LINE_UDF(varray_variable(i));
        END LOOP;
        !!!RESOLVE EWI!!! /*** SSC-EWI-OR0108 - THE FOLLOWING ASSIGNMENT STATEMENT IS NOT SUPPORTED BY SNOWFLAKE SCRIPTING ***/!!!
        varray_variable(1) := 40;
        FOR i IN 1 TO 0 /*varray_variable.COUNT*/!!!RESOLVE EWI!!! /*** SSC-EWI-0073 - PENDING FUNCTIONAL EQUIVALENCE REVIEW FOR 'VARRAY CUSTOM TYPE EXPRESSION' NODE ***/!!! LOOP
            --** SSC-FDM-OR0035 - CHECK UDF IMPLEMENTATION FOR DBMS_OUTPUT.PUT_LINE_UDF. **
            CALL DBMS_OUTPUT.PUT_LINE_UDF(varray_variable(i));
        END LOOP;
    END;
$$;

CALL varray_iteration();

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 the 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. SSC-EWI-0058: Functionality is not currently supported by Snowflake Scripting.

2. SSC-EWI-0062: Custom type usage changed to variant.

3. SSC-EWI-0073: Pending Functional Equivalence Review.

4. SSC-EWI-OR0108: The Following Assignment Statement is Not Supported by Snowflake Scripting.

5. SSC-FDM-OR0035: DBMS_OUTPUT.PUTLINE check UDF implementation.