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)


### ユーザー定義関数について

SQL、または、組込み関数にはない機能をもたせた関数である。

### 参考リンク

- ホワイトペーパー
    - Guide to Migrating from Oracle to SQL Server 2014 and Azure SQL Database
    - Oracle to SQL Server Migration Handbook v 2.0

## Azure SQL Database への移行

### SSMA による変換

Oracle Database では関数内で次のようなことを実施している場合には、関数だけでなく、ストアードプロシージャが生成される。

- DML ステートメントを含めること
- ストアド プロシージャを呼び出すこと
- トランザクション管理コマンドを実施すること
- 例外処理

Oracle Database では関数の引数として次の方法を実施できるが、Azure SQL ファミリーが対応している位置表記法に変換される。

- 位置表記法
- 名前表記法
- 混合表記法

`INLINE pragma`を指定している場合には、エラーとなるため、`INLINE pragma`の表記を削除する必要がある。

### 移行方針

Azure SQL Database では拡張ストアド プロシージャ機能がサポートされていないため、`xp_ora2ms_exec2_ex` 拡張ストアードプロシージャを用いた関数に変換された場合には、同時に生成されるプロシージャ（`$IMPL`）への呼び出しに置き換える必要がある。

### 変換例

#### 変換例 1 基本的な関数

##### Oracle Database のコード

```sql
CREATE OR REPLACE FUNCTION funtions_001 (
    in_var_1 VARCHAR2 := ' ',
    in_var_2 VARCHAR2 := ' ',
    in_var_3 VARCHAR2 := ' '
) RETURN NUMBER IS
    return_val NUMBER := 999;
BEGIN
    IF in_var_1 != ' ' THEN
        return_val := 1;
    END IF;
    IF in_var_2 != ' ' THEN
        return_val := 2;
    END IF;
    IF in_var_3 != ' ' THEN
        return_val := 3;
    END IF;
    RETURN ( return_val );
END;
```

##### Azure SQL Database への変換

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

```sql
CREATE FUNCTION SSMA.FUNTIONS_001 
( 
   @IN_VAR_1 varchar(max) = ' ',
   @IN_VAR_2 varchar(max) = ' ',
   @IN_VAR_3 varchar(max) = ' '
)
/*
*   SSMA warning messages:
*   O2SS0356: Conversion from NUMBER datatype can cause data loss.
*/

RETURNS float(53)
AS 
   BEGIN

      DECLARE
         /*
         *   SSMA warning messages:
         *   O2SS0356: Conversion from NUMBER datatype can cause data loss.
         */

         @RETURN_VAL float(53) = 999

      IF @IN_VAR_1 != ' '
         SET @RETURN_VAL = 1

      IF @IN_VAR_2 != ' '
         SET @RETURN_VAL = 2

      IF @IN_VAR_3 != ' '
         SET @RETURN_VAL = 3

      RETURN @RETURN_VAL

   END
GO
```

#### 変換例 2 ストアードプロシージャに置換される例

##### Oracle Database のコード

```SQL
CREATE OR REPLACE FUNCTION funtions_002 (
    in_var_1 VARCHAR2 := ' ',
    in_var_2 VARCHAR2 := ' ',
    in_var_3 VARCHAR2 := ' '
) RETURN NUMBER IS
    return_val NUMBER := 999;
BEGIN
    IF in_var_1 != ' ' THEN
        return_val := 1;
    END IF;
    IF in_var_2 != ' ' THEN
        return_val := 2;
    END IF;
    IF in_var_3 != ' ' THEN
        return_val := 3;
    END IF;

    RETURN ( return_val );
EXCEPTION
    WHEN OTHERS THEN
        dbms_output.put_line('エラーです');
END;
```

##### Azure SQL Database への変換

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

```SQL

/* 
*   SSMA error messages:
*   O2SS0518: Wrapper functions are not supported by Azure SQL platform. Use $impl procedures instead.

CREATE FUNCTION SSMA.FUNTIONS_002 
( 
   @IN_VAR_1 varchar(max) = ' ',
   @IN_VAR_2 varchar(max) = ' ',
   @IN_VAR_3 varchar(max) = ' '
)
/*
*   SSMA warning messages:
*   O2SS0356: Conversion from NUMBER datatype can cause data loss.
*/

RETURNS float(53)
AS 
   BEGIN

      DECLARE
         @active_spid INT, 
         @login_time DATETIME, 
         @db_name NVARCHAR(128)

      SET @active_spid = ssma_oracle.GET_ACTIVE_SPID()

      SET @login_time = ssma_oracle.GET_ACTIVE_LOGIN_TIME()

      SET @db_name = DB_NAME()

      DECLARE
         /*
         *   SSMA warning messages:
         *   O2SS0356: Conversion from NUMBER datatype can cause data loss.
         */

         @return_value_argument float(53)

      /*
      *   SSMA warning messages:
      *   O2SS0452: "xp_ora2ms_exec2_ex" when called from within UDF cannot bind to outer transaction. It can lead to dead locks and losing transaction atomicity. Consider calling $impl procedure directly.
      */

      EXECUTE master.dbo.xp_ora2ms_exec2_ex 
         @active_spid, 
         @login_time, 
         @db_name, 
         N'SSMA', 
         N'FUNTIONS_002$IMPL', 
         N'true', 
         @IN_VAR_1, 
         @IN_VAR_2, 
         @IN_VAR_3, 
         @return_value_argument  OUTPUT

      RETURN @return_value_argument

   END
*/
```

