Last updated
Last updated
This section is a work in progress, information may change in the future
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.
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.
This method returns true if the given element is contained within the collection. In associative arrays, it tests if the key is contained.
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.
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'.
Please note that the second and third versions do not apply to Varrays.
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.
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.
This method is used to append new elements to a Nested Table or a Varray. It has three possible variants:
.EXTEND inserts a null element.
.EXTEND(n) inserts 'n' null elements.
.EXTEND(n, i) inserts 'n' copies of the element at 'i'.
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 method returns the maximum limit of a Varray.
This method is not supported in Snowflake.
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.
Snowflake does not have support for limited-space varrays. For this reason, this method is not supported.
No EWIs related.
The snowflake equivalent is the method.
The snowflake equivalent is the method. Note that, when using Varchar elements, casting to Variant is necessary.
This functionality may be implemented using
This is a translation reference to convert the Oracle Collection Methods to Snowflake
Not Recognized.
Not Translated.
UDF
Not Recognized.
Not Translated.
UDF (To be defined)
Not Recognized.
Not Translated.
UDF
Not Recognized.
Not Translated.
Not Recognized.
Not Translated.
UDF
Not Recognized.
Not Translated.
UDF
Not Recognized.
Not Translated.
Not Recognized.
Not Translated.
Not Supported.
Not Recognized.
Not Translated.
UDF (To be defined)
Not Recognized.
Not Translated.
UDF (To be defined)