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.

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

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

IN -> Oracle_01.sql

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

OUT -> Oracle_01.sql

!!!RESOLVE EWI!!! /*** SSC-EWI-0073 - PENDING FUNCTIONAL EQUIVALENCE REVIEW FOR 'VARYING ARRAY' NODE ***/!!!
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;

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. SSC-EWI-0062: Custom type usage changed to variant.

2. SSC-EWI-0073: Pending Functional Equivalence Review.