Migrating Oracle to SQL Server using SSMA - Error O2SS0347 Comparison of record or collection expressions is not supported
By Bill Ramos and Badal Bordia, Advaiya Inc.
This blog post covers the reasons why SQL Server Migration Assistant (SSMA) for Oracle does not support comparison of Records or collection variables.
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.
Error O2SS0347 Comparison of record or collection expressions is not supported
Background
Whenever a PL/SQL block with comparison operator like “=” or “IN” etc. is encountered with operands of type collection or record, “Error O2SS0347 Comparison of record or collection expressions is not supported” is generated.
Possible Remedies
Consider the below example, where we are comparing two collections by using “=” operator:
DECLARE
TYPE SIMPLE_COLLECTION IS TABLE OF INTEGER;
VARCOLL1 SIMPLE_COLLECTION := SIMPLE_COLLECTION(1, 1, 1);
VARCOLL2 SIMPLE_COLLECTION := SIMPLE_COLLECTION(1, 1, 1);
BEGIN
IF VARCOLL1 = VARCOLL2 THEN
DBMS_OUTPUT.PUT_LINE(1);
ELSE
DBMS_OUTPUT.PUT_LINE(0);
END IF;
END;
When SSMA tries to convert the above code, it does not resolve the operator and hence generates error “Error O2SS0347 Comparison of record or collection expressions is not supported”
The solution of the above error is to rewrite the code in Oracle. Unlike Oracle, Microsoft SQL Server supports neither records nor collections and its associated operations on them. So, when you migrate from Oracle to SQL Server using SSMA, you must apply substantial transformations to the PL/SQL code. Below is the rewritten Oracle code:
DECLARE
TYPE SIMPLE_COLLECTION IS TABLE OF INTEGER;
VARCOLL1 SIMPLE_COLLECTION := SIMPLE_COLLECTION(1, 2,3);
VARCOLL2 SIMPLE_COLLECTION := SIMPLE_COLLECTION(1, 2,3);
j PLS_INTEGER := 0;
BEGIN
if VARCOLL1.count=VARCOLL2.count then
FOR i IN 1..VARCOLL1.count
LOOP
IF VARCOLL1(i) = VARCOLL2(i) THEN
j:=1;
else
j:=0;
DBMS_OUTPUT.PUT_LINE(0);
Exit;
End if;
END LOOP;
If j=1 then
DBMS_OUTPUT.PUT_LINE(1);
End if;
Else
DBMS_OUTPUT.PUT_LINE(0);
End if;
END;
Corresponding SQL Server Code:
BEGIN
DECLARE
@CollectionIndexInt$TYPE varchar(max) = ' TABLE OF INT'
DECLARE
@VARCOLL1 dbo.CollectionIndexInt = dbo.CollectionIndexInt ::[Null].SetType(@CollectionIndexInt$TYPE).AddInt(1).AddInt(2).AddInt(3),
@VARCOLL2 dbo.CollectionIndexInt = dbo.CollectionIndexInt ::[Null].SetType(@CollectionIndexInt$TYPE).AddInt(1).AddInt(2).AddInt(3),
@j int = 0
IF @VARCOLL1.Count = @VARCOLL2.Count
BEGIN
BEGIN
DECLARE
@i int
SET @i = 1
DECLARE
@loop$bound int
SET @loop$bound = @VARCOLL1.Count
WHILE @i <= @loop$bound
BEGIN
IF @VARCOLL1.GetInt(@i) = @VARCOLL2.GetInt(@i)
SET @j = 1
ELSE
BEGIN
SET @j = 0
PRINT 0
BREAK
END
SET @i = @i + 1
END
END
IF @j = 1
PRINT 1
END
ELSE
PRINT 0
END
GO
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 O2SS0408 Collection or record type is not supported
· Error O2SS0352 BULK COLLECT INTO clause in SELECT statement not converted
References
For more information, check out the following references: