By Bill Ramos and Ankit Matta, Advaiya Inc.
This blog post covers the reasons why SQL Server Migration Assistant (SSMA) for Oracle Cannot Convert Standalone User-Defined Types.
A User Defined Type (UDT) is an entitled data type that is made in the database by the user. A UDT can be a distinct type which segments a common representation with a built-in data type.
Whenever you create a User Defined data type and make a reference of that data type in a packaged query to declare a variable, SSMA does not recognize this data type and hence does not convert it to corresponding SQL Server code. This is because the user defined data type is not defined in the scope of the code where the variable is being declared and hence generates error “Error O2SS0339 Cannot Convert Usage of Standalone User-Defined Types”
Consider the below example, where we have created two different UDTs and then created a package specification followed by a package body. These UDTs are used to declare variables in the package code.
CREATE OR REPLACE TYPE TO_TEXT AS OBJECT(Text_Id NUMBER, Line NUMBER, Text VARCHAR2(2000))
CREATE OR REPLACE TYPE TT_TEXT AS TABLE OF To_Text
CREATE OR REPLACE PACKAGE PKG_UDTVAR_TEST IS
CREATE OR REPLACE PACKAGE BODY PKG_UDTVAR_TEST IS
The solution of the above error is to rewrite the code in Oracle. Just create the user defined data types inside the package specification keeping the remaining code as is.
When you define the UDTs inside the package specification, the UDTs comes in the scope of the Package and hence the error is resolved.
TYPE TO_TEXT IS RECORD (Text_Id NUMBER, Line NUMBER, Text VARCHAR2(2000));
TYPE TT_TEXT IS TABLE OF TO_TEXT;
CREATE PROCEDURE dbo.PKG_UDTVAR_TEST$SSMA_Initialize_Package
@CollectionIndexInt$TYPE varchar(max) = ' TABLE OF ( RECORD ( TEXT_ID DOUBLE , LINE DOUBLE , TEXT STRING ) )'
SET @temp = dbo.CollectionIndexInt ::[Null].SetType(@CollectionIndexInt$TYPE)
There are several other errors related to “User defined Types” that you may encounter. These include the following:
· Emulating Records and Collections using CLR UDTs
For more information, check out the following references:
Migrating Oracle to SQL Server 2008 White Paper
SSMA Blog: Converting Oracle UDT to SQL Server TVP