Emulating Records and Collections via CLR UDT

        By Bill Ramos and Vishal Soni, Advaiya Inc.

This blog covers how the SQL Server Migration Assistant (SSMA) for Oracle uses the SQL Server Common Language Runtime (CLR) User-Defined Types (UDT) for emulating Oracle Records and Collections. 

In this blog, we will cover the following topics related to CLR UDTs:

o Declaring Record or Collection Types and Variables

o Constructor Calls Conversions

o Referencing and Assigning Record and Collection Elements

o Collection Built-in Methods

o Project Settings for Records

Note: The content for this blog is extracted and refined from the Migrating Oracle to SQL Server 2008 White Paper

Declaring Record or Collection Types and Variables

SSMA creates three CLR-based UDTs:

· CollectionIndexInt

· CollectionIndexString

· Record

The CollectionIndexInt type is intended for emulating collections indexed by integer, such as VARRAYs, nested tables, and integer key based associative arrays. The CollectionIndexString type is used for associative arrays indexed by character keys. The Oracle record functionality is emulated by the Record type.

All declarations of the record or collection types are converted to this Transact-SQL declaration:

declare @Collection$TYPE varchar(max) = ’<type definition>’ 

Here <type definition> is a descriptive text uniquely identifying the source PL/SQL type.

Consider the following example:

Oracle

Declare

TYPE Manager IS RECORD (mgrid integer, mgrname varchar2(40), hiredate date);

TYPE Manager_table is TABLE OF Manager INDEX BY PLS_INTEGER;

 

Mgr_rec Manager;

Mgr_table_rec Manager_table;

Begin

     mgr_rec.mgrid := 1;

     mgr_rec.mgrname := 'Mike';

     mgr_rec.hiredate := sysdate;

 

     select empno, ename, hiredate

     BULK COLLECT INTO mgr_table_rec

     from emp;

End;

 

SQL Server

BEGIN

   DECLARE

      @CollectionIndexInt$TYPE varchar(max) = ' TABLE INDEX BY INT OF ( RECORD ( MGRID INT , MGRNAME STRING , HIREDATE DATETIME ) )'

 

   DECLARE

      @Mgr_rec$mgrid int,

      @Mgr_rec$mgrname varchar(40),

      @Mgr_rec$hiredate datetime2(0),

      @Mgr_table_rec dbo.CollectionIndexInt = dbo.CollectionIndexInt ::[Null].SetType(@CollectionIndexInt$TYPE)

 

   SET @mgr_rec$mgrid = 1

   SET @mgr_rec$mgrname = 'Mike'

   SET @mgr_rec$hiredate = sysdatetime()

   SET @mgr_table_rec = @mgr_table_rec.RemoveAll()

   SET @mgr_table_rec = @mgr_table_rec.AssignData(sysdb.ssma_oracle.fn_bulk_collect2CollectionComplex(

      (

         SELECT CAST(EMP.EMPNO AS int) AS mgrid, EMP.ENAME AS mgrname, EMP.HIREDATE AS hiredate

         FROM dbo.EMP

         FOR XML PATH

      )))

END

GO

 

Here, since the Manager table is associated with a numeric index (INDEX BY PLS_INTEGER), the corresponding T-SQL declaration used is of type @CollectionIndexInt$TYPE.

 

Oracle

TYPE Manager_table is TABLE OF Manager INDEX BY PLS_INTEGER;

SQL

@CollectionIndexInt$TYPE varchar(max) = ' TABLE INDEX BY INT OF ( RECORD ( MGRID INT , MGRNAME STRING , HIREDATE DATETIME ) )'

 

If the table was associated with a character set index, like VARCHAR2, the corresponding T-SQL declaration would be of type @CollectionIndexString$TYPE.

 

Oracle:

TYPE Manager_table is TABLE OF Manager INDEX BY VARCHAR2(40);

SQL:

@CollectionIndexString$TYPE varchar(max) = ' TABLE INDEX BY STRING OF ( RECORD ( MGRID INT , MGRNAME STRING , HIREDATE DATETIME ) )'

 

The Oracle record functionality is emulated by the Record type only.

Each of the types, CollectionIndexInt, CollectionIndexString, and Record, has a static property [Null] returning an empty instance. The SetType method is called to receive an empty object of a specific type (as seen in the above example).

