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

 

clip_image001

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:

·         Migrating Oracle to SQL Server 2008 White Paper