Oracle の OUT バインド変数を PostgreSQL データベースに移行

作成者: Bikash Chandra Rout (AWS) と Vinay Paladi (AWS)

環境: PoCまたはパイロット	ソース: データベースリレーショナル	ターゲット: RDS/Aurora PostgreSQL
Rタイプ: リプラットフォーム	ワークロード: Oracle	テクノロジー: データベース、移行
AWS サービス: Amazon Aurora、Amazon RDS、AWS SCT

[概要]

このパターンでは、Oracleデータベース OUT のバインド変数を、次の PostgreSQL 互換 AWS データベースサービスのいずれか1つに移行する方法を示します：

• PostgreSQL の Amazon Relational Database Service (Amazon RDS)

• Amazon Aurora PostgreSQL 互換エディション

PostgreSQLは、OUTのバインド変数をサポートしていません。Python ステートメントで同じ機能を取得するには、代わりに、GET と SET のパッケージ変数を使用するカスタム PL/pgSQL 関数を作成できます。これらの変数を適用するために、このパターンで提供されるサンプルのラッパー関数スクリプトでは、「AWS Schema Conversion Tool (AWS SCT) 拡張パック」 を使用しています。

注: Oracle EXECUTE IMMEDIATE ステートメントが　1 行しか返すことができない SELECT ステートメントである場合、以下がベストプラクティスです：

• OUT バインド変数 (定義) をINTO 句に入れます。

• IN バインド変数をUSING 句に 入れます

詳細については、Oracle ドキュメントの「EXECUTE IMMEDIATE ステートメント」 を参照してください。

前提条件と制限

前提条件

• アクティブなAWS アカウント 

• オンプレミスのデータセンターにある Oracle データベース 10g (またはより新しい) のソースデータベース

• 「Amazon RDS for PostgreSQL DB インスタンス」 または 「Aurora PostgreSQL 互換 DB インスタンス」

アーキテクチャ

ソーステクノロジースタック

• オンプレミスの Oracle データベース 10g (またはそれ以降) データベース 

ターゲットテクノロジースタック

• Amazon RDS for PostgreSQL DB インスタンスまたは Aurora PostgreSQL DB インスタンス

ターゲットアーキテクチャ

次の図は、Oracle Database のOUTバインド変数を PostgreSQL 互換の AWS データベースに移行するためのワークフローの例を示しています。

この図表は、次のワークフローを示しています：

1. AWS SCT は、ソースデータベーススキーマとカスタムコードの大部分を、ターゲット PostgreSQL 互換 AWS データベースと互換性のある形式に変換します。

2. 自動的に変換できないいずれかのデータベースオブジェクトには、PL/pgSQL 関数によってフラグが立てられます。フラグが立てられたオブジェクトが手動で変換され、移行が完了します。

ツール

• 「Amazon Aurora PostgreSQL 互換エディション」は、PostgreSQL デプロイのセットアップ、運用、スケーリングに役立つ、フルマネージド型のACID準拠のリレーショナルデータベースエンジンです。

• PostgreSQL の Amazon Relational Database Service (Amazon RDS) を使用して、 AWS Cloud.でリレーショナルデータベース (DB) のセットアップ、運用、スケーリングができます。

• AWS Schema Conversion Tool (AWS SCT) は、ソースデータベーススキーマと大部分のカスタムコードを、ターゲットデータベースと互換性のある形式に自動的に変換することにより、異種データベースの移行をサポートします。

• 「pgAdmin」は.PostgreSQLのオープンソース管理ツールです。データベースオブジェクトの作成、管理、使用を支援するグラフィカルインターフェイスを提供します。

エピック

カスタム PL/pgSQL 関数と AWS SCT を使用して Oracle OUT バインド変数を移行

タスク	説明	必要なスキル
PostgreSQL 互換 AWS データベースに接続します。	DB インスタンスの作成後に、標準の SQL クライアントアプリケーションを使用して DB クラスターのデータべースに接続できます。たとえば、「pgAdmin」 を使用して DB インスタンスに接続できます。 詳細については、以下のいずれかを参照してください： Amazon RDS ユーザーガイドの「Amazon RDS DB インスタンスに接続」 Amazon Aurora ユーザーガイドの「Amazon Aurora DB クラスターに接続」	移行エンジニア
このパターンのラッパー関数スクリプトの例を、ターゲットデータベースのメインスキーマに追加します。	このパターンの追加情報セクションから、PL/pgSQL ラッパー関数スクリプトの例をコピーします。次に、その関数をターゲットデータベースのメインスキーマに追加します。 詳細については、PostgreSQL のドキュメントの「機能の作成」を参照してください。	移行エンジニア
(オプション)ターゲットデータベースのメインスキーマの検索パスを更新して、Test_PG スキーマを含むようにします。	パフォーマンス向上のために、PostgreSQL の search_path 変数を更新して Test_PG スキーマ名が含まれるようにします。検索パスにスキーマ名を含めると、PL/pgSQL 関数を呼び出すたびに名前を指定する必要はありません。 詳細については、PostgreSQL のドキュメントのセクション 「5.9.3 スキーマ検索パス」 を参照してください。	移行エンジニア

