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
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.
Consider the below example:
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”.
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.
Corresponding SQL Server code generated by SSMA
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
For more information, check out the following references: