PostgreSQLのOracle Foreign Data WrapperをWindowsで使う

はじめに

Oracle DatabaseにWindows上のMicrosoft ExcelからVBAでアクセスしたい。 個々の端末にODBCデータソースを設定するのはスクリプトで可能かもしれないが、面倒なのでADOとOLEDBの組み合わせでアクセスすることを考えている。 しかし、Oracle Instant Clientをアクセスする全ての端末にインストールするのは現実的ではない場合があると思う。 この場合のアクセスは読み取り専用であって、select文以外を処理できる必要はない。

そこで、PostgreSQLのOracle Foreign Data Wrapper Extensionを使って、PostgreSQL上にOracleのテーブルとビューを透過的に作成して、PostgreSQLのOLEDB Providerを端末にインストールし、OLEDBで参照するようにしたい。 つまり、Oracle同士でのDATABASE LINKのようなことをやりたい。

今回は理由があって最終的な環境では、サーバーはWindows Server 2012 R2 Standard (64-bit)で決まっている。 なので、全てWindows上で処理しなくてはならない。 空いているWindows環境がこれしかないと言う理由で、Windows 10 Home (64-bit)を使うことにした。 Oracle DatabaseとしてもOracle Database Express Edition 11g Release 2を新規にローカルにインストールして使うことにした。 PostgreSQLは9.5.2を使用し、これも新規にローカルにインストールして使う。

Oracle Databaseの準備

この記事を執筆している時点では、最新のOracle Express Editionは、Oracle Database Express Edition 11g Release 2である。 12系列のExpress Editionは存在していないようである。 Oracle Express Edition 11g Release 2Downloadsページより、x86_64のWindows用のバイナリーと言うことで、Oracle Database Express Edition 11g Release 2 for Windows x64をダウンロードしておく。 ダウンロードされたファイルは、OracleXE112_Win64.zipである。 これを展開しておき、DISK1ディレクトリーにあるsetup.exeを実行する。 特に既定値から変更することはなくインストールした。

sqlplusがインストールされるので起動させ、

> conn scott/tiger

のようにして、scott/tigerでログインし、サンプルデータを使おうと思ったが、そもそもscottユーザーが存在していない。 ウェブで検索すると、Oracle Express Edition 11g Release 2にはscottユーザーは存在せず、utlsampl.sqlを実行することで、旧来のOracle Database同様にこのデータが用意できるようである。 utlsampl.sqlで、C:\oraclexeディレクトリー以下を検索すると、

C:\oraclexe\app\oracle\product\11.2.0\server\rdbms\admin\utlsampl.sql

が存在するので、以下のようにして実行しておく。

> conn / as sysdba
> @C:\oraclexe\app\oracle\product\11.2.0\server\rdbms\admin\utlsampl.sql

再度sqlplusでscott/tigerでログインしてみると、以下のようになる。

> conn scott/tiger
> select table_names from user_tables;
(DEPT、EMP、BONUS、SALGRADEが存在することを確認)
> desc emp;
(EMPの定義が表示できることを確認)
> select * from emp;
(EMPの内容が表示できることを確認)

最終的な環境では、Oracle Database上のビューをPostgreSQL経由で参照したいので、ビューの例を一つ作成しておく。

> conn / as sysdba
> create view scott.test_view as select * from scott.dept;
> select view_name from user_view;
(test_viewが表示されることを確認)

PostgreSQLの準備

PostgreSQL 9.5.2を使うことにしているので、EnterpriseDBのWebサイトより、postgresql-9.5.2-1-windows-x64.exeをダウンロードする。 インストールの途中では既定値からは変更はしなかった。

Oracle Foreign Data Wrapperのインストール

githubのoracle_fdwのリリースページより、今日時点の最新版であるバージョン1.4.0をダウンロードする。PostgreSQL 9.5用と言うことで、oracle_fdw-1.4.0-pg95-win64.zipをダウンロードする。 ZIPファイルを展開し、その中にあるlib、share、symbolsの3つのディレクトリーを、C:\Program Files\PostgreSQL\9.5にコピーする。既にそれらのディレクトリーは存在しているので、コピーするファイルを追加するようにする。 ここで、PostgreSQLのインストールに伴って作成されたスタートメニューのPostgreSQL 9.5というグループにあるReload Configurationを管理者として実行する。 これによって、oracle_fdwが認識されるようになるっぽい。

Oracle Foreign Data Wrapperの設定

この手順は、Connecting your PostgreSQL instance to an Oracle database – The PostgreSQL 9.5 wayが非常に参考になった。

PostgreSQL側にscottスキーマを作成し、Oracle Databaseのscottスキーマのテーブルとビューへの参照をそこに作成する方針である。

まずは、Oracle Foreign Data Wrapperが使用できる状態になっているか確認する。

# create extension oracle_fdw;

これが正常に作成でき、

# \dew

でoracle_fdwが表示されれば、Oracle Foreign Data Wrapperは使用できるようになっている。 Ocacle Express Edition 11g Release 2がインストールされていない Oracle Instant Clientだけがインストールされている環境では、 oci.dllが存在するディレクトリーがPATH環境変数に含まれていない場合が、一度あったので、PATH環境変数をまずは確認したら良いと思う。 他にもインストール時にコピーするファイルが不足していると、その旨のエラーメッセージが表示される。

以下のようにして、PostgreSQLでscottスキーマを作成する。ここにOracle Databaseのscottスキーマを参照する内容を展開する。

# create schema scott;

次に、参照するサーバーを設定する。 今回はOracle Database Express Edition 11g Release 2を参照するので、XEで識別される。 Oracle Database Express Edition以外の場合には、ローカルのtnsnames.oraの内容を確認しておく。

# create server remote_db foreign data wrapper oracle_fdw options (dbserver 'XE' );

ここで、dbserverとするのが昔とは違っているようなので注意点である。

次にユーザー名の変換規則を設定する。 今回は、Oracle Database側はユーザー名scott、パスワードtigerである。PostgreSQL側は、ユーザー名postgresでログインしているものとする。

# create user mapping for postgres server remote_db options (user 'SCOTT', password 'tiger');

次にOracle Databaseのscottスキーマの内容を全てPostgreSQLのscottスキーマに展開する。

# import foreign schema "SCOTT" from server remote_db into scott;

以下のようにして、全てのテーブルとビューに対して参照できるようになっていることを確認する。

# set search_path='scott';
# \d
(テーブルとビューへの参照の一覧が表示される)

# \d+ emp
(Oracle DatabaseのEMPに対応する項目定義が表示される)

課題

良く分からないのは、マルチバイト文字についてである。 今回の環境では、Oracle DatabaseはAL32UTF8であり、PostgreSQL側はSJISだと思うのだが、Oracle Database側のsqlplusで登録した日本語のレコードは、PostgreSQLのpsqlでも文字化けせずに見えてしまった。 PostgreSQLに接続するNetBSDのターミナルがmltermでUTF-8で使っているせいかもしれない。 コマンドプロンプトでも文字化けしないのは良く分からないが…