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.
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.
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
DECLARE TYPE tbl_type_DNAME IS TABLE OF varchar2(14);
SELECT DISTINCT DNAME
BULK COLLECT INTO var_dname
IF var_dname.COUNT = 0 THEN
FOR i IN var_dname.FIRST..var_dname.LAST LOOP
DBMS_OUTPUT.PUT_LINE(' DEPT NAME #' || var_dname(i));
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”
The solution to convert the above PL/SQL block is to replace DISTINCT clause with UNIQUE clause (a synonym of DISTINCT) as shown below:
SELECT UNIQUE DNAME
SSMA will now convert the procedure without any issues as shown below.
There are several other errors related to “Collection” that you may encounter. These include the following:
For more information, check out the following references.