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.

Some parts in the output code are omitted for clarity reasons.

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

Query

IN -> Oracle_01.sql

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

Query

OUT -> Oracle_01.sql

INSERT INTO customer_table_demo(customer_table_id, customer_data)
VALUES ( 1, customer_typ_demo(1, 'First Name 1', 'Last Name 1') !!!RESOLVE EWI!!! /*** SSC-EWI-0073 - PENDING FUNCTIONAL EQUIVALENCE REVIEW FOR 'customer_typ_demo' NODE ***/!!!);

INSERT INTO customer_table_demo(customer_table_id, customer_data)
VALUES ( 2, customer_typ_demo(2, 'First Name 2', 'Last Name 2') !!!RESOLVE EWI!!! /*** SSC-EWI-0073 - PENDING FUNCTIONAL EQUIVALENCE REVIEW FOR 'customer_typ_demo' NODE ***/!!!);

Simple Type usage

Oracle

Query

IN -> Oracle_02.sql

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;

Results

CUSTOMER_TABLE_ID|CUSTOMER_DATA                 |
-----------------+------------------------------+
                1|[1, First Name 1, Last Name 1]|
                2|[2, First Name 2, Last Name 2]|

Snowflake

Query

OUT -> Oracle_02.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 customer_typ_demo AS OBJECT (
    customer_id INTEGER,
    cust_first_name VARCHAR2(20),
    cust_last_name VARCHAR2(20)
)
;

CREATE OR REPLACE TABLE customer_table_demo (
        customer_table_id INTEGER,
        customer_data VARIANT !!!RESOLVE EWI!!! /*** SSC-EWI-0062 - CUSTOM TYPE 'customer_typ_demo' USAGE CHANGED TO VARIANT ***/!!!
    )
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"oracle"}}'
;

CREATE OR REPLACE VIEW PUBLIC.customer_table_demo_view
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": {  "major": 0,  "minor": 0,  "patch": "0" }, "attributes": {  "component": "oracle",  "convertedOn": "" }}'
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
        customer_table_demo;

    SELECT * FROM
        customer_table_demo_view;

Results

CUSTOMER_TABLE_ID|CUST_ID|CUST_FIRST_NAME|CUST_LAST_NAME|
-----------------+-------+---------------+--------------+
                1|1      |First Name 1   |Last Name 1   |
                2|2      |First Name 2   |Last Name 2   |

Inserts for Nested Type Usage

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

Oracle

Query

IN -> Oracle_03.sql

INSERT INTO customer_table_demo(customer_id, customer_data) values
(1, customer_typ_demo('Customer 1', email_typ_demo('[e-mail]')));

INSERT INTO customer_table_demo(customer_id, customer_data) values
(2, customer_typ_demo('Customer 2', email_typ_demo('[e-mail]')));

Snowflake

Query

OUT -> Oracle_03.sql

INSERT INTO customer_table_demo(customer_id, customer_data) values
(1, customer_typ_demo('Customer 1', email_typ_demo('[e-mail]') !!!RESOLVE EWI!!! /*** SSC-EWI-0073 - PENDING FUNCTIONAL EQUIVALENCE REVIEW FOR 'email_typ_demo' NODE ***/!!!) !!!RESOLVE EWI!!! /*** SSC-EWI-0073 - PENDING FUNCTIONAL EQUIVALENCE REVIEW FOR 'customer_typ_demo' NODE ***/!!!);

INSERT INTO customer_table_demo(customer_id, customer_data) values
(2, customer_typ_demo('Customer 2', email_typ_demo('[e-mail]') !!!RESOLVE EWI!!! /*** SSC-EWI-0073 - PENDING FUNCTIONAL EQUIVALENCE REVIEW FOR 'email_typ_demo' NODE ***/!!!) !!!RESOLVE EWI!!! /*** SSC-EWI-0073 - PENDING FUNCTIONAL EQUIVALENCE REVIEW FOR 'customer_typ_demo' NODE ***/!!!);

Nested Type Usage

Oracle

Query

IN -> Oracle_04.sql

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;

Results

CUSTOMER_ID|CUSTOMER_DATA                    |
-----------+---------------------------------+
          1|[Customer 1, [[e-mail]]] |
          2|[Customer 2, [[e-mail]]]|

Snowflake

Query

OUT -> Oracle_04.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 email_typ_demo AS OBJECT (email VARCHAR2(20))
;

!!!RESOLVE EWI!!! /*** SSC-EWI-0056 - CUSTOM TYPES ARE NOT SUPPORTED IN SNOWFLAKE BUT REFERENCES TO THIS CUSTOM TYPE WERE CHANGED TO VARIANT ***/!!!

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

CREATE OR REPLACE TABLE customer_table_demo (
    customer_id INTEGER,
    customer_data VARIANT !!!RESOLVE EWI!!! /*** SSC-EWI-0062 - CUSTOM TYPE 'customer_typ_demo' USAGE CHANGED TO VARIANT ***/!!!
)
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"oracle"}}'
;

CREATE OR REPLACE VIEW PUBLIC.customer_table_demo_view
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": {  "major": 0,  "minor": 0,  "patch": "0" }, "attributes": {  "component": "oracle",  "convertedOn": "" }}'
AS
SELECT
    customer_id,
    customer_data:cust_name :: VARCHAR AS cust_name,
    customer_data:cust_email:email :: VARCHAR AS email
FROM
    customer_table_demo;

SELECT * FROM
    customer_table_demo_view;

Results

CUSTOMER_ID|CUST_NAME |CUST_EMAIL       |
-----------+----------+-----------------+
          1|Customer 1|[e-mail] |
          2|Customer 2|[e-mail]|

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. SSC-EWI-0056: Create Type Not Supported.
2. SSC-EWI-0062: Custom type usage changed to variant.
3. SSC-EWI-0073: Pending Functional Equivalence Review.