Nested Table Type Definition

This is a translation reference to convert the Nested Table Variant of the Oracle Create Type Statements (UDT's) to Snowflake

SnowConvert only recognizes these definitions, does not support any translation and there is no known workaround for them.

Description

Nested Table Types define an embedded table structure of a previously existing datatype (including other Custom Types). They can be used as a more powerful version of the Array Type.

Unlike any of the other types, there is still no known workaround or any possible translation for them.

CREATE TYPE <type name> AS TABLE OF <data type>

Sample Source Patterns

Nested Table Type usage

Oracle

Query

IN -> Oracle_01.sql

CREATE TYPE textdoc_typ AS OBJECT (
    document_typ VARCHAR2(32),
    formatted_doc BLOB
);
/

CREATE TYPE textdoc_tab AS TABLE OF textdoc_typ;
/

Snowflake

Snowflake

Query

OUT -> Oracle_01.sql

!!!RESOLVE EWI!!! /*** SSC-EWI-0056 - CUSTOM TYPES ARE NOT SUPPORTED IN SNOWFLAKE BUT REFERENCES TO THIS CUSTOM TYPE WERE CHANGED TO VARIANT ***/!!!
CREATE TYPE textdoc_typ AS OBJECT (
    document_typ VARCHAR2(32),
    formatted_doc BLOB
)
;

!!!RESOLVE EWI!!! /*** SSC-EWI-0073 - PENDING FUNCTIONAL EQUIVALENCE REVIEW FOR 'NESTED TABLE' NODE ***/!!!

CREATE TYPE textdoc_tab AS TABLE OF textdoc_typ;

Known Issues

1. Create Type creation options are not supported

Currently, there is no known workaround for any of the creation options; for these reasons, they are not taken into account when defining the type.

Related EWIs

1. SSC-EWI-0073: Pending Functional Equivalence Review

2. SSC-EWI-0056: Create Type Not Supported.