虽然测试查询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 = ×t;
报错
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 **)×t;
下面是整个编译过程:
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
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 **)×t;
编译安装
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
http://www.oracle.com/technetwork/topics/linuxx86-64soft-092277.html
