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](https://qiita.com/manabian/items/0ec96b66033a7bd03a25)


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

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

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

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

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

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

引用元：[5 PL/SQLのコレクションとレコード (oracle.com)](https://docs.oracle.com/cd/F19136_01/lnpls/plsql-collections-and-records.html)

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

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

### 参考リンク

- ホワイトペーパー
    - Guide to Migrating from Oracle to SQL Server 2014 and Azure SQL Database
- その他
    - [CLR UDT を使用したレコードとコレクションのエミュレーション - SQL Server | Microsoft Learn](https://learn.microsoft.com/ja-jp/sql/ssma/oracle/emulate-records-collections-via-clr?view=sql-server-ver16)

## 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 のコード

```sql
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 のコードの同等の動作となる。

```sql
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 により次のように変換される。次のコードを実行すると、エラーとなる。

```sql
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`という変数が生成されるが、不要である。

```sql
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 のコード

```sql
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 により次のように変換される。

```sql
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 のコードの同等の動作となる。

```sql
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 のコード

```sql
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 により次のように変換される。

```sql
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`という変数が生成されるが、不要である。

```sql
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 のコード

```sql
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 により次のように変換される。

```sql
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 のコードの同等の動作となる。

```sql
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
```