概要

本記事の位置付けについて

Oracle Database から Azure SQL Database へ SQL Server Migration Assistant for Oracle （SSMA）によるプログラム移行検証結果の一部である。次の記事にて個別機能別記事へのリンクを整理している。

SQL Server Migration Assistant for Oracle による Oracle Database から Azure SQL Database への移行検証記事のまとめ - Qiita

レコードとコレクションについて

レコードとコレクションとは、コンポジット・データ型である。

Oracle Database ドキュメントにて、次のように記載されている。

内部コンポーネントを持つ値が格納されます。コンポジット変数全体をサブプログラムにパラメータとして渡すことや、コンポジット変数の内部コンポーネントに個別にアクセスすることができます。

collectionの内部コンポーネントは、常に同じデータ型であり、要素と呼ばれます。

レコードの内部コンポーネントは、データ型が異なる場合があり、フィールドと呼ばれます。

引用元：5 PL/SQLのコレクションとレコード (oracle.com)

コレクションとレコードには、コレクションが3種類に分けられるため、次のようなものがある。

レコード
コレクション
- 連想配列(結合配列、または、索引付き表)
- ネストした表
- VARRAY

参考リンク

ホワイトペーパー
- Guide to Migrating from Oracle to SQL Server 2014 and Azure SQL Database
その他
- CLR UDT を使用したレコードとコレクションのエミュレーション - SQL Server | Microsoft Learn

Azure SQL Database への移行

SSMA による変換

Oracle Database の変換方法が次のホワイトペーパーに記載されている。

Guide to Migrating from Oracle to SQL Server 2014 and Azure SQL Database

次の 6 つの方法が例示されており、SSMA では 5、あるいは、6 に自動変換される。

コレクション、あるいは、レコードを避けるように PL/SQL コードを書き直す方法
テーブル変数で代替する方法
一時テーブルで代替する方法
永続テーブルで代替する方法
XML 型変数で代替する方法
CLR UDT で代替する方法

移行方針

Azure SQL Database では、CLR UDT をサポートしていないため CLR UDT で代替する方法を実施できないため、XML 型変数で代替する方法をベースに自動変換される。

v9.3 では、XML 型変数に想定通り変換されない場合があるため、次のような手動修正が必要である。

CLR UDT となっている変数を、XML 型に修正
ssma_oracle.ExtendCollectionがストアドプロシージャとして変換されるため、関数に修正

変換例

変換例 1 レコードのサンプル

Oracle Database のコード

DECLARE
    TYPE rec IS RECORD (
        department_id   NUMBER(4, 0),
        department_name VARCHAR2(30 BYTE)
    );
    dpt_rec rec;
BEGIN
    dpt_rec.department_id := 60;
    dpt_rec.department_name := 'PERSONNEL';
    dbms_output.put_line(dpt_rec.department_id
                         || ' '
                         || dpt_rec.department_name);
END;

Azure SQL Database への変換

Convert record as a list of separates variablesという SSMA の設定値がYesの場合には、SSMA により次のように変換される。次のコードを実行すると、Oracle Database のコードの同等の動作となる。

BEGIN

   DECLARE
      @DPT_REC$DEPARTMENT_ID numeric(4, 0), 
      @DPT_REC$DEPARTMENT_NAME varchar(30)

   SET @DPT_REC$DEPARTMENT_ID = 60

   SET @DPT_REC$DEPARTMENT_NAME = 'PERSONNEL'

   PRINT ISNULL(CAST(@DPT_REC$DEPARTMENT_ID AS nvarchar(max)), '') + ' ' + ISNULL(@DPT_REC$DEPARTMENT_NAME, '')

END
GO

Convert record as a list of separates variablesという SSMA の設定値がNOの場合には、SSMA により次のように変換される。次のコードを実行すると、エラーとなる。

BEGIN

   DECLARE
      @Record$TYPE varchar(max) = ' RECORD ( DEPARTMENT_ID DECIMAL , DEPARTMENT_NAME STRING )'

   DECLARE
      @DPT_REC ssma_oracle.Record = ssma_oracle.Record ::[Null].SetType(@Record$TYPE)

   SET @DPT_REC = ssma_oracle.SetRecord_float(@DPT_REC, N'DEPARTMENT_ID', 60)

   SET @DPT_REC = ssma_oracle.SetRecord_varchar(@DPT_REC, N'DEPARTMENT_NAME', 'PERSONNEL')

   PRINT ISNULL(CAST(ssma_oracle.GetRecord_float(@DPT_REC, N'DEPARTMENT_ID') AS nvarchar(max)), '') + ' ' + ISNULL(ssma_oracle.GetRecord_varchar(@DPT_REC, N'DEPARTMENT_NAME'), '')

END
GO

CLR UDT 型に変換されるた変数のデータ型を、XML 型に変更することで、Oracle Database のコードの同等の動作となる。@Record$TYPEという変数が生成されるが、不要である。

