Migrating Oracle to SQL Server using SSMA - Error O2SS0352 BULK COLLECT INTO clause in SELECT statement not converted
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”
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.
Related Errors
There are several other errors related to “Collection” that you may encounter. These include the following:
- Error O2SS0407 - Unsupported collection key type
- Error O2SS0351 - The conversion of collection method '{0}' is not supported.
- Error O2SS0334 BULK COLLECT INTO Clause not converted
References
For more information, check out the following references.
- Migrating Oracle to SQL Server 2008 White Paper
- Oracle Database PL/SQL Users Guide and Reference - Using Collection Methods