COLLECTIONS AND RECORDS
Translation reference to convert Oracle COLLECTIONS and RECORDS to Snowflake Scripting
This section is a work in progress, information may change in the future.
General Description
PL/SQL lets you define two kinds of composite data types: collection and record, where composite is a data type that stores values that have internal components.
In a collection, the internal components always have the same data type, and are called elements.
In a record, the internal components can have different data types, and are called fields. (Oracle PL/SQL Language Reference COLLECTIONS AND RECORDS)
Please take into account the CREATE TYPE statement translation reference since some workarounds can overlap and may be functional in both scenarios.
Limitations
Snowflake doesn't support user-defined data types, which includes PL Collections and Records, according to its online documentation Unsupported Data Types, but it supports Semi-structured Data Types, which can be used to mimic both the hierarchy-like structure of Record and the element structure of Collection User-defined types. For this reason, there are multiple types of features that have no workaround.
Following are the features for which NO workaround is proposed:
Variable size cannot exceed 16MB
Snowflake sets VARIANT, OBJECT, and ARRAY's maximum size on 16MBs. This means that if a Record, a Collection, or any element of either exceeds this size it will cause a Runtime Error.
Varray capacity cannot be limited
Oracle's varrays offer the capacity to limit the number of elements within them. This is not supported by Snowflake.
Proposed Workaround
About Record types definition
The proposed workaround is to use an "OBJECT" semi-structured data type to mimic Oracle's data type.
About Collection types definition
There are two different workarounds that depend on the type of collection to be migrated:
Associative Arrays are proposed to be changed into an "OBJECT" semi-structured data type.
Varrays and Nested Table Arrays are proposed to be changed into an "ARRAY" semi-structured data type.
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.
Known Issues
1. Associate Arrays are considered a Nested Table
As of now, SnowConvert doesn't differentiate between an Associative Array and a Nested Table meaning they are mixed up in the same assessment counts.
Related EWIs
No related EWIs.
Last updated