Constructor Calls Conversions

Constructor notation can be used only for nested tables and VARRAYs, so all the explicit constructor calls are converted using the CollectionIndexInt type. Empty constructor calls are converted via SetType call invoked on null instance of CollectionIndexInt. The [Null] property returns the null instance. If the constructor contains a list of elements, special method calls are applied sequentially to add the value to the collection.

For example:

Oracle

DECLARE

   TYPE nested_type IS TABLE OF VARCHAR2(20);

   TYPE varray_type IS VARRAY(5) OF INTEGER;

   v1 nested_type;

   v2 varray_type;

BEGIN

   v1 := nested_type('Arbitrary','number','of','strings');

   v2 := varray_type(10, 20, 40, 80, 160);

END;

SQL Server

 

BEGIN

 

   DECLARE

      @CollectionIndexInt$TYPE varchar(max) = ' VARRAY OF INT',

      @CollectionIndexInt$TYPE$2 varchar(max) = ' TABLE OF STRING',

      @v1 dbo.CollectionIndexInt,

      @v2 dbo.CollectionIndexInt

 

   SET @v1 = dbo.CollectionIndexInt ::[Null].SetType(@CollectionIndexInt$TYPE$2).AddString('Arbitrary').AddString('number').AddString('of').AddString('strings')

 

   SET @v2 = dbo.CollectionIndexInt ::[Null].SetType(@CollectionIndexInt$TYPE).AddInt(10).AddInt(20).AddInt(40).AddInt(80).AddInt(160)

 

END

GO

Referencing and Assigning Record and Collection Elements

Each of the UDTs has a set of methods working with elements of the various data types. For a detailed list of all the methods, please refer Migrating Oracle to SQL Server 2008 White Paper (‘Migrating Oracle Records and Collections’ section).

Collection Built-in Methods

A detailed list of various built-in collection methods is available in the Migrating Oracle to SQL Server 2008 White Paper (‘Migrating Oracle Records and Collections’ section).

BULK COLLECT operation

SSMA converts BULK COLLECT INTO statements into SQL Server SELECT … FOR XML PATH statement, whose result is wrapped into one of the following functions:

sysdb.ssma_oracle.fn_bulk_collect2CollectionSimple sysdb.ssma_oracle.fn_bulk_collect2CollectionComplex

 

The choice depends on the type of the target object. These functions return XML values that can be parsed by the CollectionIndexInt, CollectionIndexString, and Record types. A special AssignData function assigns XML-based collection to the UDT.

SSMA recognizes three kinds of BULK COLLECT INTO statements:

1. The collection containing elements with scalar types, and the SELECT list containing one column:

Oracle

SELECT column_name_1

  BULK COLLECT INTO <collection_name_1> FROM <data_source>

SQL Server

SET @<collection_name_1> = @<collection_name_1>.AssignData(sysdb.ssma_oracle.fn_bulk_
collect2CollectionSimple((select column_name_1 from <data_source> for xml path)))

 

For example:

Oracle Code :

select empno, ename, hiredate

     BULK COLLECT INTO mgr_table_rec

 

Results into the following T_SQL statements:

SQL Code:

SELECT CAST(EMP.EMPNO AS int) AS mgrid, EMP.ENAME AS mgrname, EMP.HIREDATE AS hiredate

      FROM dbo.EMP

FOR XML PATH

 

2. The collection containing elements with record types, and the SELECT list containing one column:

Oracle

SELECT column_name_1[, column_name_2...]

  BULK COLLECT INTO <collection_name_1> FROM <data_source>

 

SQL Server

SET @<collection_name_1> = @<collection_name_1>.AssignData(sysdb.ssma_oracle.fn_bulk_
collect2CollectionComplex((select column_name_1 as [collection_name_1_element_field_name_1], column_name_2 as [collection_name_1_element_field_name_2] from <data_source> for xml path)))

 

3. The collection containing elements with scalar type, and the SELECT list containing multiple columns:

Oracle

SELECT column_name_1[, column_name_2 ...]

  BULK COLLECT INTO <collection_name_1>[, <collection_name_2> ...]

  FROM <data_source>

SQL Server:

