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.
Sample Source Patterns
COUNT
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
CREATEORREPLACEPROCEDURE collection_countISTYPE varray_typ IS VARRAY(5) OF INTEGER;TYPE nt_typ ISTABLE OF INTEGER;TYPE aa_typ ISTABLE OF INTEGERINDEXBYVARCHAR2(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.