Edit

O2SS0352: BULK COLLECT INTO clause in SELECT statement not converted (Error)

  • Article
  • 2 minutes to read

This article covers why SQL Server Migration Assistant (SSMA) for Oracle does not convert the PL/SQL blocks when SELECT statement is used with BULK COLLECT INTO clause and DISTINCT clause.

Background

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 DISTINCT clause specifies that only unique value can appear in the result set. 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 a migration path for Oracle collections. However, if the SELECT statement used to populate the collection uses a DISTINCT clause, SSMA generates the error message.

Example

Consider the below example in which SELECT statement is used with DISTINCT clause and BULK COLLECT INTO clause:

CREATE OR REPLACE PROCEDURE BULK_COLLECT_WITH_DISTINCT
AS
BEGIN
    DECLARE
        TYPE tbl_type_DNAME IS TABLE OF varchar2(14);
        var_dname tbl_type_DNAME;
    BEGIN
        SELECT DISTINCT DNAME
        BULK COLLECT INTO var_dname
        FROM dept;

        IF var_dname.COUNT = 0 THEN
            DBMS_OUTPUT.PUT_LINE('No results!');
        ELSE
            DBMS_OUTPUT.PUT_LINE('Results:');

            FOR i IN var_dname.FIRST..var_dname.LAST LOOP
                DBMS_OUTPUT.PUT_LINE(' DEPT NAME #' || var_dname(i));
            END LOOP;
        END IF;
    END;
END;

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

O2SS0352: BULK COLLECT INTO clause in SELECT statement not converted

Possible remedies

To convert the above PL/SQL block we need to replace DISTINCT clause with UNIQUE clause (a synonym of DISTINCT) as shown below:

CREATE OR REPLACE PROCEDURE BULK_COLLECT_WITH_DISTINCT
AS
BEGIN
    DECLARE
        TYPE tbl_type_DNAME IS TABLE OF varchar2(14);
        var_dname tbl_type_DNAME;
    BEGIN
        SELECT UNIQUE DNAME
        BULK COLLECT INTO var_dname
        FROM dept;

        IF var_dname.COUNT = 0 THEN
            DBMS_OUTPUT.PUT_LINE('No results!');
        ELSE
            DBMS_OUTPUT.PUT_LINE('Results:');

            FOR i IN var_dname.FIRST..var_dname.LAST LOOP
                DBMS_OUTPUT.PUT_LINE(' DEPT NAME #' || var_dname(i));
            END LOOP;
        END IF;
    END;

END;