在PostgreSQL中使用oracle_fdw訪問Oracle

標籤：

本文講述如何在PostgreSQL中使用oracle_fdw訪問Oracle上的資料。

1. 安裝oracle_fdw

可以參照：oracle_fdw in github

編譯安裝oracle_fdw之前，需要安裝Oracle的用戶端程式，通常可以安裝輕量級用戶端包：oracle instant client，為：oracle instant client

安裝oracle_fdw，可以到網站上: oracle_fdw in github](https://github.com/laurenz/oracle_fdw "")下載安裝包，或直接使用git clone下載原始碼：

cd <postgresql源碼目錄>/contribgit clone https://github.com/laurenz/oracle_fdw.git

在編譯安裝前，需要設定Oracle的環境變數，如在.bash_profile中增加：

export ORACLE_HOME=/opt/oracle/products/11.2.0export NLS_LANG=AMERICAN_AMERICA.AL32UTF8export LD_LIBRARY_PATH=$ORACLE_HOME/lib:$LD_LIBRARY_PATHexport PATH=${PATH}:${ORACLE_HOME}/bin/

然後進入到oracle_fdw源碼目錄進行編譯安裝：

makemake insttall

安裝完後，需要重啟資料庫才會生效。

2. 建立oracle_fdw外部表格

oracle_fdw是通過oci介面訪問Oracle了，所以需要配置$ORACLE_HOME/network/admin/tnsnames.ora，內容如下：

oratest =  (DESCRIPTION =    (ADDRESS_LIST =        (ADDRESS = (PROTOCOL = TCP)(HOST = 127.0.0.1)(PORT = 1521))    )    (CONNECT_DATA =      (sid = oratest)      (SERVER = DEDICATED)    )  )

上面我們配置了一個串連串DSN為“oratest”，後面我們會用到這個串。配置完後，使用tnsping測試一下：

[email protected]:~$ tnsping oratestTNS Ping Utility for Linux: Version 11.2.0.1.0 - Production on 11-NOV-2014 22:28:06Copyright (c) 1997, 2009, Oracle.  All rights reserved.Used parameter files:Used TNSNAMES adapter to resolve the aliasAttempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 127.0.0.1)(PORT = 1521))) (CONNECT_DATA = (sid = oratest) (SERVER = DEDICATED)))OK (0 msec)

假設在Oracle資料庫中有scott使用者，密碼為tiger，其中有表dept，下面我把在PostgreSQL建張外部表格，可以訪問到Oracle中的這張dept表上：

在psql中，使用超級使用者：

CREATE EXTENSION oracle_fdw;CREATE SERVER oradb FOREIGN DATA WRAPPER oracle_fdw          OPTIONS (dbserver ‘oratest‘);GRANTUSAGEONFOREIGN SERVER oradb TO scott;

注意上面命令中“dbserver ‘oratest‘”中的"oratest"就是前面tnsnames中設定的TNSNAMES。 上面的SQL中把訪問Oracle外部服務的許可權賦給了使用者scott，現在使用scott使用者登入：

CREATEUSER MAPPING FORcurrent_user SERVER oradb          OPTIONS (user‘scott‘, password ‘tiger‘);CREATEFOREIGNTABLE dept(deptno smallint options(key‘true‘), dname varchar(14), loc varchar(13))    SERVER oradb OPTIONS (schema‘SCOTT‘, table‘DEPT‘);

測試一下，在psql中查詢dept:

osdba=# select * from dept; deptno |   dname    |   loc    --------+------------+----------     10 | ACCOUNTING | NEW YORK     20 | RESEARCH   | DALLAS     30 | SALES      | CHICAGO     40 | OPERATIONS | BOSTON(4 rows)

可以看出已查詢到資料了，oracle_fdw外部表格也可以支援插入、更新、刪除:

mydb=> insertinto dept values(50, ‘OSDBA‘, ‘HANGZHOU‘);INSERT01mydb=> select * from dept; deptno |   dname    |   loc    --------+------------+----------     10 | ACCOUNTING | NEW YORK     20 | RESEARCH   | DALLAS     30 | SALES      | CHICAGO     40 | OPERATIONS | BOSTON     50 | OSDBA      | HANGZHOU(5 rows)mydb=> update dept set loc=‘HZ‘where deptno=50;UPDATE1mydb=> select * from dept; deptno |   dname    |   loc    --------+------------+----------     10 | ACCOUNTING | NEW YORK     20 | RESEARCH   | DALLAS     30 | SALES      | CHICAGO     40 | OPERATIONS | BOSTON     50 | OSDBA      | HZ(5 rows)mydb=> deletefrom dept where deptno=50;DELETE1mydb=> select * from dept; deptno |   dname    |   loc    --------+------------+----------     10 | ACCOUNTING | NEW YORK     20 | RESEARCH   | DALLAS     30 | SALES      | CHICAGO     40 | OPERATIONS | BOSTON(4 rows)

在PostgreSQL中使用oracle_fdw訪問Oracle