By Bill Ramos and Mayank Bhanawat Advaiya Inc.

This blog post covers why SQL Server Migration Assistant (SSMA) for Oracle doesn’t convert the PL/SQL blocks when SELECT statement is used with BULK COLLECT INTO clause and DISTINCT clause.

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.

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 O2SS0352 BULK COLLECT INTO clause in SELECT statement not converted

Background

SSMA provides for a migration path for Oracle collections. However, if the SELECT statement used to populate the collection uses a DISTINCT clause, SSMA generates the O2SS0352 error.

Possible Remedies

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 SSMA tries to convert the above PL/SQL block, it gives following error:

“O2SS0352: SQL Server Migration Assistant for Oracle Error message: BULK COLLECT INTO clause in SELECT statement not converted”

Error_O2SS0352_1

The solution to convert the above PL/SQL block is 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;

SSMA will now convert the procedure without any issues as shown below.

Error_O2SS0352_2

Related Errors

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.