このora2pgがすごい!Oracleのストアドプロシージャ2種をPostgreSQLに移行してみた!
商用DBMSのOracleからPostgreSQLへの移行について紹介したいと思います。
ぬこのたのしいぽすぐれ教室 第2回
- 2017年10月27日公開
はじめに
みなさんはご存知でしょうか? ここ数年、PostgreSQLが目覚しく進化していることを。
以前は商用DBMSでなければ利用できなかった同期レプリケーションや、CPUコア数の増加に伴う性能のスケールアップ、マテリアライズドビューやパラレルクエリなどの機能も使えるようになっています。
また、周辺ツールも充実しています。性能監視ツール pg_statsinfo や、SQLの実行計画を制御する pg_hint_plan 、日本語全文検索を行う pg_bigm ほか、PostgreSQLの利用用途を広めるさまざまなツールが存在します。
このような背景から、PostgreSQLを導入するシステムも多くなりました。今までは商用DBMSしか選択肢がなかったシステムでも、更改のタイミングでPostgreSQLに乗り換えを検討するところが増えています。
今回は、そんな状況を踏まえ、商用DBMSのOracleからPostgreSQLへの移行を試してみたいと思います。
OracleからPostgreSQLの移行にはどんな作業が必要?
OracleからPostgreSQLへの移行作業は、以下のように分類できます。
- Oracleデータベースのオブジェクト(DDL)を、PostgreSQLに移行する。
- Oracleデータベースに登録しているデータをPostgreSQLに移行する。
- Oracle用のアプリケーションのSQLをPostgreSQL用に書き換える。
Oracle、PostgreSQL両方のDBMSに通じており、「Oracle用のDDL、SQLをPostgreSQL用に書き換えるなんて簡単!」というエンジニアであっても、全ての修正要否を目視で確認し、修正していくのは大変な労力を要するでしょう。
修正量自体も膨大です。DBテーブルが1000個あったとして、それを手作業で修正していくなんてのは・・・考えたくないですよね^^;
そんな問題を解決するために使用するのが「ora2pg」です!
ora2pgとは?
ora2pgはOracle/MySQLからPostgreSQLへの移行を支援するツールです。
Oracleデータベースからオブジェクト定義を読み出し、PostgreSQLで実行可能なDDLにコンバートします。 DBデータも同様に、PostgreSQLで実行可能なINSERT文の形式にコンバートすることが可能です。
コンバートした結果はファイルに出力されます。出力したファイルをPostgreSQLに対して実行するだけで、オブジェクト、データの移行が完了します。
ora2pgの主な機能
ora2pgの主な機能は以下の3つです。
- OracleのオジェクトをPostgreSQLのDDLの書式にコンバートする
- OracleのDBデータをPostgreSQLのINSERT文として出力する
- アプリケーション(SQLファイル)をPostgreSQLで実行可能な形式にコンバートする
ツールの実行イメージは以下のとおり。