BEGIN

   -- DECLARE
      -- @Record$TYPE varchar(max) = ' RECORD ( DEPARTMENT_ID DECIMAL , DEPARTMENT_NAME STRING )'

   DECLARE
    --   @DPT_REC ssma_oracle.Record = ssma_oracle.Record ::[Null].SetType(@Record$TYPE)
      @DPT_REC xml

   SET @DPT_REC = ssma_oracle.SetRecord_float(@DPT_REC, N'DEPARTMENT_ID', 60)

   SET @DPT_REC = ssma_oracle.SetRecord_varchar(@DPT_REC, N'DEPARTMENT_NAME', 'PERSONNEL')

   PRINT ISNULL(CAST(ssma_oracle.GetRecord_float(@DPT_REC, N'DEPARTMENT_ID') AS nvarchar(max)), '') + ' ' + ISNULL(ssma_oracle.GetRecord_varchar(@DPT_REC, N'DEPARTMENT_NAME'), '')

END
GO

変換例 2 連想配列(結合配列、または、索引付き表)のサンプル

Oracle Database のコード

DECLARE
    TYPE dpttable IS
        TABLE OF NUMBER;
    depts dpttable;
    i     INTEGER;
BEGIN
    SELECT
        id
    BULK COLLECT
    INTO depts
    FROM (
        SELECT 102 AS ID FROM DUAL
        UNION ALL
        SELECT 202 AS ID FROM DUAL
        UNION ALL
        SELECT 303 AS ID FROM DUAL
    ) src;

    FOR i IN depts.first..depts.last LOOP
        dbms_output.put_line(i
                             || ': '
                             || depts(i));
    END LOOP;
    dbms_output.put_line('COUNT: ' || depts.count());

END;

Azure SQL Database への変換

SSMA により次のように変換される。

BEGIN

   DECLARE
      @DEPTS ssma_oracle.CollectionIndexInt, 
      @I int

   SET @DEPTS = ssma_oracle.fn_bulk_collect2CollectionSimple(
      (
         SELECT SRC.ID AS ID
         FROM 
            (
               SELECT 102 AS ID
                UNION ALL
               SELECT 202 AS ID
                UNION ALL
               SELECT 303 AS ID
            )  AS SRC
         FOR XML PATH
      ))

   DECLARE
      @I$2 int

   SET @I$2 = ssma_oracle.CollectionFirst(@DEPTS)

   DECLARE
      @loop$bound int

   SET @loop$bound = ssma_oracle.CollectionLast(@DEPTS)

   WHILE @I$2 <= @loop$bound
   
      BEGIN

         PRINT ISNULL(CAST(@I$2 AS nvarchar(max)), '') + ': ' + ISNULL(CAST(ssma_oracle.GetCollection_float(@DEPTS, @I$2) AS nvarchar(max)), '')

         SET @I$2 = @I$2 + 1

      END

   PRINT 'COUNT: ' + ISNULL(CAST(ssma_oracle.CountCollection(@DEPTS) AS nvarchar(max)), '')

END
GO

CLR UDT 型に変換されるた変数のデータ型を、XML 型に変更することで、Oracle Database のコードの同等の動作となる。

BEGIN

   DECLARE
    --   @DEPTS ssma_oracle.CollectionIndexInt, 
      @DEPTS xml, 
      @I int

   SET @DEPTS = ssma_oracle.fn_bulk_collect2CollectionSimple(
      (
         SELECT SRC.ID AS ID
         FROM 
            (
               SELECT 102 AS ID
                UNION ALL
               SELECT 202 AS ID
                UNION ALL
               SELECT 303 AS ID
            )  AS SRC
         FOR XML PATH
      ))

   DECLARE
      @I$2 int

   SET @I$2 = ssma_oracle.CollectionFirst(@DEPTS)

   DECLARE
      @loop$bound int

   SET @loop$bound = ssma_oracle.CollectionLast(@DEPTS)

   WHILE @I$2 <= @loop$bound
   
      BEGIN

         PRINT ISNULL(CAST(@I$2 AS nvarchar(max)), '') + ': ' + ISNULL(CAST(ssma_oracle.GetCollection_float(@DEPTS, @I$2) AS nvarchar(max)), '')

         SET @I$2 = @I$2 + 1

      END

   PRINT 'COUNT: ' + ISNULL(CAST(ssma_oracle.CountCollection(@DEPTS) AS nvarchar(max)), '')

END
GO

変換例 3 ネストした表のサンプル

Oracle Database のコード

DECLARE
    v      VARCHAR2(255);
    TYPE inttab IS
        TABLE OF v%type INDEX BY PLS_INTEGER;
    v_tab  inttab;
BEGIN
    v_tab(17) := 'AAA';
    v_tab(25) := 'BBB';
    v := v_tab(17);
    DBMS_OUTPUT.PUT_LINE(v);
END;

Azure SQL Database への変換

