Share via

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

  • Article

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;

 

Error_O2SS0407_problem

 

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;

 

Error_O2SS0407_solution

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. 

· Error O2SS0334 - BULK COLLECT INTO Clause not converted

· Error O2SS0352 - SELECT statements with BULK COLLECT INTO clause and DISTINCT are not supported

References

For more information, check out the following references.

· Migrating Oracle to SQL Server 2008 White Paper

· Oracle Database PL/SQL Users Guide and Reference - Using Collection Methods