Create Type

This is a translation reference to convert Oracle Create Type Statements (UDT's) to snowflake

General Description

One of the most important features the Oracle database engine offers is an Object-Oriented approach. PL/SQL offers capabilities beyond other relational databases in the form of OOP by using Java-like statements in the form of packages, functions, tables and types. This document will cover the last one and how SnowConvert solves it, remaining compliant to functionality.

Oracle supports the following specifications:

• Abstract Data Type (ADT) (including an SQLJ object type).

• Standalone varying array (varray) type.

• Standalone nested table type.

• Incomplete object type.

All this according to the information found in Oracle Create Type Statement Documentation

CREATE [ OR REPLACE ] [ EDITIONABLE | NONEDITIONAL ] TYPE <type name>
[ <type source creation options> ]
[<type definition>]
[ <type properties> ]

Limitations

Snowflake doesn't support user-defined data types, according to its online documentation Unsupported Data Types, but it supports Semi-structured Data Types, which can be used to mimic the hierarchy-like structure of most User-defined types. For this reason, there are multiple type features that have no workaround.

Following are the User Defined Types features for which NO workaround is proposed:

Subtypes: Type Hierarchy

These statements aren't supported in Snowflake. SnowConvert only recognizes them, but no translation is offered.

CREATE TYPE person_t AS OBJECT (name VARCHAR2(100), ssn NUMBER) 
   NOT FINAL;
/

CREATE TYPE employee_t UNDER person_t 
   (department_id NUMBER, salary NUMBER) 
   NOT FINAL;
/

CREATE TYPE part_time_emp_t UNDER employee_t (num_hrs NUMBER);
/

Type properties

These refer to the options that are normally used when using OOP in PL/SQL: Persistable, Instantiable and Final.

CREATE OR REPLACE TYPE type1 AS OBJECT () NOT FINAL NOT INSTANTIABLE NOT PERSISTABLE;
CREATE OR REPLACE TYPE type2 AS OBJECT () FINAL INSTANTIABLE PERSISTABLE;

Nested Table Type

These statements aren't supported in Snowflake. SnowConvert only recognizes them, but no translation is offered.

CREATE TYPE textdoc_typ AS OBJECT
    ( document_typ      VARCHAR2(32)
    , formatted_doc     BLOB
    ) ;
/

CREATE TYPE textdoc_tab AS TABLE OF textdoc_typ;
/

Type Source Creation Options

These options stand for custom options regarding access and querying the type.

CREATE TYPE type1 FORCE OID 'abc' SHARING = METADATA DEFAULT COLLATION schema1.collation ACCESSIBLE BY (schema1.unitaccesor) AS OBJECT ();
CREATE TYPE type2 FORCE OID 'abc' SHARING = NONE DEFAULT COLLATION collation ACCESSIBLE BY (PROCEDURE unitaccesor) AS OBJECT ();
CREATE TYPE type3 AUTHID CURRENT_USER AS OBJECT ();
CREATE TYPE type4 AUTHID DEFINER AS OBJECT ();

Proposed workarounds

About types definition

For the definition, the proposed workaround is to create semi-structure data type to mimic Oracle's data type.

About types member function

For the member functions containing logic and DML, the proposed workaround relies on helpers to translate this into stored procedures.

Current SnowConvert Support

The next table shows a summary of the current support provided by the SnowConvert tool. Please keep into account that translations may still not be final, and more work may be needed.

Type Statement Element	Current recognition status	Current translation status	Has Known Workarounds
Object Type Definitions	Recognized.	Partially Translated.	Yes.
Subtype Definitions	Recognized.	Not Translated.	No.
Array Type Definitions	Recognized.	Not Translated.	Yes.
Nested Table Definitions	Recognized.	Not Translated.	No.
Member Function Definitions	Recognized.	Not Translated.	Yes.

Known Issues

1. DML usages for Object Types are not being transformed

As of now, only DDL definitions that use User-Defined Types are being transformed into Variant. This means that any Inserts, Updates or Deletes using User-defined Types are not being transformed and need to be manually transformed. There is no EWI for this but there is a work item to add this corresponding EWI.

2. 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-FDM-0015: ​Referenced custom type in query not found.

2. SSC-EWI-0056: Custom Types Not Supported.

3. SSC-FDM-OR0039: Create Type Variant Not Supported.

4. SSC-EWI-0062: Custom type usage changed to variant.

5. SSC-EWI-0063: ​Added statements because object used a custom type.

6. SSC-FDM-OR0024: Columns from expression not found.

7. SSC-EWI-OR0082: Cannot Convert Nested Type Attribute Expression.