2
4

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?

More than 3 years have passed since last update.

ora2pgを使用してOracleのSQLをPostgreSQLへ変換する

Last updated at Posted at 2020-08-30

はじめに

ora2pgはOracle(とMySQL)からPostgreSQLへの移行を支援するためのツールです。
今回はSQLの変換を試してみます。

以下の投稿で構築した環境を利用します。

OracleのSQLをPostgreSQLへ変換する

input.sqlを準備して、ora2pgコマンドでoutput.sqlに変換後のSQLを出力します。

# ora2pg -c config/ora2pg.conf -i input.sql -o output.sql -t QUERY

DUAL表

DUAL表を定義するという対応もあります。

[変換前]
select 1 from dual
[変換後]
select 1

外部結合

全てLEFT OUTER JOINになりました。

[変換前]
select * from test01 t1, test02 t2 where t1.id(+) = t2.id;
[変換後]
select * FROM test02 t2
LEFT OUTER JOIN test01 t1 ON (t2.id = t1.id);
[変換前]
select * from test01 t1, test02 t2 where t1.id = t2.id(+);
[変換後]
select * FROM test01 t1
LEFT OUTER JOIN test02 t2 ON (t1.id = t2.id);

ROWNUM

[変換前]
select * from test01 where rownum <= 10;
[変換後]
select * from test01 LIMIT 10;

MINUS

[変換前]
select * from test01 minus select * from test02;
[変換後]
select * from test01 EXCEPT select * from test02;

シーケンス

[変換前]
select seq_test.nextval from dual;
[変換後]
select nextval('seq_test');

日付

[変換前]
select current_timestamp from dual;
select current_date from dual;
select systimestamp from dual;
select sysdate from dual;
select localtimestamp from dual;
[変換後]
select current_timestamp;
select current_date;
select CURRENT_TIMESTAMP;
select LOCALTIMESTAMP;
select localtimestamp;

正規表現

[変換前]
select * from test01 where regexp_like(txt, '[a-zA-Z]');
[変換後]
select * from test01 where txt ~ '[a-zA-Z]';

日付計算

[変換前]
select add_months(sysdate, 1) from dual;
[変換後]
select LOCALTIMESTAMP + '1 month'::interval;

DECODE関数

[変換前]
select
  decode(txt,'1', '01', '2', '02')
from
  test01 t;
[変換後]
select
  CASE WHEN txt='1' THEN  '01' WHEN txt='2' THEN  '02' END
from
  test01 t;

NVL関数

[変換前]
select nvl(abc, 0) from test;
[変換後]
select coalesce(abc, 0) from test;

from句の副問い合わせ

[変換前]
select * from (select * from test01);
[変換後]
select * from (select * from test01) alias0;

Oraface

Orafaceの関数を使用する場合はora2pg.confの設定を修正します。有効にするとadd_month等の関数は変換の対象外になります。

# Orafaceを使用する場合は"1"に設定(デフォルト"0")
USE_ORAFCE      1

その他

今回は紹介しませんが、ora2pgの他にもAWS Schema Conversion Tool (AWS SCT) を使用してSQLを変換することができます。
SCTの場合は独自のOracle互換関数を使用するように変換されます。

参考

2
4
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
2
4

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?