Edit

Share via

O2SS0334: BULK COLLECT INTO clause not converted (Error)

This article covers some of the reasons why SQL Server Migration Assistant (SSMA) for Oracle can't convert a statement that contains the BULK COLLECT INTO clause (a collection category query).

Background

A collection is an ordered group of elements, all of the same type. It's 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.

SSMA provides path for a migration of Oracle collections, however, when the list count doesn't match the BULK COLLECT INTO collection set, SSMA is unable to make the conversion and generates an error message.

Example

In the following example, the dept table has two columns (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;

When you try to convert the previous code in SSMA, it generates the following error message:

O2SS0334: BULK COLLECT INTO clause not converted

Possible remedies

The solution is to normalize the number of columns listed in the SELECT clause with the number of variables used in BULK COLLECT INTO clause. In the example, the asterisk expands into two columns from the dept table whereas there's 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 following example adds a variable into BULK COLLECT INTO clause:

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;