SSMA により次のように変換される。

BEGIN

   DECLARE
      @CollectionIndexInt$TYPE varchar(max) = ' TABLE INDEX BY INT OF STRING', 
      @V varchar(255)

   DECLARE
      @V_TAB ssma_oracle.CollectionIndexInt = ssma_oracle.CollectionIndexInt ::[Null].SetType(@CollectionIndexInt$TYPE)

   SET @V_TAB = ssma_oracle.SetCollection_varchar(@V_TAB, 17, 'AAA')

   SET @V_TAB = ssma_oracle.SetCollection_varchar(@V_TAB, 25, 'BBB')

   SET @V = ssma_oracle.GetCollection_varchar(@V_TAB, 17)

   PRINT @V

END
GO

CLR UDT 型に変換されるた変数のデータ型を XML 型に変更することで、Oracle Database のコードの同等の動作となる。@CollectionIndexInt$TYPEという変数が生成されるが、不要である。

BEGIN

   DECLARE
      -- @CollectionIndexInt$TYPE varchar(max) = ' TABLE INDEX BY INT OF STRING', 
      @V varchar(255)

   DECLARE
    --   @V_TAB ssma_oracle.CollectionIndexInt = ssma_oracle.CollectionIndexInt ::[Null].SetType(@CollectionIndexInt$TYPE)
      @V_TAB xml

   SET @V_TAB = ssma_oracle.SetCollection_varchar(@V_TAB, 17, 'AAA')

   SET @V_TAB = ssma_oracle.SetCollection_varchar(@V_TAB, 25, 'BBB')

   SET @V = ssma_oracle.GetCollection_varchar(@V_TAB, 17)

   PRINT @V

END
GO

変換例 4 VARRAY のサンプル

Oracle Database のコード

DECLARE
    TYPE varray_type IS VARRAY(50) OF VARCHAR2(255);
    v1 varray_type := varray_type();
    cnt number;
BEGIN
    v1.extend(50);
    
    -- Error Message 1
    v1(1) := 'aaa';

    -- Error Message 2
    v1(2) := 'bbb';

    -- Error Message 3
    v1(3) := 'ccc';
    
    FOR i IN v1.first..v1.last LOOP
        DBMS_OUTPUT.PUT_LINE(v1(i));
    END LOOP;

END;

Azure SQL Database への変換

SSMA により次のように変換される。

BEGIN

   DECLARE
      @V1 ssma_oracle.CollectionIndexInt = '', 
      /*
      *   SSMA warning messages:
      *   O2SS0356: Conversion from NUMBER datatype can cause data loss.
      */

      @CNT float(53)

   EXECUTE ssma_oracle.ExtendCollection @V1  OUTPUT, 50

   /* Error Message 1*/
   SET @V1 = ssma_oracle.SetCollection_varchar(@V1, 1, 'aaa')

   /* Error Message 2*/
   SET @V1 = ssma_oracle.SetCollection_varchar(@V1, 2, 'bbb')

   /* Error Message 3*/
   SET @V1 = ssma_oracle.SetCollection_varchar(@V1, 3, 'ccc')

   DECLARE
      @I int

   SET @I = ssma_oracle.CollectionFirst(@V1)

   DECLARE
      @loop$bound int

   SET @loop$bound = ssma_oracle.CollectionLast(@V1)

   WHILE @I <= @loop$bound
   
      BEGIN

         PRINT ssma_oracle.GetCollection_varchar(@V1, @I)

         SET @I = @I + 1

      END

END
GO

CLR UDT 型に変換されるた変数のデータ型を、XML 型に変更することで、Oracle Database のコードの同等の動作となる。

BEGIN

   DECLARE
    --   @V1 ssma_oracle.CollectionIndexInt = '', 
      @V1 xml, 
      /*
      *   SSMA warning messages:
      *   O2SS0356: Conversion from NUMBER datatype can cause data loss.
      */

      @CNT float(53)

--    EXECUTE ssma_oracle.ExtendCollection @V1  OUTPUT, 50
   SET @V1 = ssma_oracle.ExtendCollection(@V1, 50, DEFAULT)

   /* Error Message 1*/
   SET @V1 = ssma_oracle.SetCollection_varchar(@V1, 1, 'aaa')

   /* Error Message 2*/
   SET @V1 = ssma_oracle.SetCollection_varchar(@V1, 2, 'bbb')

   /* Error Message 3*/
   SET @V1 = ssma_oracle.SetCollection_varchar(@V1, 3, 'ccc')

   DECLARE
      @I int

   SET @I = ssma_oracle.CollectionFirst(@V1)

   DECLARE
      @loop$bound int

   SET @loop$bound = ssma_oracle.CollectionLast(@V1)

   WHILE @I <= @loop$bound
   
      BEGIN

         PRINT ssma_oracle.GetCollection_varchar(@V1, @I)

         SET @I = @I + 1

      END

END
GO