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: