By Bill Ramos and Ankit Matta, Advaiya Inc.
This blog post covers some of the reasons why SQL Server Migration Assistant (SSMA) for Oracle cannot convert a statement that contains the BULK COLLECT INTO Clause (a collection category query).
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. The BULK COLLECT INTO clause binds the output of the query to the collection, resulting in less communication between the PL/SQL and SQL engines.
Before embarking on the migration of Oracle collections, you should consider the four migration options mentioned in the Migrating Oracle to SQL Server 2008 White Paper.
SSMA provides for a migration path for Oracle collections. However, when the list count doesn’t match the BULK COLLECT INTO collection set, SSMA is unable to make the conversion and will generate error O2SS0334.
Consider the following example of “dept” table having two columns, i.e. deptno and dname in it and the SELECT statement uses an asterisk:
TYPE test_deptno IS TABLE OF number;
SELECT * BULK COLLECT INTO var_deptno
The solution is to equalize the number of columns called in the SELECT statement with the number of variables used in BULK COLLECT INTO clause. In the example, the asterisk will expand and call two columns from the dept table whereas there is only one variable in the BULK COLLECT INTO clause.
To successfully migrate the example, fetch only the deptno column from the dept table, or add another variable in BULK COLLECT INTO clause, depending on your requirement. The example below shows how to modify query along with the screenshot of the converted query:
TYPE test_dname IS TABLE OF varchar2(20);
SELECT deptno, dname BULK COLLECT INTO var_deptno, var_dname
There are several other errors related to “Collection” that you may encounter. These include the following:
For more information, check out the following references.