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.

Sub-Feature	Current recognition status	Current translation status	Has Known Workarounds
Record Type Definitions	Recognized.	Not Translated.	Yes.
Associative Array Type Definitions	Not Recognized.	Not Translated.	Yes.
Varray Type Definitions	Recognized.	Not Translated.	Yes.
Nested Table Array Type Definitions	Recognized.	Not Translated.	Yes.

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

1. SSC-FDM-0015: Data Type was Not Recognized.
2. SSC-EWI-0056: Create Type Not Supported.
3. SSC-FDM-0024: Functionality is not currently supported by Snowflake Scripting.
4. SSC-EWI-0062: Custom type usage changed to variant.
5. SSC-EWI-0073: Pending Functional Equivalence Review.