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.

Error O2SS0339 Cannot Convert Standalone User-Defined Types

Background

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”

Possible Remedies

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

  g_Text  Tt_Text;

END PKG_UDTVAR_TEST;

 

CREATE OR REPLACE PACKAGE BODY PKG_UDTVAR_TEST IS

BEGIN

  g_Text:=Tt_Text();

END PKG_UDTVAR_TEST;

 

 

clip_image001

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.

CREATE OR REPLACE PACKAGE PKG_UDTVAR_TEST IS

TYPE TO_TEXT IS RECORD (Text_Id NUMBER, Line NUMBER, Text VARCHAR2(2000));

TYPE TT_TEXT IS TABLE OF TO_TEXT;

      g_Text  Tt_Text;

END PKG_UDTVAR_TEST;

/

 

CREATE OR REPLACE PACKAGE BODY PKG_UDTVAR_TEST IS

BEGIN

  g_Text:=Tt_Text();

END PKG_UDTVAR_TEST;

/

Corresponding SQL Server code generated by SSMA

CREATE PROCEDURE dbo.PKG_UDTVAR_TEST$SSMA_Initialize_Package

AS

   DECLARE

     @CollectionIndexInt$TYPE varchar(max) = ' TABLE OF ( RECORD ( TEXT_ID DOUBLE , LINE DOUBLE , TEXT STRING ) )'

   EXECUTE sysdb.ssma_oracle.db_clean_storage

   DECLARE

      @g_Text dbo.CollectionIndexInt

   EXECUTE sysdb.ssma_oracle.set_pv_collection_int

      'PROSEWARE',

      'DBO',

      'PKG_UDTVAR_TEST',

      'G_TEXT',

      @g_Text

   DECLARE

      @temp dbo.CollectionIndexInt

      SET @temp = dbo.CollectionIndexInt ::[Null].SetType(@CollectionIndexInt$TYPE)

      EXECUTE sysdb.ssma_oracle.set_pv_collection_int

      'PROSEWARE',

      'DBO',

      'PKG_UDTVAR_TEST',

      'G_TEXT',

      @temp

GO

Related Errors

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

References

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