Create Type
This is a translation reference to convert Oracle Create Type Statements (UDT's) to snowflake
Last updated
This is a translation reference to convert Oracle Create Type Statements (UDT's) to snowflake
Last updated
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
Snowflake doesn't support user-defined data types, according to its online documentation , but it supports , 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:
These statements aren't supported in Snowflake. SnowConvert only recognizes them, but no translation is offered.
These refer to the options that are normally used when using OOP in PL/SQL: Persistable, Instantiable and Final.
These statements aren't supported in Snowflake. SnowConvert only recognizes them, but no translation is offered.
These options stand for custom options regarding access and querying the type.
For the definition, the proposed workaround is to create semi-structure data type to mimic Oracle's data type.
For the member functions containing logic and DML, the proposed workaround relies on helpers to translate this into stored procedures.
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.
Recognized.
Partially Translated.
Yes.
Recognized.
Not Translated.
No.
Recognized.
Not Translated.
Yes.
Recognized.
Not Translated.
No.
Recognized.
Not Translated.
Yes.
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.
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.
SSC-FDM-OR0039: Create Type Variant Not Supported.
: ​Referenced custom type in query not found.
: Custom Types Not Supported.
: Custom type usage changed to variant.
: ​Added statements because object used a custom type.
: Columns from expression not found.
: Cannot Convert Nested Type Attribute Expression.