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 ドキュメントにて、次のように記載されている。

パッケージとは、関連するプログラム・オブジェクトをカプセル化した集合体で、データベースにまとめて格納されています。プログラム・オブジェクトには、プロシージャ、ファンクション、変数、定数、カーソルおよび例外があります。

引用元：1.1 パッケージの概要 (oracle.com)

Oracle 社が提供しているパッケージもあり、次の記事にて標準で利用できるパッケージが整理されている。

• オラクル社が提供するPL/SQLパッケージおよびタイプの概要 (oracle.com)

参考リンク

• ホワイトペーパー
  • Guide to Migrating from Oracle to SQL Server 2014 and Azure SQL Database

Azure SQL Database への移行

SSMA による変換

パッケージを、ストアドプロシージャ、あるいは、関数に、<schema name>.<package_name>$<procedure_name or function_name>という命名規則で変換する。

• パッケージ変数は、SPID (セッション識別⼦) とセッションのログイン時間で一意となるテーブルのレコードによりエミュレートする。
• オーバーロード機能はサポートされておらず、<schema name>.<package name>$<procedure name>$ovl<# of procedure instance> というように連番が付与される。
• カーソルオブジェクトは、グローバルカーソルとして、<schema>$<package name>$<cursor name>という命名規則で定義される。

移行方針

次の処理でパッケージを利用する。1 のついては、手動対応が必要となる。

1. スキーマ名とパッケージ名を引数として、ssma_oracle.db_check_init_packageを実行
   1. ssma_oracle.db_init_packageを実行
   2. <schema name>.<package_name>$<procedure_name or function_name>$SSMA_Initialize_Packageを実行
   3. ssma_oracle.db_clean_storageプロシージャにより、ssma_oracle.db_storageテーブルから現在のSPID (セッション識別⼦) と現在のセッションのログイン時間のデータを DELETE する
   4. パッケージで定義している変数を、ssma_oracle.db_storageテーブルに格納
2. パッケージプロシージャ、あるいは、パッケージ関数の利用

次の対応が必要となる場合がある。

• パッケージ関数にて、変数を変更している場合には、関数ではなく、ストアドプロシージャとして移行する必要がある。

変換例

変換例 1 パッケージの変換

Oracle Database のコード

次のコードを Oracle Database 上で実行する。

CREATE OR REPLACE PACKAGE local_1 AS
    FUNCTION f1 RETURN INT;

END;
/
CREATE OR REPLACE PACKAGE BODY local_1 AS

    val INT;

    FUNCTION f2 RETURN INT AS
    BEGIN
        val := val + 1;
        RETURN val;
    END;

    FUNCTION f1 RETURN INT AS
    BEGIN
        RETURN f2();
    END;

BEGIN
    val := 4;
END;

次のコードを Oracle Database 側で実行するごとに、加算された数値が出力される。

DECLARE
    x INT;
BEGIN
    x := local_1.f1();
    DBMS_OUTPUT.PUT_LINE(x);
END;

Azure SQL Database への変換

Oracle の SSMA に変換を行うと、次のオブジェクトが生成される。

• HR.LOCAL_1$SSMA_Initialize_Package
• HR.LOCAL_1$F1
• HR.LOCAL_1$F2
• HR.LOCAL_1$F2$IMPL

/*
*   SSMA warning messages:
*   O2SS0519: Package variables value will be changed if rollback occurred.
*/

CREATE PROCEDURE HR.LOCAL_1$SSMA_Initialize_Package
AS 
   EXECUTE ssma_oracle.db_clean_storage
   EXECUTE ssma_oracle.set_pv_int 'SSMA', 'LOCAL_1', 'VAL', 4
GO

CREATE FUNCTION HR.LOCAL_1$F1 
( 
)
RETURNS int
AS 
   BEGIN

      /* 
      *   SSMA error messages:
      *   O2SS0516: Init block can't be used inside function.

      EXECUTE ssma_oracle.db_fn_check_init_package 'SSMA', 'LOCAL_1'
      */



      RETURN HR.LOCAL_1$F2()

   END
GO

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

CREATE FUNCTION HR.LOCAL_1$F2 
( 
)
RETURNS int
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
         @return_value_argument int

      /*
      *   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'LOCAL_1$F2$IMPL', 
         N'true', 
         @return_value_argument  OUTPUT

      RETURN @return_value_argument

   END
*/

CREATE PROCEDURE HR.LOCAL_1$F2$IMPL  
   @return_value_argument int  OUTPUT
AS 
   BEGIN

      DECLARE
         @temp int

      SET @temp = ssma_oracle.get_pv_int('SSMA', 'LOCAL_1', 'VAL') + 1

      EXECUTE ssma_oracle.set_pv_int 'SSMA', 'LOCAL_1', 'VAL', @temp

      SET @return_value_argument = ssma_oracle.get_pv_int('SSMA', 'LOCAL_1', 'VAL')

      RETURN 

   END
GO

次のコードを Azure SQL Database 側で実行するごとに、加算された数値が出力される。

-- Azure SQL Database call to the package
BEGIN
    DECLARE
        @x int
    EXECUTE ssma_oracle.db_check_init_package 'SSMA', 'LOCAL_1'
    EXECUTE HR.LOCAL_1$f1 @x OUTPUT
