Migrating Oracle to SQL Server using SSMA - Error O2SS0407: Unsupported collection key type

Article
05/19/2011

By Bill Ramos and Vishal Soni, Advaiya Inc.

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 IS TABLE OF NUMBER INDEX BY DECIMAL;

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 IS TABLE OF NUMBER INDEX BY INTEGER;

money_index number_colln_dec_index;

BEGIN

END;