Associative Array Type Definition

This is a translation reference to convert the Oracle Associative Array Declaration to Snowflake

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

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

Description

An associative array (formerly called PL/SQL table or index-by table) is a set of key-value pairs. Each key is a unique index, used to locate the associated value with the syntax variable_name(index).

The data type of index can be either a string type (VARCHAR2, VARCHAR, STRING, or LONG) or PLS_INTEGER. Indexes are stored in sort order, not creation order. For string types, sort order is determined by the initialization parameters NLS_SORT and NLS_COMP.

(Oracle PL/SQL Language Reference ASSOCIATIVE ARRAYS)

Not to be confused with the PL/SQL NESTED TABLE Type definition.

For the translation, the type definition is replaced by an OBJECT Semi-structured Data Type and then its usages are changed accordingly across any operations.

In order to define an Associative Array type, the syntax is as follows:

type_definition := TYPE IS TABLE OF datatype INDEX BY indexing_datatype;

indexing_datatype := { PLS_INTEGER
                     | BINARY_INTEGER
                     | string_datatype
                     }

To declare a variable of this type:

variable_name collection_type;

Sample Source Patterns

Varchar-indexed Associative Array

Oracle

Query

CREATE OR REPLACE PROCEDURE associative_array
IS
    TYPE associate_array_typ IS TABLE OF INTEGER
        INDEX BY VARCHAR2(50);
        
    associate_array associate_array_typ := associate_array_typ();
    associate_index VARCHAR2(50);
BEGIN
    associate_array('abc') := 1;
    associate_array('bca') := 2;
    associate_array('def') := 3;
    
    DBMS_OUTPUT.PUT_LINE(associate_array('abc'));
    associate_array('abc') := 4;
    --THROWS 'NO DATA FOUND'
    --DBMS_OUTPUT.PUT_LINE(associate_array('no exists'));
    
    DBMS_OUTPUT.PUT_LINE(associate_array.COUNT);
    
    associate_index := associate_array.FIRST;
    WHILE associate_index IS NOT NULL
    LOOP
        DBMS_OUTPUT.PUT_LINE(associate_array(associate_index));
        associate_index := associate_array.NEXT(associate_index);
    END LOOP;
END;

CALL associative_array();

Result

DBMS OUTPUT
-----------
1
3
4
2
3

Snowflake

Please note the 'true' parameter in the OBJECT_INSERT. This is so that the element is updated if it is already present in the array.

Query

CREATE OR REPLACE PROCEDURE PUBLIC.associative_array ()
RETURNS VARCHAR
LANGUAGE SQL
EXECUTE AS CALLER
AS
$$
   DECLARE
      associate_array OBJECT := OBJECT_CONSTRUCT();
      associate_index VARCHAR(50);
   BEGIN
      associate_array := OBJECT_INSERT(associate_array, 'abc', 1, true);
      associate_array := OBJECT_INSERT(associate_array, 'bca', 2, true);
      associate_array := OBJECT_INSERT(associate_array, 'def', 3, true);

      CALL DBMS_OUTPUT.PUT_LINE(:associate_array['abc']);
      CALL DBMS_OUTPUT.PUT_LINE(:associate_array['not found']);
      
      associate_array := OBJECT_INSERT(:associate_array, 'abc', 4, true);
    
      CALL DBMS_OUTPUT.PUT_LINE(ARRAY_SIZE(OBJECT_KEYS(:associate_array)));
      
      FOR i IN 1 TO ARRAY_SIZE(OBJECT_KEYS(:associate_array))
      LOOP
         associate_index := OBJECT_KEYS(:associate_array)[:i-1];
         CALL DBMS_OUTPUT.PUT_LINE(:associate_array[:associate_index]);
      END LOOP;
   END;
$$;

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

Result

DBMS OUTPUT
-----------
1

3
4
2
3

Numeric-indexed Associative Array

Oracle