Oracle user-defined types

With AWS DMS, you can migrate Oracle user-defined types (UDTs) to compatible AWS database services. Oracle UDTs are custom data types that extend the built-in scalar data types, allowing you to store complex data structures, such as objects and collections.

Feature compatibility	AWS SCT / AWS DMS automation level	AWS SCT action code index	Key differences
		User-Defined Types	Aurora MySQL doesn’t support user-defined types.

Oracle usage

Oracle refers to user-defined types (UDTs) as OBJECT TYPES. These types are managed using PL/SQL. User-defined types enable the creation of application-dedicated, complex data types that are based on, and extend, the built-in Oracle data types.

The CREATE TYPE statement supports creation of the following types:

• Objects types

• Varying array or varray types

• Nested table types

• Incomplete types

• Additional types such as an SQLJ object type, which is a Java class mapped to SQL user-defined type

Examples

Create an Oracle Object Type to store an employee phone number.

CREATE OR REPLACE TYPE EMP_PHONE_NUM AS OBJECT (
  PHONE_NUM VARCHAR2(11));

CREATE TABLE EMPLOYEES (
  EMP_ID NUMBER PRIMARY KEY,
  EMP_PHONE EMP_PHONE_NUM NOT NULL);

INSERT INTO EMPLOYEES VALUES(1, EMP_PHONE_NUM('111-222-333'));
SELECT a.EMP_ID, a.EMP_PHONE.PHONE_NUM FROM EMPLOYEES a;

EMP_ID  EMP_PHONE.P
1       111-222-333

Create an Oracle object type as a collection of attributes for the employees table.

CREATE OR REPLACE TYPE EMP_ADDRESS AS OBJECT (
  STATE VARCHAR2(2),
  CITY VARCHAR2(20),
  STREET VARCHAR2(20),
  ZIP_CODE NUMBER);

CREATE TABLE EMPLOYEES (
  EMP_ID NUMBER PRIMARY KEY,
  EMP_NAME VARCHAR2(10) NOT NULL,
  EMP_ADDRESS EMP_ADDRESS NOT NULL);

INSERT INTO EMPLOYEES VALUES(1, 'John Smith',
  EMP_ADDRESS('AL', 'Gulf Shores', '3033 Joyce Street', '36542'));

SELECT a.EMP_ID, a.EMP_NAME, a.EMP_ADDRESS.STATE,
  a.EMP_ADDRESS.CITY, a.EMP_ADDRESS.STREET, a.EMP_ADDRESS.ZIP_CODE
  FROM EMPLOYEES a;

EMP_ID  EMP_NAME    STATE  CITY         STREET             ZIP_CODE
1       John Smith  AL     Gulf Shores  3033 Joyce Street  36542

For more information, see CREATE TYPE and CREATE TYPE BODY in the Oracle documentation.

MySQL usage

Currently, Amazon Aurora MySQL doesn’t provide a directly comparable alternative for user-defined types.