Converting Oracle UDT to SQL Server TVP

Oracle supports User Defined Type (UDT) which include object type and collection. This data type is not supported in SQL Server and SSMA does not support conversion of Oracle UDT. You may consider using SQL Server TVP when migrating your Oracle database to SQL Server.

The following provides use scenarios of UDT and examples on how to recreate the statements in SQL Server:

Object table is created out of schema definition of the user defined table type

PL/SQL

T-SQL

CREATE TABLE obtblperson OF person_ot;

DECLARE @person_ot person_ot

SELECT * INTO obtblperson FROM @person_ot

Oracle table column with user defined type is converted into a seperate table. For object type, the main table column is converted into uniqueidentifier column with foreign key relationship to the sub table. For collection, the sub table is created with a foreign key column referring to the primary key of the main table.