ora2pgでは、テーブルやインデックスといった一般的なオブジェクトの移行はもちろん、Oracle固有のオブジェクトも、PostgreSQLのDDLに置き換えてくれます。
たとえば、パーティションテーブル。
PostgreSQLでパーティションテーブルを作成するときは、テーブル、子テーブル、トリガなどの組み合わせを全て別々に定義し、パーティションテーブル構成を実現する必要がありますが、ora2pgではそれらの面倒な定義を一発変換してくれます。非常に楽です。
一方で、Oracleの「CREATE TABLE」文のPCTFREE句をPostgreSQLのFILLFACTOR句へ置き換えるといった、一見、文字列置換でできるような簡単な置き換えは行われません。こういったところは必要に応じて修正する必要があったりもします。
ora2pgで移行可能なOracleのオブジェクト種別
ora2pg.confに設定可能なオブジェクト種別は以下のとおり。 コンフィグファイルには明記されていませんが、「DBLINK」「SYNONYM」も指定可能です。
移行対象オブジェクト | 指定するキーワード | 説明 |
---|---|---|
テーブル | TABLE | Export tables, constraints, indexes, ... |
パッケージ | PACKAGE | Export packages |
データ | INSERT | Export data from table as INSERT statement |
データ | COPY | Export data from table as COPY statement |
ビュー | VIEW | Export views |
権限 | GRANT | Export grants |
シーケンス | SEQUENCE | Export sequences |
トリガ | TRIGGER | Export triggers |
ユーザ関数 | FUNCTION | Export functions |
プロシージャ | PROCEDURE | Export procedures |
表領域 | TABLESPACE | Export tablespace (PostgreSQL >= 8 only) |
データ型 | TYPE | Export user defined Oracle types |
パーティション | PARTITION | Export range or list partition (PostgreSQL >= v8.4) |
外部表 | FDW | Export table as foreign data wrapper tables |
マテリアライズドビュー | MVIEW | Export materialized view as snapshot refresh view |
SQL文 | QUERY | Convert Oracle SQL queries from a file. |
XMLテンプレート | KETTLE | Generate XML ktr template files to be used by Kettle. |
シノニム | SYNONYM | (説明なし) |
データベースリンク | DBLINK | (説明なし) |
この中から移行したいオブジェクト種別をひとつを選び、ora2pg.confに設定します。テーブルをOracleからPostgreSQLにコンバートする場合は以下のようにコンフィグファイルに設定します。
TYPE = TABLE
全てのオブジェクト、データを一度に全部コンバートすることはできません。
コンフィグファイルを設定してora2pgを実行すると、指定した1つのオブジェクト種別のみがPostgreSQLで実行可能な形式にコンバートされ、SQLファイルに出力されます。
移行したいオブジェクト種別が複数ある場合は、オブジェクト種別の数だけora2pgを実行し、オブジェクト種別の数だけSQLファイルを生成する必要があります。
ストアドプロシージャに注目!PL/SQLのオブジェクトが移行できるか試してみた!
ストアドプロシージャは、PL/SQLで記述された一連の処理をデータベースに格納した、Oracle固有のオブジェクトです。
オブジェクトではあるのですが、実態はプログラムであるため、文字列置換のような定型的な修正では対応できません。そのため、通常はPostgreSQLのPL/pgSQLの形式に手作業でコンバートする必要があります。
PL/SQLとは?
PL/SQLは、OracleのSQL言語を拡張した手続き型のプログラム言語です。アプリケーションを作成する際には、ビジネスロジックとデータベース操作(SQL)は別々に作成しますが、PL/SQLを使うことで、ビジネスロジックとデータベース操作をひとまとめにできます。
また、PL/SQLを使って作成したプログラムはオブジェクトとしてデータベース内に事前に登録でき、ユーザが必要なタイミングで利用することができます。
プログラムの構文解析や、プログラム内でアクセスしているテーブルなどのオブジェクト存在チェックはストアドプロシージャの登録時に行われます。これにより、実際にプログラムを実行する際には構文解析が不要となり、高速な処理が期待できます。
このようなオブジェクトをストアドプロシージャ、ストアドファンクション(ユーザ定義関数)と呼びます。
PL/pgSQLとは?
PL/pgSQLは、OracleのPL/SQLと同様、PostgreSQLのSQL言語を拡張した手続き型のプログラム言語になります。PostgreSQLではPL/pgSQLを使って、ユーザ定義関数やトリガを作成します。
PostgreSQLのマニュアルには、OracleのPL/SQLからPL/pgSQLへの移植例が記載されています。
https://www.postgresql.jp/document/9.6/html/plpgsql-porting.html
移植例を見ると分かるように、PostgreSQLのPL/pgSQLはOracleのPL/SQLと結構似ています。
とはいえ、PL/SQLで書かれたプログラムをPostgreSQL上でそのまま実行できるわけではないため、PL/pgSQLの構文規約や、PostgreSQLとOracleの差分を吸収する形で書き換える必要があります。
実際にストアドプロシージャの移行を試してみる
ストアドプロシージャはプログラムのため、移行の難易度は非常に高いといえます。であればこそ、ora2pgの実力を試すのに最適ということで、PL/SQLで作成したストアドプロシージャをOracleに登録し、実際に移行してみました!
対象としたオブジェクトは以下の2つです。
- ストアドプロシージャ
- パッケージプロシージャ
パッケージプロシージャは、複数のストアドプロシージャやストアドファンクションをさらにひとまとめにしてパッケージ化したもので、Oracle固有のオブジェクト種別となります。
環境条件
今回、移行の確認のために使用した環境は以下のとおりです。
- 移行元:Oracleバージョン:12c
- 移行先:PostgreSQLバージョン 9.6
- OS:CentOS7.3
ストアドプロシージャの移行例
サンプルとして、t_emp テーブルから、給料の平均を取得するプロシージャを作成しました。入力には「給料(SAL)」「部署(DEPTNO)」を使います。入力した「部署」が同じで、かつ入力した「給料」よりも多い社員の「給料」の平均を返却します。
テーブルは以下の状態とします。なお、t_emp テーブルは「パッケージプロシージャ」の移行例でも使用します。
SQL> select * from t_emp
EMPNO ENAME SAL DEPTNO
---------- ---------- ---------- ----------
1 KUMA 150000 10
2 NEKO 200000 10
3 KIJI 300000 20
4 TORA 250000 20
OracleのDDL
CREATE OR REPLACE PROCEDURE test_proc ( sal_in IN NUMBER, deptno_in IN NUMBER, sal_avg_out OUT NUMBER )
AS
empno_out NUMBER;
ename_out VARCHAR2(100);
BEGIN
SELECT empno, ename INTO empno_out, ename_out
FROM (
SELECT empno, ename
FROM t_emp
WHERE sal >= sal_in AND deptno = deptno_in
ORDER BY empno
)
WHERE ROWNUM = 1;
DBMS_OUTPUT.PUT_LINE( 'EMPNO : ' || empno_out );
DBMS_OUTPUT.PUT_LINE( 'ENAME : ' || ename_out );
SELECT AVG(sal) INTO sal_avg_out FROM t_emp WHERE sal >= sal_in AND deptno = deptno_in;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('ERROR : '|| SQLCODE || ' : ' || SQLERRM );
END;
/
ストアドプロシージャというオブジェクト種別はPostgreSQLにはありません。また、PostgreSQLにはないROWNUMや、Oracle固有のDBMS_OUTPUTパッケージプロシージャのほか、エラー処理がどのようににコンバートされるかを確認しましょう。
PostgreSQL変換後のDDL
プロシージャは関数へコンバートされました。 コンバートしたDDLをそのままPostgreSQL上で実行したところ、いくつかのエラーが発生したため、修正しています。(後述)
CREATE OR REPLACE FUNCTION test_proc ( sal_in bigint, deptno_in bigint, sal_avg_out OUT bigint ) AS $body$
DECLARE
empno_out bigint;
ename_out varchar(100);
BEGIN
SELECT empno, ename INTO empno_out, ename_out
FROM (
SELECT empno, ename
FROM t_emp
WHERE sal >= sal_in AND deptno = deptno_in
ORDER BY empno
) TMP_TBL LIMIT 1 OFFSET 0;
RAISE NOTICE 'EMPNO : %', empno_out;
RAISE NOTICE 'ENAME : %', ename_out;
SELECT AVG(sal) INTO sal_avg_out FROM t_emp WHERE sal >= sal_in AND deptno = deptno_in;
EXCEPTION
WHEN OTHERS THEN
RAISE NOTICE 'ERROR : % : %', SQLSTATE, SQLERRM;
END;
$body$
LANGUAGE PLPGSQL
SECURITY DEFINER
;
文字列を表示するDBMS_OUTPUT.PUT_LINEは、RAISE NOTICE にコンバートされています。ROWNUMはLIMITに置き換えられています。 エラー処理は同じですね。
大体予想通りになっていると思います。
OracleとPostgreSQLの実行例比較
各プロシージャを実行した結果も比較してみましょう。エラー処理もあるので、正常に動いたとき、エラーを発生させたとき、それぞれの結果を見てみます。
大体予想通り・・・おっと!「データがないとき」の結果が違いますね・・・!
SELECTの件数が0件の場合、Oracleでは「ORA-01403: no data found」のエラーが出ていますが、PostgreSQLでは特にエラーとして処理されないようです。
OracleのPL/SQLの中でSELECTをしている場合は、明示的にエラーハンドリングを追加する必要があるということですね。
■データがあるとき(Oracle)
SQL> variable vv number;
SQL> execute test_proc(200000,20,:vv);
EMPNO : 3
ENAME : KIJI
PL/SQL procedure successfully completed.
SQL> print vv
VV
----------
275000
■データがあるとき(PostgreSQL)
postgres=# select test_proc(200000,20);
NOTICE: EMPNO : 3
CONTEXT: PL/pgSQL function test_proc(bigint,bigint) line 17 at RAISE
NOTICE: ENAME : KIJI
CONTEXT: PL/pgSQL function test_proc(bigint,bigint) line 18 at RAISE
test_proc
-----------
275000
(1 行)
■データがないとき(エラー処理)Oracle
SELECTの結果が0件の場合、エラー処理が行われる。
SQL>set serveroutput on
SQL> execute test_proc(200000,10);
ERROR : 100 : ORA-01403: no data found
PL/SQL procedure successfully completed.
■データがないとき(エラー処理)PostgreSQL
SELECTの結果が0件であっても、明示的にハンドリングをしない限り、エラーにはならない。(SELECTの結果が0件の場合のハンドリング例は、「ストアドパッケージ」の移行例に記載)
postgres=# select test_proc(200000,50);
NOTICE: EMPNO : <NULL>
CONTEXT: PL/pgSQL function test_proc(bigint,bigint) line 17 at RAISE
NOTICE: ENAME : <NULL>
CONTEXT: PL/pgSQL function test_proc(bigint,bigint) line 18 at RAISE
test_proc
-----------
(1 行)
ストアドパッケージ
次に、ストアドパッケージを移行してみましょう。
移行例のストアドパッケージには、ストアドプロシージャとストアドパッケージをひとつずつ持たせています。ストアドプロシージャはt_empテーブルへのデータ登録を、ストアドファンクションは入力した「社員名」の「給料」を返却します。
OracleのDDL
ストアドパッケージはパッケージに含むプロシージャ名などを定義する「仕様部」と、ストアドプロシージャ「本体部」で構成されます。
CREATE OR REPLACE PACKAGE test_pkg AS
PROCEDURE insert_emp ( empno_in IN NUMBER, ename_in IN VARCHAR2, sal_in IN NUMBER, deptno_in IN NUMBER );
FUNCTION get_emp_sal ( ename_in IN VARCHAR2 ) RETURN NUMBER;
END test_pkg;
/
CREATE OR REPLACE PACKAGE BODY test_pkg AS
PROCEDURE insert_emp ( empno_in IN NUMBER, ename_in IN VARCHAR2, sal_in IN NUMBER, deptno_in IN NUMBER ) AS
BEGIN
INSERT INTO t_emp VALUES ( empno_in, ename_in, sal_in, deptno_in);
COMMIT;
END insert_emp;
FUNCTION get_emp_sal ( ename_in IN VARCHAR2 ) RETURN NUMBER as
sal_out NUMBER := 0;
BEGIN
SELECT sal INTO sal_out FROM t_emp
WHERE ename LIKE '%' || ename_in || '%';
RETURN sal_out;
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('ENAME NOT FOUND.');
RETURN sal_out;
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('ERROR : '|| SQLCODE || ' : ' || SQLERRM );
RETURN sal_out;
END get_emp_sal;
END test_pkg;
/
こちらのポイントはパッケージがどのようにコンバートされるかです。
PostgreSQL変換後のDDL
パッケージはスキーマとして定義されました。SQL空呼び出す場合は「パッケージ名.関数名」から「スキーマ名.関数名」になるだけなので記述内容は同じです。
ただ、同一名称のスキーマがすでに使用されている場合は競合するので、その場合はスキーマ名を変える必要があります。
CREATE SCHEMA test_pkg;
CREATE OR REPLACE FUNCTION test_pkg.insert_emp ( empno_in bigint, ename_in text, sal_in bigint, deptno_in bigint ) RETURNS VOID AS $body$
BEGIN
BEGIN
INSERT INTO t_emp VALUES ( empno_in, ename_in, sal_in, deptno_in);
END;
END;
$body$
LANGUAGE PLPGSQL
SECURITY DEFINER
VOLATILE;
CREATE OR REPLACE FUNCTION test_pkg.get_emp_sal ( ename_in text ) RETURNS bigint AS $body$
DECLARE
sal_out bigint := 0;
BEGIN
SELECT sal INTO STRICT sal_out FROM t_emp
WHERE ename LIKE '%' || ename_in || '%';
RETURN sal_out;
EXCEPTION
WHEN NO_DATA_FOUND THEN
RAISE NOTICE 'ENAME NOT FOUND.';
RETURN sal_out;
WHEN OTHERS THEN
RAISE NOTICE 'ERROR : % : %', SQLSTATE, SQLERRM;
RETURN sal_out;
END;
$body$
LANGUAGE PLPGSQL
SECURITY DEFINER
;
ora2pgでコンバートしたDDLを結果をPostgreSQL上で実行したところ、いくつかエラーが出たので修正しました。
まず、「test_pkg.insert_emp」の最終行を「STABLE」→「VOLATILE」に変更しています。また、「INSERT」文の前後に「BEGIN」「END;」を付与して修正しています。
OracleとPostgreSQLの実行例比較
■データがあるとき(Oracle)
SQL> select test_pkg.get_emp_sal('NEKO') from dual;
TEST_PKG.GET_EMP_SAL('NEKO')
----------------------------
200000
■データがあるとき(PostgreSQL)
postgres=# select test_pkg.get_emp_sal('NEKO');
get_emp_sal
-------------
200000
(1 行)
■データが無いとき(Oracle)
SQL> select test_pkg.get_emp_sal('SARU') from dual;
TEST_PKG.GET_EMP_SAL('SARU')
----------------------------
0
ENAME NOT FOUND.
■データが無いとき(PostgreSQL)
postgres=# select test_pkg.get_emp_sal('SARU');
NOTICE: ENAME NOT FOUND.
get_emp_sal
-------------
0
(1 行)
■データ登録(Oracle)
SQL> exec test_pkg.insert_emp(101,'TORI',180000,30);
PL/SQL procedure successfully completed.
SQL> select * from t_emp;
EMPNO ENAME SAL DEPTNO
---------- -------------------- ---------- ----------
1 KUMA 150000 10
2 NEKO 200000 10
3 KIJI 300000 20
4 TORA 250000 20
101 TORI 180000 30
■データ登録(PostgreSQL)
postgres=# select test_pkg.insert_emp(101,'TORI',180000,30);
insert_emp
------------
(1 行)
postgres=# select * from t_emp;
empno | ename | sal | deptno
-------+-------+--------+--------
1 | KUMA | 150000 | 10
2 | NEKO | 200000 | 10
3 | KIJI | 300000 | 20
4 | TORA | 250000 | 20
101 | TORI | 180000 | 30
(5 行)
ストアドプロシージャ移行のまとめ
今回はOracleのストアドプロシージャ、ストアドパッケージの移行についてora2pgの実力を試してみました。
ストアドプロシージャの移行は難易度が高いですが、ora2pgを用いることで、移行作業を大幅に軽減できることが確認できました。
ただし、コンバート後のPostgreSQL用のDDLは完璧なものではないため、PL/SQLからPL/pgSQLに変換したあとに、デバッグ作業が必要です。
ストアドプロシージャの移行における、ora2pgの使いどころと注意点は以下です。
- OracleとPostgreSQLの大枠の変換に用いる。
- 変換は完全ではないので、人による修正が必要。
- 全て関数にコンバートされるので、呼び出し元の修正が必要。
それでもPL/SQLの構文を一からPL/pgSQL構文へ手動で修正するよりは難易度は低くなるため、移行時には積極的に使って行くことが有効です。
NTTテクノクロスでは、OracleからPostgreSQLへの移行や、運用支援のサービスを提供しています。

Oracle、PostgreSQL 両方のデータベースを熟知。特にOracle→PostgreSQLへのデータベース移行が得意。 初めて触ったデータベースはOracle8i。のちにOracle10g RACを使ったシステムを構築し、 オラクルマスター10g GOLDを取得するも、いつの間にかPostgreSQLひとすじに。 当然のようにネコ好き。