在PostgreSQL中使用jdbc_fdw访问Oracle

Posted on 2014-11-11 21:39:07 by osdba

本文讲述如何在PostgreSQL中使用jdbc_fdw访问Oracle上的数据。

1. 安装jdbc_fdw

可以参照:jdbc_fdw in github

需要先安装jdbc_fdw,到网站上: jdbc_fdw in github

cd /usr/lib
ln -sf /usr/lib/jvm/jdk1.7.0_71/jre/lib/amd64/server/libjvm.so libjvm.so
cd <ostgresql-src_dir>/contrib
git clone https://github.com/atris/jdbc_fdw.git
make
make insttall

2. 创建jdbc_fdw外部表

假设在Oracle数据库中有scott用户,密码为tiger,其中有表dept,下面我把在PostgreSQL建张外部表,可以访问到Oracle中的这张dept表上:

在psql中:

CREATE EXTENSION jdbc_fdw;

CREATE SERVER jdbc_serv4 FOREIGN DATA WRAPPER jdbc_fdw OPTIONS(
  drivername 'oracle.jdbc.driver.OracleDriver',
  url 'jdbc:oracle:thin:@localhost:1521:oratest',
  querytimeout '15',
  jarfile '/home/osdba/workspace/jdbclib/ojdbc6.jar',
  maxheapsize '600'
);

CREATE USER MAPPING FOR user
    SERVER jdbc_serv4
    OPTIONS(username 'scott',password 'tiger');

CREATE FOREIGN TABLE dept(deptno smallint, dname varchar(14), loc varchar(13)) SERVER jdbc_serv4 OPTIONS (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中的sqlplus中往dept中插入一条数据:

oracle@osdba-work:~$ sql scott/tiger

SQL*Plus: Release 11.2.0.1.0 Production on Tue Nov 11 20:46:07 2014

Copyright (c) 1982, 2009, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, Oracle Label Security, OLAP and Oracle Database Vault options

SQL> INSERT INTO "SCOTT"."DEPT" (DEPTNO, DNAME, LOC) VALUES ('50', 'tangtest', 'BEIJING')
  2  ;

1 row created.

SQL> commit;

Commit complete.

然后再到psql中,就可以看到新插入的数据了:

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