本文共 3574 字，大约阅读时间需要 11 分钟。

作者：瀚高PG实验室 （Highgo PG Lab）

highgo=# create extension oracle_fdw;CREATE EXTENSIONhighgo=# \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;

遇到的问题

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 TABLEhighgo=# 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).