関連リソース

• AWS スキーマ変換ツール

• 「アウトバインド変数」 (Oracle ドキュメント)

• 「バインド変数を使用して SQL クエリのパフォーマンスを向上」 (Oracle ブログ)

追加情報

PL/pgSQL 関数の例

/* Oracle */

CREATE or replace PROCEDURE test_pg.calc_stats_new1 (
                                              a NUMBER,
                                              b NUMBER,
                                              result out NUMBER
                                            )                                                                                   
IS
BEGIN
result:=a+b;
END;
/
/* Testing */
set serveroutput on 
DECLARE
  a NUMBER := 4;
  b NUMBER := 7;
  plsql_block VARCHAR2(100);
  output number;
BEGIN
  plsql_block := 'BEGIN test_pg.calc_stats_new1(:a, :b,:output); END;';
  EXECUTE IMMEDIATE plsql_block USING a, b,out output;  -- calc_stats(a, a, b, a)
  DBMS_OUTPUT.PUT_LINE('output:'||output);
END;

output:11


PL/SQL procedure successfully completed.

--Postgres--

/* Example : 1 */
CREATE OR REPLACE FUNCTION test_pg.calc_stats_new1(
                                                    w integer,
                                                    x integer
                                                  )
RETURNS integer
AS 
$BODY$
begin
      return w + x ;
end;
$BODY$
LANGUAGE  plpgsql;


CREATE OR REPLACE FUNCTION aws_oracle_ext.set_package_variable(
                                                                package_name name,
                                                                variable_name name,
                                                                variable_value anyelement
                                                              )
    RETURNS void
    LANGUAGE 'plpgsql'

    COST 100
    VOLATILE 
AS $BODY$
begin
    perform set_config
      ( format( '%s.%s',package_name, variable_name )
      , variable_value::text
      , false );
  end;
$BODY$;

CREATE OR REPLACE FUNCTION aws_oracle_ext.get_package_variable_record(
                                                                      package_name name,
                                                                      record_name name
                                                                    )
RETURNS text
LANGUAGE 'plpgsql'
    COST 100
    VOLATILE 
AS $BODY$
begin
    execute 'select ' || package_name || '$Init()';

    return aws_oracle_ext.get_package_variable
        ( 
         package_name := package_name
        , variable_name := record_name || '$REC' );
end;
$BODY$;

--init()--
CREATE OR REPLACE FUNCTION test_pg.init()
RETURNS void
AS
$BODY$
BEGIN
if aws_oracle_ext.is_package_initialized('test_pg' ) then
      return;
    end if;
    perform aws_oracle_ext.set_package_initialized
      ('test_pg' );
PERFORM aws_oracle_ext.set_package_variable('test_pg', 'v_output', NULL::INTEGER);
PERFORM aws_oracle_ext.set_package_variable('test_pg', 'v_status', NULL::text);
END;
$BODY$
LANGUAGE  plpgsql;

/* callable for 1st Example */

DO $$ 
declare
v_sql text;
v_output_loc int; 
a integer :=1;
b integer :=2;
BEGIN 
perform  test_pg.init();
--raise notice 'v_sql %',v_sql;
execute 'do $a$ declare v_output_l int; begin select * from test_pg.calc_stats_new1('||a||','||b||') into v_output_l;
PERFORM aws_oracle_ext.set_package_variable(''test_pg'', ''v_output'', v_output_l) ; end; $a$'  ; 
v_output_loc := aws_oracle_ext.get_package_variable('test_pg', 'v_output');
raise notice 'v_output_loc %',v_output_loc; 
END ; 
$$

/*In above Postgres example we have set the value of v_output using v_output_l in the dynamic anonymous block to mimic the 
  behaviour of oracle out-bind variable .*/

--Postgres Example : 2 --
CREATE OR REPLACE FUNCTION test_pg.calc_stats_new2(
 w integer,
 x integer,
inout status text,
 out result integer)
AS
$BODY$
DECLARE
begin
result :=  w + x ;
status := 'ok';
end;
$BODY$
LANGUAGE  plpgsql;

/* callable for 2nd Example */
DO $$ 
declare
v_sql text;
v_output_loc int; 
v_staus text:= 'no';
a integer :=1;
b integer :=2;
BEGIN 
perform  test_pg.init();
execute 'do $a$ declare v_output_l int; v_status_l text; begin select * from test_pg.calc_stats_new2('||a||','||b||','''||v_staus||''') into v_status_l,v_output_l;
PERFORM aws_oracle_ext.set_package_variable(''test_pg'', ''v_output'', v_output_l) ; 
PERFORM aws_oracle_ext.set_package_variable(''test_pg'', ''v_status'', v_status_l) ; 
end; $a$'  ; 
v_output_loc := aws_oracle_ext.get_package_variable('test_pg', 'v_output');
v_staus := aws_oracle_ext.get_package_variable('test_pg', 'v_status');
raise notice 'v_output_loc %',v_output_loc; 
raise notice 'v_staus %',v_staus; 
END ; 
$$