;with bulkC as (select column_name_1 [collection_name_1_element_field_name_1], column_name_2 [collection_name_1_element_field_name_2] from <data_source>)

select @<collection_name_1> = @<collection_name_1>.AssignData(sysdb.ssma_oracle.fn_bulk_
collect2CollectionSimple((select [collection_name_1_element_field_name_1] from bulkC for xml path))),

@<collection_name_2> = @<collection_name_2>.AssignData(sysdb.ssma_oracle.fn_bulk_
collect2CollectionSimple ((select [collection_name_1_element_field_name_2] from bulkC for xml path)))

Project Settings for Records

When the result of the Oracle query is saved in a PL/SQL record variable, you have two options depending on the SSMA setting for Convert record as a list of separated variables. If the value of this setting is Yes (the default), then SSMA does not create an instance of the Record type. Instead, it splits the record into the constituting fields by creating a separate Transact-SQL variable per each record field. If the setting is No, the record is instantiated and each field is assigned a value using Set methods.

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
• Best practices for deploying SQL CLR assemblies
• SSMA Blog: Converting Oracle UDT to SQL Server TVP

Comments

• Anonymous
  February 14, 2013
  From the white paper: SSMA creates three CLR-based UDTs: • CollectionIndexInt • CollectionIndexString • Record For some reason, unknown to me, these TYPES have not been created when running SSMA and then many of my conversions fail with "Cannot find data type dbo.CollectionIndexInt" How do I get SSMA to create the types or how do I create them myself?

• Anonymous
  February 14, 2013
  Hi Scott, Have you installed the SSMA for Oracle Extension Pack? This is a separate installation than the SSMA executable itself and is the one that will deploy the CLR assemblies and create the CLR based UDTs on the on target SQL Server. This has functionalities to emulate Oracle features not natively supported in SQL server. Please test that out and leave a comment on the results. Angshuman Nayak

• Anonymous
  February 15, 2013
  Thanks Angshuman. Deciding to go a different route. We want a "pure" solution, so we don't have to do any special installations on each target database. Cheers.

• Anonymous
  February 17, 2013
  Hi Scott, This is not a special installation rather it's a part of the SSMA installation itself which unzips into to two distinct exe files. In the download section of SSMA we have explicitly mentioned this.   "This download includes a GUI client based application to manage migration process as well as SSMA extension pack to be installed on target SQL Server. SSMA extension pack includes functionalities to emulate Oracle features not natively supported in SQL server, tester database to support SSMA Testing features, and an application to facilitate direct server-to-server data migration. " So the steps are part of the "pure" solution. We do this so that we provide the same Oracle features that are not natively supported in SQL Server. This helps in porting your application without major changes.   You can find details at : msdn.microsoft.com/.../hh313165.aspx

• Anonymous
  June 14, 2015
  I have used SSMA to convert the oracle collections and records. After migration SQL server is giving me a error message saying "Cannot find data type dbo.CollectionIndexInt.". Not sure what I have missed here.. Can some one help me with this? My oracle code is: TYPE ATTR_VAL IS  RECORD  (    ATTR_DB_NAME VARCHAR2(1000),    ATTR_VAL     VARCHAR2(1000),    ATTR_TYPE    VARCHAR2(10),    ATTR_DEF     VARCHAR2(256)); TYPE T_ATTR_VAL IS  TABLE OF ATTR_VAL;  L_ATTR_VAL T_ATTR_VAL   := T_ATTR_VAL(); Converted SQL code: DECLARE      @CollectionIndexInt$TYPE varchar(max) = ' TABLE OF ( RECORD ( ATTR_DB_NAME STRING , ATTR_VAL STRING , ATTR_TYPE STRING , ATTR_DEF STRING ) )', DECLARE      @L_ATTR_VAL dbo.CollectionIndexInt = dbo.CollectionIndexInt ::[Null].SetType(@CollectionIndexInt$TYPE),

• Anonymous
  September 23, 2015
  I have similar problems as Scott and Narendra, extension pack is installed, most functions are there, but some functions cause problems for unknown reason. I presume something somehow goes wrong in installing the SSMA extension pack - but only sometimes, I have done this a couple of times and I have got past this stage and had my data migrated fine. Still investigating the issue, but starting from the scratch on SQL Server side may be required.