END

変換例 2 パッケージの仕様部にカーソルを定義

Oracle Database のコード

CREATE OR REPLACE PACKAGE HR.pkg_cursor_test IS

PROCEDURE main;

CURSOR emp_cur IS SELECT * FROM HR.departments WHERE manager_id IS NULL;
emp_rec
emp_cur%rowtype;

end;
/
CREATE OR REPLACE PACKAGE BODY HR.pkg_cursor_test IS

    PROCEDURE main IS
    BEGIN
        OPEN emp_cur;
        LOOP
            FETCH emp_cur INTO emp_rec;
            EXIT WHEN emp_cur%notfound;
            dbms_output.put_line(emp_rec.department_name
                                 || ' '
                                 || emp_rec.department_name);
        END LOOP;

        CLOSE emp_cur;
    END;
END;

Azure SQL Database への変換

パッケージの仕様部にて定義したカーソル処理に関するコードが、個別のストアドプロシージャ（例：HR.PKG_CURSOR_TEST$MAIN）に追記される。

CREATE PROCEDURE HR.PKG_CURSOR_TEST$MAIN  
AS 
   BEGIN

      EXECUTE ssma_oracle.db_check_init_package 'SSMA', 'PKG_CURSOR_TEST'

      /*
      *   SSMA warning messages:
      *   O2SS0437: Global cursor may not work properly.
      */

      DECLARE
          HR$PKG_CURSOR_TEST$EMP_CUR CURSOR GLOBAL FOR 
            SELECT DEPARTMENTS.DEPARTMENT_ID, DEPARTMENTS.DEPARTMENT_NAME, DEPARTMENTS.MANAGER_ID, DEPARTMENTS.LOCATION_ID
            FROM HR.DEPARTMENTS
            WHERE DEPARTMENTS.MANAGER_ID IS NULL

      EXECUTE ssma_oracle.set_pv_int 'SSMA', 'PKG_CURSOR_TEST', 'v_EMP_CUR_ROWCOUNT', 0

      OPEN HR$PKG_CURSOR_TEST$EMP_CUR

      WHILE 1 = 1
      
         BEGIN

            DECLARE
               @PKG_CURSOR_TEST$EMP_REC$DEPARTMENT_ID float(53), 
               @PKG_CURSOR_TEST$EMP_REC$DEPARTMENT_NAME varchar(8000), 
               @PKG_CURSOR_TEST$EMP_REC$MANAGER_ID float(53), 
               @PKG_CURSOR_TEST$EMP_REC$LOCATION_ID float(53)

            FETCH HR$PKG_CURSOR_TEST$EMP_CUR
                INTO @PKG_CURSOR_TEST$EMP_REC$DEPARTMENT_ID, @PKG_CURSOR_TEST$EMP_REC$DEPARTMENT_NAME, @PKG_CURSOR_TEST$EMP_REC$MANAGER_ID, @PKG_CURSOR_TEST$EMP_REC$LOCATION_ID

            EXECUTE ssma_oracle.set_pv_float 'SSMA', 'PKG_CURSOR_TEST', 'EMP_REC$LOCATION_ID', @PKG_CURSOR_TEST$EMP_REC$LOCATION_ID

            EXECUTE ssma_oracle.set_pv_float 'SSMA', 'PKG_CURSOR_TEST', 'EMP_REC$MANAGER_ID', @PKG_CURSOR_TEST$EMP_REC$MANAGER_ID

            EXECUTE ssma_oracle.set_pv_varchar 'SSMA', 'PKG_CURSOR_TEST', 'EMP_REC$DEPARTMENT_NAME', @PKG_CURSOR_TEST$EMP_REC$DEPARTMENT_NAME

            EXECUTE ssma_oracle.set_pv_float 'SSMA', 'PKG_CURSOR_TEST', 'EMP_REC$DEPARTMENT_ID', @PKG_CURSOR_TEST$EMP_REC$DEPARTMENT_ID

            IF @@FETCH_STATUS = 0
               BEGIN

                  DECLARE
                     @temp int

                  SET @temp = ssma_oracle.get_pv_int('SSMA', 'PKG_CURSOR_TEST', 'v_EMP_CUR_ROWCOUNT') + 1

                  EXECUTE ssma_oracle.set_pv_int 'SSMA', 'PKG_CURSOR_TEST', 'v_EMP_CUR_ROWCOUNT', @temp

               END

            /*
            *   SSMA warning messages:
            *   O2SS0113: The value of @@FETCH_STATUS might be changed by previous FETCH operations on other cursors, if the cursors are used simultaneously.
            */

            IF @@FETCH_STATUS <> 0
               BREAK

            PRINT ISNULL(ssma_oracle.get_pv_varchar('SSMA', 'PKG_CURSOR_TEST', 'EMP_REC$DEPARTMENT_NAME'), '') + ' ' + ISNULL(ssma_oracle.get_pv_varchar('SSMA', 'PKG_CURSOR_TEST', 'EMP_REC$DEPARTMENT_NAME'), '')

         END

      CLOSE HR$PKG_CURSOR_TEST$EMP_CUR

      DEALLOCATE HR$PKG_CURSOR_TEST$EMP_CUR

   END
GO