Migrating Oracle to SQL Server using SSMA - Error O2SS0334 BULK COLLECT INTO Clause not converted

  • Article

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.

Error O2SS0334 BULK COLLECT INTO Clause not converted Background

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.

Possible Remedies

Consider the following example of “dept” table having two columns, i.e. deptno and dname in it and the SELECT statement uses an asterisk:

DECLARE

      TYPE test_deptno IS TABLE OF number;

      var_deptno test_deptno;

BEGIN

  SELECT * BULK COLLECT INTO var_deptno

  FROM dept;

END;

Error_O2SS0334_01

 

 

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:

 

DECLARE

      TYPE test_deptno IS TABLE OF number;

      TYPE test_dname IS TABLE OF varchar2(20);

      var_deptno test_deptno;

      var_dname test_dname;

BEGIN

  SELECT deptno, dname BULK COLLECT INTO var_deptno, var_dname

  FROM dept;

END;

Error_O2SS0334_02

 

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

References

For more information, check out the following references.