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 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
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
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
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;
CUSTOMER_TABLE_ID|CUSTOMER_DATA |
-----------------+------------------------------+
1|[1, First Name 1, Last Name 1]|
2|[2, First Name 2, Last Name 2]|
Snowflake
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;
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
IN -> Oracle_03.sql
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')));
!!!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;
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.
