This is a translation reference to convert the Oracle Collection Methods 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
A collection method is a PL/SQL subprogram—either a function that returns information about a collection or a procedure that operates on a collection. Collection methods make collections easier to use and your applications easier to maintain.
Some of these methods can be mapped to native Snowflake semi-structured operations. The ones that can't or have differences will be mapped to a UDF implementation.
Current SnowConvert Support
The next table shows a summary of the current support provided by the SnowConvert tool. Please keep into account that translations may still not be final, and more work may be needed.
This method returns the count of "non-undefined" (not to be confused with null) elements within a collection (nested tables can become sparse leaving these elements in between). In associative arrays, it returns the number of keys in the array.
Oracle
CREATE OR REPLACE PROCEDURE collection_countIS TYPE varray_typ IS VARRAY(5) OF INTEGER; TYPE nt_typ IS TABLE OF INTEGER; TYPE aa_typ IS TABLE OF INTEGER INDEX BY VARCHAR2(20); associative_array aa_typ := aa_typ('abc'=>1, 'bca'=>1); varray_variable varray_typ := varray_typ(1, 2, 3); nt_variable nt_typ := nt_typ(1, 2, 3, 4);BEGIN DBMS_OUTPUT.PUT_LINE(associative_array.COUNT); DBMS_OUTPUT.PUT_LINE(varray_variable.COUNT); DBMS_OUTPUT.PUT_LINE(nt_variable.COUNT);END;CALL collection_count();
DBMS OUTPUT
-----------
2
3
4
Snowflake
The snowflake equivalent is the ARRAY_SIZE method.
This method returns true if the given element is contained within the collection. In associative arrays, it tests if the key is contained.
Oracle
CREATE OR REPLACE PROCEDURE collection_existsIS TYPE nt_typ IS TABLE OF INTEGER; TYPE aa_typ IS TABLE OF INTEGER INDEX BY VARCHAR2(20); associative_array aa_typ := aa_typ('abc'=>1, 'bca'=>1); nt_variable nt_typ := nt_typ(1, 2, 3, 4);BEGIN IF associative_array.EXISTS('abc') THEN DBMS_OUTPUT.PUT_LINE('Found'); END IF; IF NOT associative_array.EXISTS('not found') THEN DBMS_OUTPUT.PUT_LINE('Not found'); END IF; IF nt_variable.EXISTS(1) THEN DBMS_OUTPUT.PUT_LINE('Found'); END IF; IF NOT nt_variable.EXISTS(5) THEN DBMS_OUTPUT.PUT_LINE('Not found'); END IF;END;/CALL collection_exists();
DBMS OUTPUT
-----------
2
3
4
Snowflake
The snowflake equivalent is the ARRAY_CONTAINS method. Note that, when using Varchar elements, casting to Variant is necessary.
CREATE OR REPLACE PROCEDURE PUBLIC.collection_exists()RETURNS VARCHARLANGUAGE SQLEXECUTE AS CALLERAS$$DECLARE associative_array OBJECT := OBJECT_CONSTRUCT('abc', 1, 'bca', 1); nt_variable ARRAY := ARRAY_CONSTRUCT(1, 2, 3, 4);BEGIN IF (ARRAY_CONTAINS('abc'::VARIANT, OBJECT_KEYS(associative_array))) THEN CALL DBMS_OUTPUT.PUT_LINE('Found'); END IF; IF (NOT ARRAY_CONTAINS('not found'::VARIANT, OBJECT_KEYS(associative_array))) THEN CALL DBMS_OUTPUT.PUT_LINE('Not found'); END IF; IF (ARRAY_CONTAINS(1, nt_variable)) THEN CALL DBMS_OUTPUT.PUT_LINE('Found'); END IF; IF (NOT ARRAY_CONTAINS(5, nt_variable)) THEN CALL DBMS_OUTPUT.PUT_LINE('Not found'); END IF;END;$$;CALL PUBLIC.collection_exists();SELECT * FROM DBMS_OUTPUT.DBMS_OUTPUT_LOG;
DBMS OUTPUT
-----------
2
3
4
FIRST/LAST
These two methods return the First/Last element of the collection, respectively. If the collection is empty it returns null. This operation is mapped to a UDF, which will be added in further revisions.
Oracle
CREATE OR REPLACE PROCEDURE collection_first_lastIS TYPE nt_typ IS TABLE OF INTEGER; TYPE aa_typ IS TABLE OF INTEGER INDEX BY VARCHAR2(20); associative_array aa_typ := aa_typ('abc'=>1, 'bca'=>1); nt_variable nt_typ := nt_typ();BEGIN DBMS_OUTPUT.PUT_LINE(associative_array.FIRST); DBMS_OUTPUT.PUT_LINE(associative_array.LAST); DBMS_OUTPUT.PUT_LINE(nt_variable.FIRST); DBMS_OUTPUT.PUT_LINE(nt_variable.LAST); nt_variable := nt_typ(1, 2, 3, 4); DBMS_OUTPUT.PUT_LINE(nt_variable.FIRST); DBMS_OUTPUT.PUT_LINE(nt_variable.LAST);END;/CALL collection_first_last();
DBMS OUTPUT
-----------
abc
bca
--These empty spaces are due to it evaluating to null
1
4
DBMS OUTPUT
-----------
abc
bca
--These empty spaces are due to it evaluating to null
1
4
DELETE
This method is used to remove elements from a Collection. It has three possible variants:
.DELETE removes all elements.
.DELETE(n) removes the element whose index matches 'n'.
.DELETE(n, m) removes in the indexes from 'n' through 'm'.
In Oracle, using this operation on Nested Tables causes it to have "undefined" elements within it due to them being sparse.
Please note that the second and third versions do not apply to Varrays.
Oracle
For the sake of simplicity, this sample only checks on the number of elements but may be modified to display the contents of each collection.
CREATE OR REPLACE PROCEDURE collection_deleteIS TYPE varray_typ IS VARRAY(5) OF INTEGER; TYPE nt_typ IS TABLE OF INTEGER; TYPE aa_typ IS TABLE OF INTEGER INDEX BY VARCHAR2(20); associative_array1 aa_typ := aa_typ('abc'=>1, 'def'=>2, 'ghi'=>3, 'jkl'=>4); associative_array2 aa_typ := aa_typ('abc'=>1, 'def'=>2, 'ghi'=>3, 'jkl'=>4); associative_array3 aa_typ := aa_typ('abc'=>1, 'def'=>2, 'ghi'=>3, 'jkl'=>4); varray_variable1 varray_typ := varray_typ(1, 2, 3, 4); nt_variable1 nt_typ := nt_typ(1, 2, 3, 4); nt_variable2 nt_typ := nt_typ(1, 2, 3, 4); nt_variable3 nt_typ := nt_typ(1, 2, 3, 4);BEGIN varray_variable1.DELETE;--delete everything nt_variable1.DELETE;--delete everything nt_variable2.DELETE(2);--delete second position nt_variable3.DELETE(2, 3);--delete range associative_array1.DELETE;--delete everything associative_array2.DELETE('def');--delete second position associative_array3.DELETE('def', 'jkl');--delete range DBMS_OUTPUT.PUT_LINE(varray_variable1.COUNT); DBMS_OUTPUT.PUT_LINE(nt_variable1.COUNT); DBMS_OUTPUT.PUT_LINE(nt_variable2.COUNT); DBMS_OUTPUT.PUT_LINE(nt_variable3.COUNT); DBMS_OUTPUT.PUT_LINE(associative_array1.COUNT); DBMS_OUTPUT.PUT_LINE(associative_array2.COUNT); DBMS_OUTPUT.PUT_LINE(associative_array3.COUNT);END;/CALL collection_delete();
DBMS OUTPUT
-----------
0
0
3
2
0
3
1
Snowflake
Snowflake does not support deletions from an existing ARRAY and for this reason, the only offered workaround is to rebuild a new ARRAY depending on the original parameters of the DELETE.
Note that a UDF was added to implement the functionality for the update of the element.
This method is used to remove the last elements from a Nested Table or a Varray. It has two possible variants:
.TRIM removes the last element.
.TRIM(n) removes the last 'n' elements.
This functionality may be implemented using ARRAY_SLICE
Oracle
CREATE OR REPLACE PROCEDURE collection_trimIS TYPE varray_typ IS VARRAY(5) OF INTEGER; TYPE nt_typ IS TABLE OF INTEGER; varray_variable1 varray_typ := varray_typ(1, 2, 3); nt_variable1 nt_typ := nt_typ(1, 2, 3, 4);BEGIN varray_variable1.TRIM; nt_variable1.TRIM(2); DBMS_OUTPUT.PUT_LINE(nt_variable1.COUNT); DBMS_OUTPUT.PUT_LINE(varray_variable1.COUNT);END;/CALL collection_trim();
DBMS OUTPUT
-----------
2
2
LIMIT
This method returns the maximum limit of a Varray.
This method is not supported in Snowflake.
Oracle
CREATE OR REPLACE PROCEDURE collection_limitIS TYPE varray_typ1 IS VARRAY(5) OF INTEGER; TYPE varray_typ2 IS VARRAY(6) OF INTEGER; varray_variable1 varray_typ1 := varray_typ1(1, 2, 3); varray_variable2 varray_typ2 := varray_typ2(1, 2, 3, 4);BEGIN DBMS_OUTPUT.PUT_LINE(varray_variable1.LIMIT); DBMS_OUTPUT.PUT_LINE(varray_variable2.LIMIT);END;/CALL collection_limit();
DBMS OUTPUT
-----------
5
6
PRIOR/NEXT
This method returns the prior/next index, given an index. If there is not a prior/next then it returns null. It is most frequently used to traverse a collection.
Oracle
CREATE OR REPLACE PROCEDURE collection_prior_nextIS TYPE varray_typ1 IS VARRAY(5) OF INTEGER; TYPE aa_typ IS TABLE OF INTEGER INDEX BY VARCHAR2(20); varray_variable1 varray_typ1 := varray_typ1(-1, -2, -3); associative_array1 aa_typ := aa_typ('abc'=>1, 'def'=>2, 'ghi'=>3, 'jkl'=>4);BEGIN DBMS_OUTPUT.PUT_LINE(varray_variable1.PRIOR(1)); DBMS_OUTPUT.PUT_LINE(varray_variable1.PRIOR(2)); DBMS_OUTPUT.PUT_LINE(varray_variable1.NEXT(2)); DBMS_OUTPUT.PUT_LINE(varray_variable1.NEXT(3)); DBMS_OUTPUT.PUT_LINE(associative_array1.PRIOR('abc')); DBMS_OUTPUT.PUT_LINE(associative_array1.PRIOR('def')); DBMS_OUTPUT.PUT_LINE(associative_array1.NEXT('ghi')); DBMS_OUTPUT.PUT_LINE(associative_array1.NEXT('jkl')); DBMS_OUTPUT.PUT_LINE(associative_array1.PRIOR('not found'));END;/CALL collection_prior_next();
DBMS OUTPUT
-----------
-- Empty spaces are due to null results
1
3
abc
jkl
jkl
Known Issues
1. Limit method is not supported in Snowflake
Snowflake does not have support for limited-space varrays. For this reason, this method is not supported.