By Bill Ramos and Badal Bordia, Advaiya Inc.
This blog post describes why SQL Server Migration Assistant (SSMA) for Oracle doesn’t support the conversion of some of the collection method
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. You can use the methods EXISTS, COUNT, LIMIT, FIRST, LAST, PRIOR, NEXT, EXTEND, TRIM, and DELETE to manage collections in Oracle whose size is unknown or varies.
Error O2SS0351 Conversion of collection method not supported
Whenever you make a reference of Limit method in collection query, SSMA is not able to resolve that method. Hence, usage of this collection method results in error O2SS0351 In Oracle, LIMIT returns the maximum number of elements that array can contain (which you must specify in its type definition).
Consider the below example:
TYPE VARRAYTEST IS VARRAY(5) OF VARCHAR2(25);
vTest := VARRAYTEST(‘text1’, ‘text2’, ‘text3’);
DBMS_OUTPUT.PUT_LINE(‘COLLECTION LIMIT IS — ‘ || vTest.LIMIT);
When SSMA tries to convert the above code, it gives the error “Error O2SS0351 Conversion of collection method not supported”.
The solution of the above error is to modify the code in SQL Server because LIMIT method is not relevant in the way SSMA emulates the collection. When SSMA converts the array collection element of Oracle to SQL Server, it allocates the maximum size to the collection element and SQL server allows dynamic allocation to the collection at the run time.
So for the above example, we have replaced the Limit method with Count method of SQL server to get the number of elements in collection
@CollectionIndexInt$TYPE varchar(max) = ‘ VARRAY OF STRING’,
SET @vTest = dbo.CollectionIndexInt
PRINT ‘COLLECTION LIMIT IS — ‘ + ISNULL(CAST(@vTest.Count AS
There are several other errors related to “Collection conversion“ that you may encounter. These include the following:
· Error O2SS0316: Conversion of collection element not supported
· Error O2SS0323: Conversion of collection method not supported
For more information, check out the following references: