Array Type Definition
This is a translation reference to convert the Array Variant of the Oracle Create Type Statements (UDT's) to Snowflake
SnowConvert only recognizes these definitions and for the moment does not support any translation for them. This page is only used as a future reference for translations.
Description
Array Types define an array structure of a previously existing datatype (including other Custom Types).
For the translation of array types, the type definition is replaced by a Semi-structured Data Type and then it is expanded on any usages across the code. This means taking type's definition and then expanding it on the original code.
CREATE TYPE <type name>
AS { VARRAY | [VARYING] ARRAY } ( <size limit> ) OF <data type>
Sample Source Patterns
Inserts for the array 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, phone_list_typ_demo('2000-0000', '4000-0000', '0000-0000'));
INSERT INTO customer_table_demo(customer_table_id, customer_data) VALUES
(1, phone_list_typ_demo('8000-2000', '0000-0000', '5000-0000'));
Snowflake
INSERT INTO customer_table_demo(customer_table_id, customer_data)
SELECT 1, ARRAY_CONSTRUCT('2000-0000', '4000-0000', '0000-0000');
INSERT INTO customer_table_demo(customer_table_id, customer_data)
SELECT 1, ARRAY_CONSTRUCT('8000-2000', '0000-0000', '5000-0000');
Array Type usage
Oracle
CREATE TYPE phone_list_typ_demo AS VARRAY(3) OF VARCHAR2(25);
/
CREATE TABLE customer_table_demo (
customer_table_id INTEGER,
customer_data phone_list_typ_demo
);
/
SELECT * FROM customer_table_demo;
/
Snowflake
CREATE TYPE phone_list_typ_demo AS VARRAY(3) OF VARCHAR2(25);
CREATE OR REPLACE TABLE customer_table_demo (
customer_table_id INTEGER,
customer_data VARIANT !!!RESOLVE EWI!!! /*** SSC-EWI-0062 - CUSTOM TYPE 'phone_list_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
AS
SELECT
customer_table_id,
customer_data
FROM
customer_table_demo;
SELECT * FROM
customer_table_demo_view;
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.
2. Migrated code output is not functional
The statements are being changed unnecessarily, which makes them no longer be functional on the output code. This will be addressed when a proper transformation for them is in place.
Related EWIs
SSC-EWI-0073: Pending Functional Equivalence Review.
SSC-EWI-0062: Custom type usage changed to variant.
SSC-EWI-0063: Added statements because object used a custom type.
Last updated