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.

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

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

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.

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;

Numeric-indexed Associative Array

Oracle

CREATE OR REPLACE PROCEDURE numeric_associative_array
IS
    TYPE numeric_associative_array_typ IS TABLE OF INTEGER
        INDEX BY PLS_INTEGER;
        
    associate_array numeric_associativ
    e_array_typ := numeric_associative_array_typ();
    associate_index PLS_INTEGER;
BEGIN
    associate_array(1) := -1;
    associate_array(2) := -2;
    associate_array(3) := -3;
    
    DBMS_OUTPUT.PUT_LINE(associate_array(1));
    associate_array(1) := -4;
    
    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 numeric_associative_array();

Snowflake

Please note that the numeric value is converted to varchar accordingly when the operation needs it. Additionally, note the 'true' parameter in the OBJECT_INSERT. This is so that the element is updated if it is already present in the array.

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

      CALL DBMS_OUTPUT.PUT_LINE(:associate_array['1']);
      
      associate_array := OBJECT_INSERT(:associate_array, '1', -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::VARCHAR]);
      END LOOP;
   END;
$$;

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

Record-element Numeric-indexed Associative Array

In this case, the associative array is composed of a Record-structure, and this structure needs to be preserved. For this purpose, further operations on insertions were added.

Oracle

CREATE OR REPLACE PROCEDURE record_associative_array
IS
    TYPE record_typ IS RECORD(col1 INTEGER);
    TYPE record_associative_array_typ IS TABLE OF record_typ
        INDEX BY PLS_INTEGER;
        
    associate_array record_associati ve_array_typ := record_associative_array_typ();
    associate_index PLS_INTEGER;
BEGIN
    associate_array(1).col1 := -1;
    associate_array(2).col1 := -2;
    associate_array(3).col1 := -3;
    
    DBMS_OUTPUT.PUT_LINE(associate_array(1).col1);
    associate_array(4).col1 := -4;
    
    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).col1);
        associate_index := associate_array.NEXT(associate_index);
    END LOOP;
END;
/

CALL record_associative_array();

Snowflake

In this scenario, the insertion/update assumes an automatic creation of the record within the associative array and this needs to be taken into account when creating new records.

CREATE OR REPLACE PROCEDURE PUBLIC.record_associative_array ()
RETURNS VARCHAR
LANGUAGE SQL
EXECUTE AS CALLER
AS
$$
   DECLARE
      associate_array OBJECT := OBJECT_CONSTRUCT();
      associate_index NUMBER;
   BEGIN
      associate_array := OBJECT_INSERT(associate_array, '1', OBJECT_INSERT(NVL(associate_array['1'], OBJECT_CONSTRUCT()), 'col1', -1, true), true);
      associate_array := OBJECT_INSERT(associate_array, '2', OBJECT_INSERT(NVL(associate_array['2'], OBJECT_CONSTRUCT()), 'col1', -2, true), true);
      associate_array := OBJECT_INSERT(associate_array, '3', OBJECT_INSERT(NVL(associate_array['3'], OBJECT_CONSTRUCT()), 'col1', -3, true), true);

      CALL DBMS_OUTPUT.PUT_LINE(:associate_array['1']:col1);
      
      associate_array := OBJECT_INSERT(associate_array, '1', OBJECT_INSERT(NVL(associate_array['1'], OBJECT_CONSTRUCT()), 'col1', -4, true), 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::VARCHAR]:col1);
      END LOOP;
   END;
$$;

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

Known Issues

1. They are currently not being recognized

SnowConvert treats these collections as Nested Table Arrays. There is a work item to fix this.

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.

PreviousCOLLECTIONS AND RECORDSNextVarray Type Definition

Last updated