use oracle_fdw create oracle foreign table in PostgreSQL-CSDN博客

虽然测试查询ORACLE表没有成功。不过还是很令人期待。下面是测试过程.

2011-9-3放出的一个BETA版本。

Foreign Data Wrapper for Oracle: beta release

可以在pgfoundry去下载源码。

我这里的测试环境 :

PostgreSQL9.1rc1 (编译安装的)

RHEL5.6 64位

编译时文件oracle_utils.c爆出了一些错误:

代码里有几行:

(OCINumber *)value = number;

(OCIDateTime *)param->value = timest;

(OCIDateTime **)value = &timest;

报错

error: invalid lvalue in assignment

后来注释这几行或者改成如下后，都可以编译通过。不过在实际的对foreign table 进行SQL查询出现了问题。查询SQL时，ERROR: invalid memory alloc request size 18446744073709551505

value = (OCINumber *)number;

param->value = (OCIDateTime *)timest;

value = (OCIDateTime **)&timest;

下面是整个编译过程:

1. 需要Oracle客户端,oracle sdk,oracle instant client.

我这里使用的是Oracle 11.2.0.2 64位版本.
instantclient-basic-linux-x86-64-11.2.0.2.0.zip
解压缩后放到$ORACLE_HOME/oci/include
instantclient-sdk-linux-x86-64-11.2.0.2.0.zip
解压缩后放到$ORACLE_HOME/sdk

2. 环境变量文件 .bash_profile

export PS1="$USER@`/bin/hostname -s`-> "

export PGPORT= 5432

export PGDATA=/data1/pg_root

export PGARCHIVE=/opt/pgdata/pg_arch

export LANG=en_US.utf8

export PGHOME=/opt/pgsql

export ORACLE_HOME=/opt/oracle/product/11.2.0.2/db_1

export LD_LIBRARY_PATH=$ORACLE_HOME/lib:$ORACLE_HOME/lib32:$PGHOME/lib:/lib64:/usr/lib64:/usr/local/lib64:/lib:/usr/lib:/usr/local/lib

export DATE=`date +"%Y%m%d%H%M"`

export PATH=$PGHOME/bin:$PATH:/opt/pgbouncer/bin:.

export MANPATH=$PGHOME/share/man:/opt/pgbouncer/share/man:$MANPATH

3. chown -R postgres:postgres /opt/oracle

4. 把下载到的源码文件放到 /opt/soft_bak/postgresql-9.1rc1/contrib/

5. su - root

生成环境:

. /home/postgres/.bash_profile

修改oracle_utils.c文件(第1521,1551,1553行)

value = (OCINumber *)number;

param->value = (OCIDateTime *)timest;

value = (OCIDateTime **)&timest;

编译安装

cd /opt/soft_bak/postgresql-9.1rc1/contrib/oracle_fdw

make install

6. 编译通过后，到数据库新建extension,

psql -h 127.0.0.1 digoal postgres

create extension oracle_fdw;

相当于执行:

CREATE FUNCTION oracle_fdw_handler() RETURNS fdw_handler

AS 'MODULE_PATHNAME'

LANGUAGE C STRICT;

COMMENT ON FUNCTION oracle_fdw_handler()

IS 'Oracle foreign data wrapper handler';

CREATE FUNCTION oracle_fdw_validator(text[], oid) RETURNS void

AS 'MODULE_PATHNAME'

LANGUAGE C STRICT;

COMMENT ON FUNCTION oracle_fdw_validator(text[], oid)

IS 'Oracle foreign data wrapper options validator';

CREATE FUNCTION oracle_close_connections() RETURNS void

AS 'MODULE_PATHNAME'

LANGUAGE C STRICT;

COMMENT ON FUNCTION oracle_close_connections()

IS 'closes all open Oracle connections';

CREATE FOREIGN DATA WRAPPER oracle_fdw

HANDLER oracle_fdw_handler

VALIDATOR oracle_fdw_validator;

COMMENT ON FOREIGN DATA WRAPPER oracle_fdw

IS 'Oracle foreign data wrapper';

7. 创建foreign server

digoal=# create server ora foreign data wrapper oracle_fdw options (dbserver '//xxx.xxx.xxx.xxx:1521/$sid');

create user mapping for digoal server ora options (user '$username',password '$password');

创建foreign table (ORACLE用户需要有查询v$sql(当plan_costs='true')和目标表的权限)

create FOREIGN table tbl_fdw_test (id varchar,appid numeric,VIDEONAME varchar,CLASSID varchar,ORDERNUM numeric,CREATETIME timestamp without time zone ,groupid varchar) server ora options (table '$oracle_tablename',schema '$oracle_schemaname',plan_costs 'true/false');

8. 赋权

digoal=# grant all on tbl_fdw_test to digoal;

9. 查询测试

digoal=> select id from tbl_fdw_test limit 1;

ERROR: invalid memory alloc request size 18446744073709551505

还是值得期待的，PostgreSQL的融合能力越来越强大了.

【参考】

http://pgfoundry.org/docman/view.php/1000600/13802/README.txt
http://www.oracle.com/technetwork/topics/linuxx86-64soft-092277.html