Object Type Definition

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

SnowConvert supports a translation for Object Type Definitions itself. However their usages are still a work in progress.

Description

Object Types 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.

For the translation of object types, the type definition is replaced by a Semi-structured Data Type and then it is expanded on any usages across the code. For tables this means replacing the column for a Variant, adding a View so that selects (and also Views) to the original table can still function.

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

Sample Source Patterns

Inserts for Simple Type usage

The next data will be inserted inside the table before querying the select. Please note these Inserts currently need to be manually migrated into Snowflake.

Oracle

INSERT INTO customer_table_demo(customer_table_id, customer_data) values
(1, customer_typ_demo(1, 'First Name 1', 'Last Name 1'));

INSERT INTO customer_table_demo(customer_table_id, customer_data) values
(2, customer_typ_demo(2, 'First Name 2', 'Last Name 2'));

Snowflake

INSERT INTO customer_table_demo(customer_table_id, customer_data)
SELECT 1, OBJECT_CONSTRUCT('customer_id', 1,
                           'cust_first_name', 'First Name 1',
                           'cust_last_name', 'Last Name 1');

INSERT INTO customer_table_demo(customer_table_id, customer_data)
SELECT 2, OBJECT_CONSTRUCT('customer_id', 2,
                           'cust_first_name', 'First Name 2',
                           'cust_last_name', 'Last Name 2');

Simple Type usage

Oracle

-- TYPE DEFINITION
CREATE TYPE customer_typ_demo AS OBJECT
    ( customer_id        INTEGER
    , cust_first_name    VARCHAR2(20)
    , cust_last_name     VARCHAR2(20)
    ) ;

CREATE TABLE customer_table_demo
    ( customer_table_id  INTEGER
    , customer_data      customer_typ_demo
    ) ;

SELECT * FROM customer_table_demo;

Snowflake

-- ** MSC-WARNING - MSCEWI1056 - CUSTOM TYPES ARE NOT SUPPORTED IN SNOWFLAKE BUT REFERENCES TO THIS CUSTOM TYPE WERE CHANGED TO A VARIANT **
---- TYPE DEFINITION
--CREATE TYPE customer_typ_demo AS OBJECT
--    ( customer_id        INTEGER
--    , cust_first_name VARCHAR(20)
--, cust_last_name VARCHAR(20)
--)
  ;

CREATE OR REPLACE TABLE PUBLIC.customer_table_demo ( customer_table_id  INTEGER,
customer_data VARIANT /*** MSC-WARNING - MSCEWI1062 - CUSTOM TYPE 'customer_typ_demo' USAGE CHANGED TO VARIANT ***/);

--** MSC-WARNING - MSCEWI1063 - ADDED STATEMENTS BECAUSE 'customer_table_demo' USED A CUSTOM TYPE **
CREATE OR REPLACE VIEW PUBLIC.customer_table_demo_view AS
SELECT
customer_table_id,
customer_data:customer_id :: INTEGER AS customer_id,
customer_data:cust_first_name :: VARCHAR AS cust_first_name,
customer_data:cust_last_name :: VARCHAR AS cust_last_name
FROM PUBLIC.customer_table_demo;

SELECT * FROM PUBLIC.customer_table_demo_view;

Inserts for Nested Type Usage

These statements need to be placed between the table creation and the select statement to test the output.

Oracle

INSERT INTO customer_table_demo(customer_id, customer_data) values
(1, customer_typ_demo('Customer 1', email_typ_demo('email@domain.com')));

INSERT INTO customer_table_demo(customer_id, customer_data) values
(2, customer_typ_demo('Customer 2', email_typ_demo('email2@domain.com')));

Snowflake

INSERT INTO customer_table_demo(customer_table_id, customer_data)
SELECT 1, OBJECT_CONSTRUCT('cust_name', 'Customer 1',
                           'cust_email', OBJECT_CONSTRUCT('email', 'email@domain.com');

INSERT INTO customer_table_demo(customer_table_id, customer_data)
SELECT 1, OBJECT_CONSTRUCT('cust_name', 'Customer 2',
                           'cust_email', OBJECT_CONSTRUCT('email', 'email2@domain.com');

Nested Type Usage

Oracle

-- TYPE DEFINITIONS
CREATE TYPE email_typ_demo AS OBJECT
    ( email            VARCHAR2(20) );

CREATE TYPE customer_typ_demo AS OBJECT
    ( cust_name        VARCHAR2(20)
    , cust_email       email_typ_demo
    ) ;

CREATE TABLE customer_table_demo
    ( customer_id      INTEGER
    , customer_data    customer_typ_demo
    ) ;

SELECT * FROM customer_table_demo;

Snowflake

-- ** MSC-WARNING - MSCEWI1056 - CUSTOM TYPES ARE NOT SUPPORTED IN SNOWFLAKE BUT REFERENCES TO THIS CUSTOM TYPE WERE CHANGED TO A VARIANT **
---- TYPE DEFINITIONS
--CREATE TYPE email_typ_demo AS OBJECT
--    ( email VARCHAR(20) )
                         ;

-- ** MSC-WARNING - MSCEWI1056 - CUSTOM TYPES ARE NOT SUPPORTED IN SNOWFLAKE BUT REFERENCES TO THIS CUSTOM TYPE WERE CHANGED TO A VARIANT **
--CREATE TYPE customer_typ_demo AS OBJECT
--    ( cust_name VARCHAR(20)
--, cust_email VARIANT /*** MSC-WARNING - MSCEWI1062 - CUSTOM TYPE 'email_typ_demo' USAGE CHANGED TO VARIANT ***/
--)
  ;

CREATE OR REPLACE TABLE PUBLIC.customer_table_demo ( customer_id      INTEGER,
customer_data VARIANT /*** MSC-WARNING - MSCEWI1062 - CUSTOM TYPE 'customer_typ_demo' USAGE CHANGED TO VARIANT ***/);

--** MSC-WARNING - MSCEWI1063 - ADDED STATEMENTS BECAUSE 'customer_table_demo' USED A CUSTOM TYPE **
CREATE OR REPLACE VIEW PUBLIC.customer_table_demo_view AS
SELECT
customer_id,
customer_data:cust_name :: VARCHAR AS cust_name,
customer_data:cust_email:email :: VARCHAR AS email
FROM PUBLIC.customer_table_demo;

SELECT * FROM PUBLIC.customer_table_demo_view;

Known Issues

1. Migrated code output is not the same

The view statement is being changed unnecessarily, which makes the table no longer have the same behavior in the output code. There is a work item to fix this issue.

2. DML for User-defined Types is not being transformed

DML that interacts with elements that have User-defined types within them (like a table) are not being transformed. There is a work item to implement this in the future.

3. 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. MSCEWI1055: Referenced Custom Type not found.

  2. MSCEWI1056: Custom Types Not Supported.

  3. MSCEWI1062​: Custom type usage changed to variant.

  4. ​MSCEWI1063: ​Added statements because object used a custom type.

  5. ​MSCEWI1064: ​Referenced custom type in query not found.

  6. ​MSCEWI1065​: Columns from expression not found.

  7. ​MSCEWI3082​: Cannot Convert Nested Type Attribute Expression.

PreviousCreate TypeNextSubtype Definition

Last updated