Subtype Definition

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

Since there are no known workarounds, SnowConvert only recognizes these definitions and does not support any translation for them.

Description

Subtypes define a structure of data similar to a record, with the added advantages of the member function definitions. Meaning that their data may be used along some behavior within the type. Unlike Object Types, Subtypes are built as an extension to another existing type.

Regarding subtype definitions, there is still no translation, but there might be a way to reimplement them using Object Type Definitions and then using their respective translation.

CREATE TYPE <type name> UNDER <super type name>
( [{<type column definition> | type method definition } , ...]);

Sample Source Patterns

Subtypes under an Object Type

Oracle

Query

IN -> Oracle_01.sql

CREATE TYPE person_t AS OBJECT (name VARCHAR2(100), ssn INTEGER) 
   NOT FINAL;
/

CREATE TYPE employee_t UNDER person_t 
   (department_id INTEGER, salary INTEGER) 
   NOT FINAL;
/

CREATE TYPE part_time_emp_t UNDER employee_t (num_hrs INTEGER);
/

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 person_t AS OBJECT (name VARCHAR2(100), ssn INTEGER)
   NOT FINAL;

--!!!RESOLVE EWI!!! /*** SSC-EWI-OR0007 - CREATE TYPE SUBTYPE IS NOT SUPPORTED IN SNOWFLAKE ***/!!!

--CREATE TYPE employee_t UNDER person_t
--   (department_id INTEGER, salary INTEGER)
--   NOT FINAL
            ;

--!!!RESOLVE EWI!!! /*** SSC-EWI-OR0007 - CREATE TYPE SUBTYPE IS NOT SUPPORTED IN SNOWFLAKE ***/!!!

--CREATE TYPE part_time_emp_t UNDER employee_t (num_hrs INTEGER)
                                                              ;

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-0056: Create Type Not Supported.
2. SSC-EWI-OR0007: Create Type Not Supported in Snowflake.