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

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

-- ** MSC-WARNING - MSCEWI1056 - CUSTOM TYPES ARE NOT SUPPORTED IN SNOWFLAKE BUT REFERENCES TO THIS CUSTOM TYPE WERE CHANGED TO A VARIANT **
--CREATE TYPE textdoc_typ AS OBJECT
--    ( document_typ VARCHAR(32)
--    , formatted_doc BINARY /*** MSC-WARNING - MSCEWI1036 - BLOB DATA TYPE "" CONVERTED TO BINARY ***/
--    )
      ;
-- ** MSC-ERROR - MSCEWI1057 - CREATE TYPE NESTED TABLE VARIANT IS NOT SUPPORTED **
--CREATE TYPE textdoc_tab AS TABLE OF VARIANT /*** MSC-WARNING - MSCEWI1062 - CUSTOM TYPE 'textdoc_typ' USAGE CHANGED TO VARIANT ***/
                                                                                                                   ;

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. MSCEWI1057: Create Type Variant Not Supported.