Nested Table Array Type Definition

This is a translation reference to convert the Oracle Nested Table Array 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 Nested Table Arrays, for the Standalone Version please see Nested Table Type Definition.

Description

In the database, a nested table is a column type that stores an unspecified number of rows in no particular order.

When you retrieve a nested table value from the database into a PL/SQL nested table variable, PL/SQL gives the rows consecutive indexes, starting at 1. Using these indexes, you can access the individual rows of the nested table variable. The syntax is variable_name(index). The indexes and row order of a nested table might not remain stable as you store and retrieve the nested table from the database.

(Oracle PL/SQL Language Reference NESTED TABLES)

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 Nested Table Array type, the syntax is as follows:

type_definition := TYPE IS TABLE OF datatype;

To declare a variable of this type:

variable_name collection_type;

Sample Source Patterns

Nested Table Array definitions

This illustrates how to create different nested table arrays, and how to migrate the definitions for the variables.

Oracle

Query

CREATE OR REPLACE PROCEDURE nested_table_procedure
IS
    TYPE nested_table_array_typ IS TABLE OF INTEGER;
    TYPE nested_table_array_typ2 IS TABLE OF DATE;
    
    nested_table_array nested_table_array_typ;
    nested_table_array2 nested_table_array_typ2;
BEGIN
    NULL;
END;

Snowflake

Query

CREATE OR REPLACE PROCEDURE nested_table_procedure()
RETURNS INTEGER
LANGUAGE SQL
EXECUTE AS CALLER
AS
$$
DECLARE
    -- NO LONGER NEEDED
    /*
    TYPE associative_array_typ IS TABLE OF INTEGER INDEX BY VARCHAR2(30);
    TYPE associative_array_typ2 IS TABLE OF INTEGER INDEX BY PLS_INTEGER;
    */
    
    associative_array ARRAY;
    associative_array2 ARRAY;
BEGIN
    NULL;
END;
$$;

Nested Table iteration

Oracle

Query

CREATE OR REPLACE PROCEDURE nested_table_iteration
IS
    TYPE nested_table_typ IS TABLE OF INTEGER;
    nested_table_variable nested_table_typ := nested_table_typ (10, 20, 30);
BEGIN
    FOR i IN 1..nested_table_variable.COUNT
    LOOP
        DBMS_OUTPUT.PUT_LINE(nested_table_variable(i));
    END LOOP;
    
    nested_table_variable (1) := 40;
    
    FOR i IN 1..nested_table_variable.COUNT
    LOOP
        DBMS_OUTPUT.PUT_LINE(nested_table_variable(i));
    END LOOP;
END;
/

CALL nested_table_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.nested_table_iteration()
RETURNS VARCHAR
LANGUAGE SQL
EXECUTE AS CALLER
AS
$$
DECLARE
    nested_table_variable ARRAY := ARRAY_CONSTRUCT(10, 20, 30);
BEGIN
    FOR i IN 1 TO ARRAY_SIZE(nested_table_variable)
    LOOP
        CALL DBMS_OUTPUT.PUT_LINE(:nested_table_variable[:i-1]);
    END LOOP;

    /*** MSC-WARNING - MSCEWI1020 - CUSTOM UDF 'INSERT_REPLACE_COLLECTION_ELEMENT_UDF' INSERTED. ***/    
    nested_table_variable:= INSERT_REPLACE_COLLECTION_ELEMENT_UDF(nested_table_variable, 1, 40);
    
    FOR i IN 1 TO ARRAY_SIZE(nested_table_variable)
    LOOP
        CALL DBMS_OUTPUT.PUT_LINE(:nested_table_variable[:i-1]);
    END LOOP;
END;
$$;

CALL PUBLIC.nested_table_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.

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.