Windows下HGDB通过插件oracle_fdw访问oracle数据库

作者：瀚高PG实验室 （Highgo PG Lab）
环境：win10 HGDB4.3.2
下载对应版本的插件，下载地址https://github.com/laurenz/oracle_fdw/releases。HGDB4.3.2对应pg9.5，因此下载的版本是：oracle_fdw-2.0.0-pg95-win64.zip
安装oracle_fdw插件
将下载后的压缩包解压到hgdb的安装目录，然后在hgdb中安装扩展操作如下：

highgo=# create extension oracle_fdw;
CREATE EXTENSION
highgo=# \dx
                                    已安装扩展列表
     名称 | 版本 | 架构模式 | 描述
--------------+------+------------+----------------------------------------------------
 oracle_fdw | 1.1 | public | foreign data wrapper for Oracle access
 pgstattuple | 1.3 | public | show tuple-level statistics
 plpgsql | 1.0 | pg_catalog | PL/pgSQL procedural language
 postgres_fdw | 1.0 | public | foreign-data wrapper for remote PostgreSQL servers
(4 行记录)

通过oracle_fdw访问oracle

--创建访问oracle的连接
create server oracle foreign data wrapper oracle_fdw options(dbserver '127.0.0.1:1521/orcl');
--授予highgo用户访问权限
grant usage on foreign server oracle to highgo;
--创建到oracle的映射
create user mapping for highgo server oracle options(user 'SCOTT',password 'tiger');
--创建需要访问的oracle中对应表的结构
create foreign table emptest(
EMPNO NUMBER(4),
ENAME VARCHAR(10),
JOB VARCHAR(9),
MGR NUMBER(4),
HIREDATE DATE,
SAL NUMBER(7,2),
COMM NUMBER(7,2),
DEPTNO NUMBER(2)
) server oracle options(schema 'SCOTT',table 'EMP');
--在hgdb中访问oracle的表
highgo=# select * from emptest;
 empno | ename | job | mgr | hiredate | sal | comm | deptno
-------+--------+-----------+------+------------+---------+---------+--------
  7369 | SMITH | CLERK | 7902 | 1980-12-17 | 800.00 | | 20
  7499 | ALLEN | SALESMAN | 7698 | 1981-02-20 | 1600.00 | 300.00 | 30
  7521 | WARD | SALESMAN | 7698 | 1981-02-22 | 1250.00 | 500.00 | 30
  7566 | JONES | MANAGER | 7839 | 1981-04-02 | 2975.00 | | 20
  7654 | MARTIN | SALESMAN | 7698 | 1981-09-28 | 1250.00 | 1400.00 | 30
  7698 | BLAKE | MANAGER | 7839 | 1981-05-01 | 2850.00 | | 30
  7782 | CLARK | MANAGER | 7839 | 1981-06-09 | 2450.00 | | 10
  7788 | SCOTT | ANALYST | 7566 | 1987-04-19 | 3000.00 | | 20
  7839 | KING | PRESIDENT | | 1981-11-17 | 5000.00 | | 10
  7844 | TURNER | SALESMAN | 7698 | 1981-09-08 | 1500.00 | 0.00 | 30
  7876 | ADAMS | CLERK | 7788 | 1987-05-23 | 1100.00 | | 20
  7900 | JAMES | CLERK | 7698 | 1981-12-03 | 950.00 | | 30
  7902 | FORD | ANALYST | 7566 | 1981-12-03 | 3000.00 | | 20
  7934 | MILLER | CLERK | 7782 | 1982-01-23 | 1300.00 | | 10
(14 行记录)

查询已创建的到oracle的连接

highgo=# SELECT * from pg_user_mappings;
 umid | srvid | srvname | umuser | usename | umoptions
-------+-------+---------+--------+---------+-----------------------------
 24689 | 24688 | oracle | 10 | highgo | {user=SCOTT,password=tiger}
 24716 | 24715 | orcl | 10 | highgo | {user=SCOTT,password=tiger}
(2 行记录)

如何删除创建的对象

drop foreign table emptest;
drop user mapping for highgo server oracle;
drop server oracle;

遇到的问题
在hgdb中创建对应的表时，使用的是hgdb中的数据类型，如果写成oracle中的数据类型，创建过程中不会报错，但在访问时会报错，如下例子所示：

highgo=# create foreign table emp11(
highgo(# EMPNO NUMBER(4),
highgo(# ENAME VARCHAR2(10),
highgo(# JOB VARCHAR2(9),
highgo(# MGR NUMBER(4),
highgo(# HIREDATE DATE,
highgo(# SAL NUMBER(7,2),
highgo(# COMM NUMBER(7,2),
highgo(# DEPTNO NUMBER(2)
highgo(# ) server oracle options(schema 'SCOTT',table 'EMP');
CREATE FOREIGN TABLE
highgo=# select * from emp11;
错误: 22023: input value length is 5; too long for type varchar2(4)
背景: converting column "ename" for foreign table scan of "emp11", row 2

创建外部表时，oracle的用户名及表名必须为大写，否则访问时会报如下错误：

highgo=# select * from emptest;
错误: HV00R: Oracle table "SCOTT"."emp" for foreign table "emptest" does not exist or does not allow read access
描述: ORA-00942: table or view does not exist
提示: Oracle table names are case sensitive (normally all uppercase).