SQL Server Migration Assistant for Oracle による Oracle Database から Azure SQL Database への移行検証～Oracle におけるレコードとコレクション編～

概要

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

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 に自動変換される。

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

移行方針

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

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

1. CLR UDT となっている変数を、XML 型に修正
2. 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