User-Defined Types

Description

User-defined data types use Oracle built-in data types and other user-defined data types as the building blocks of object types that model the structure and behavior of data in applications. The sections that follow describe the various categories of user-defined types. (Oracle SQL Language Reference User-defined Data Types)

Snowflake does not have any support for User-defined Types. This page is meant to be a summary of Oracle's features. For the current status of User-defined Types in the SnowConvert tool please refer to the Create Type Statement Page and its subpages.

Object Types

SnowConvert offers partial translation for Object Types, for more information on this, please refer to the next section:

Object Type Definition

REF Data Types

Ref Data Types are not recognized by SnowConvert, and are instead shown as unrecognized "User-defined Functions". For more information about them, please read the REF Data Types subpage.

An object identifier (represented by the keyword OID) uniquely identifies an object and enables you to reference the object from other objects or from relational tables. A data type category called REF represents such references. A REF data type is a container for an object identifier. REF values are pointers to objects. (Oracle SQL Language Reference REF Data Types)

REF Data Types

Varrays

SnowConvert only recognizes these elements but does not offer any translation for them, for more information on this, please refer to the next section:

Array Type Definition

Nested Tables

SnowConvert only recognizes these elements but does not offer any translation for them since there are no known workarounds for them, for more information on this, please refer to the next section:

Nested Table Type Definition

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. Nested Table types are not being transformed

There is no known workaround for implementing Nested Tables, for this reason SnowConvert only offers recognition of these elements.

3. Array types are not being transformed

For now SnowConvert only recognizes these elements. A known workaround exists and there is a work item to implement them.

4. REF Data Types are not supported by SnowConvert, but there is no EWI related to them

They are not supported, and instead are reported as an unknown User-Defined Function, but there is a work item to add this corresponding EWI.

Related EWIs

1. MSCEWI1049: Not recognized function.
2. SSC-FDM-0015: Data Type was Not Recognized.
3. MSCEWI1056: Custom Types Not Supported.
4. MSCEWI1057: Create Type Variant Not Supported.
5. ​MSCEWI1062​: Custom type usage changed to variant.
6. ​MSCEWI1063: ​Added statements because object used a custom type.
7. ​MSCEWI1064: ​Referenced custom type in query not found.
8. ​MSCEWI1065​: Columns from expression not found.
9. ​MSCEWI3082​: Cannot Convert Nested Type Attribute Expression.