This blog post covers some of the reasons why SQL Server Migration Assistant (SSMA) for Oracle cannot convert some of the statements that have the collection or records data-type. This blog also covers the scenario where SSMA identifies unused code in an Oracle database.

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. When using a nested type collection, the key can either be int (default) or string type. Any other type is not supported for index association, and gives the error.

Error O2SS0407: Unsupported collection key type

Background

SSMA provides for a migration path for Oracle collections. However, SSMA do not support the collection key besides integer and string. So when SSMA encounters any collection variable with index key other than integer and string, it generates the error O2SS0407.

Possible Remedies

Consider the following example of having number_colln_dec_index, which is a user defined type indexed by DECIMAL. With this collection type, an object, money_index is created.

DECLARE

TYPE number_colln_dec_index ISTABLEOFNUMBERINDEXBYDECIMAL;

money_index number_colln_dec_index;

BEGIN

END;

The general solution for this kind of problem is to replace the index key with either integer or any supported string type, like Varchar2. For our example, since the table is of type Number, replacing the Index key DECIMAL with INTEGER would allow SSMA to convert the schema successfully.

DECLARE

TYPE number_colln_dec_index ISTABLEOFNUMBERINDEXBYINTEGER;

money_index number_colln_dec_index;

BEGIN

END;

Related Errors

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

·Emulating Oracle Collections using SSMA

·Error O2SS0351 – The conversion of collection method ‘{0}’ is not supported.