```SQL
CREATE PROCEDURE SSMA.FUNTIONS_002$IMPL  
   @IN_VAR_1 varchar(max) = ' ',
   @IN_VAR_2 varchar(max) = ' ',
   @IN_VAR_3 varchar(max) = ' ',
   /*
   *   SSMA warning messages:
   *   O2SS0356: Conversion from NUMBER datatype can cause data loss.
   */

   @return_value_argument float(53)  OUTPUT
AS 
   BEGIN

      DECLARE
         /*
         *   SSMA warning messages:
         *   O2SS0356: Conversion from NUMBER datatype can cause data loss.
         */

         @RETURN_VAL float(53) = 999

      BEGIN TRY

         IF @IN_VAR_1 != ' '
            SET @RETURN_VAL = 1

         IF @IN_VAR_2 != ' '
            SET @RETURN_VAL = 2

         IF @IN_VAR_3 != ' '
            SET @RETURN_VAL = 3

         SET @return_value_argument = @RETURN_VAL

         RETURN 

      END TRY

      BEGIN CATCH
         BEGIN
            PRINT 'エラーです'
         END
      END CATCH

   END
GO
```

#### 変換例 3 位置表記法、名前表記法、および、混合表記法によるパラメータの指定

##### Oracle Database のコード

```SQL
BEGIN
    dbms_output.put_line(SSMA.funtions_001());
    dbms_output.put_line(SSMA.funtions_001('1'));
    dbms_output.put_line(SSMA.funtions_001(' ', '2'));
    dbms_output.put_line(SSMA.funtions_001(' ', in_var_2=>'2'));
    dbms_output.put_line(SSMA.funtions_001(in_var_2=>'2'));
END;
```

##### Azure SQL Database への変換

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

```SQL

BEGIN

   /*
   *   SSMA warning messages:
   *   O2SS0105: Function (procedure) calls must include required parameters: IN_VAR_1.
   *   O2SS0105: Function (procedure) calls must include required parameters: IN_VAR_2.
   *   O2SS0105: Function (procedure) calls must include required parameters: IN_VAR_3.
   */

   PRINT SSMA.FUNTIONS_001(DEFAULT, DEFAULT, DEFAULT)

   /*
   *   SSMA warning messages:
   *   O2SS0105: Function (procedure) calls must include required parameters: IN_VAR_2.
   *   O2SS0105: Function (procedure) calls must include required parameters: IN_VAR_3.
   */

   PRINT SSMA.FUNTIONS_001('1', DEFAULT, DEFAULT)

   /*
   *   SSMA warning messages:
   *   O2SS0105: Function (procedure) calls must include required parameters: IN_VAR_3.
   */

   PRINT SSMA.FUNTIONS_001(' ', '2', DEFAULT)

   /*
   *   SSMA warning messages:
   *   O2SS0105: Function (procedure) calls must include required parameters: IN_VAR_3.
   */

   PRINT SSMA.FUNTIONS_001(' ', '2', DEFAULT)

   /*
   *   SSMA warning messages:
   *   O2SS0105: Function (procedure) calls must include required parameters: IN_VAR_1.
   *   O2SS0105: Function (procedure) calls must include required parameters: IN_VAR_3.
   */

   PRINT SSMA.FUNTIONS_001(DEFAULT, '2', DEFAULT)

END
GO
```

#### 変換例 4 INLINE pragma の利用

##### Oracle Database のコード

```SQL
BEGIN PRAGMA inline ( funtions_001, 'YES' );
    dbms_output.put_line(SSMA.funtions_001());
PRAGMA inline ( funtions_001, 'NO' );
    dbms_output.put_line(SSMA.funtions_001());
END;
```

##### Azure SQL Database への変換

次のように、エラーとなる。

```SQL

BEGIN

   /* 
   *   SSMA error messages:
   *   O2SS0004: Unparsed SQL (Parse error at line 2, column 16.  Encountered: .) 
   *   [PRAGMA inline ( funtions_001, 'YES' );
   *       dbms_output.put_line(SSMA.funtions_001());
   *   PRAGMA inline ( funtions_001, 'NO' );
   *       dbms_output.put_line(SSMA.funtions_001());] cannot be converted.
   */



   DECLARE
      @db_null_statement int

END
GO
```