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

Query

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

Query

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

Query

-- TYPE DECLARATION
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;
/

Results

CUSTOMER_TABLE_ID|CUSTOMER_DATA                          |
-----------------+---------------------------------------+
                1|[['2000-0000','4000-0000','0000-0000']]|
                1|[['8000-2000','0000-0000','5000-0000']]|

Snowflake

Query

-- ** MSC-ERROR - MSCEWI1057 - CREATE TYPE VARYING ARRAY VARIANT IS NOT SUPPORTED **
---- TYPE DECLARATION
--CREATE TYPE phone_list_typ_demo
--    AS VARRAY(3) OF VARCHAR(25)
;

CREATE OR REPLACE TABLE PUBLIC.customer_table_demo ( customer_table_id  INTEGER,
customer_data VARIANT /*** MSC-WARNING - MSCEWI1062 - CUSTOM TYPE 'phone_list_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
FROM PUBLIC.customer_table_demo;

SELECT * FROM PUBLIC.customer_table_demo_view;

Results

CUSTOMER_TABLE_ID|CUSTOMER_DATA                          |
-----------------+---------------------------------------+
                1|{['2000-0000','4000-0000','0000-0000']}|
                1|{['8000-2000','0000-0000','5000-0000']}|

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

1. MSCEWI1057: Create Type Variant Not Supported.