By Bill Ramos and Vishal Soni, Advaiya Inc.

This blog post covers the reasons why SQL Server Migration Assistant (SSMA) for Oracle does not support Collection or Records Type. A collection is an ordered group of elements, all of the same type. It is a general concept that encompasses lists, arrays, and other familiar data types. Each element has a unique subscript that determines its position in the collection.

Error O2SS0408 Collection or Record Type is not supported

Background

Whenever you make a reference of a user defined data type in collection query, SSMA is not able to resolve that data type. Hence, when you try to insert the values in table, SSMA generates an error. SSMA provides direct emulation of only the Integer and String data type and rest of the things are treated as Records.

Possible Remedies

Consider the below example:

CREATE OR REPLACE TYPE TEST_UDT AS OBJECT (ATTR_1 NUMBER);

/

 

DECLARE

      TYPE COLLECTION_TEST IS TABLE OF TEST_UDT;

      ColTest COLLECTION_TEST;

BEGIN

      ColTest := COLLECTION_TEST(TEST_UDT(10), TEST_UDT(20), TEST_UDT(30));

      DBMS_OUTPUT.PUT_LINE('COLLECTION COUNT IS -- ' || ColTest.COUNT);

END;

When SSMA tries to convert the above code, it does not resolve the operations to be performed on the user defined datatype and hence generates “Error O2SS0408 Collection or Record Type is not supported”.

Error_O2SS0408_01

 

The solution of the above error is to rewrite the code in Oracle. Instead of creating a user defined data type separately, just declare a data type as a record of INTEGER data type and make its reference while assigning the values.

DECLARE

TYPE TEST_UDT IS RECORD (ATTR_1 NUMBER);

TYPE COLLECTION_TEST IS TABLE OF TEST_UDT Index By Binary_Integer;

ColTest COLLECTION_TEST;

BEGIN

ColTest(1).ATTR_1 := 10;

ColTest(2).ATTR_1 := 20;

ColTest(3).ATTR_1 := 30;

DBMS_OUTPUT.PUT_LINE('COLLECTION COUNT IS -- ' || ColTest.COUNT);

END;

Corresponding SQL Server code generated by SSMA

BEGIN

   DECLARE

      @CollectionIndexInt$TYPE varchar(max) = ' TABLE INDEX BY INT OF ( RECORD ( ATTR_1 DOUBLE ) )'

   DECLARE

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

 

   SET @ColTest = @ColTest.SetRecord(1, @colTest.GetOrCreateRecord(1).SetInt(N'ATTR_1', 10))

 

   SET @ColTest = @ColTest.SetRecord(2, @colTest.GetOrCreateRecord(2).SetInt(N'ATTR_1', 20))

 

   SET @ColTest = @ColTest.SetRecord(3, @colTest.GetOrCreateRecord(3).SetInt(N'ATTR_1', 30))

 

   PRINT 'COLLECTION COUNT IS -- ' + ISNULL(CAST(@ColTest.Count AS nvarchar(max)), '')

 

END

GO

Related Errors

There are several other errors related to “Collection and Records” that you may encounter. These include the following:

·         Emulating Records and Collections using CLR UDTs

·         Error O2SS0407 Unsupported collection key type

References

For more information, check out the following references:

·         Migrating Oracle to SQL Server 2008 White Paper