Edit

O2SS0408: Collection or record type is not supported (Error)

  • Article

This article covers the reasons why SQL Server Migration Assistant (SSMA) for Oracle does not support collection or record types.

Background

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.

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 the table, SSMA generates an error message. SSMA provides direct emulation of only the integer and string data types and rest of the things are treated as records.

Example

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 you try to convert the above code in SSMA, it generates the following error message:

O2SS0408: Collection or record type is not supported

Possible remedies

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