免费注册 查看新帖 |

Chinaunix

  平台 论坛 博客 文库
最近访问板块 发新帖
查看: 1244 | 回复: 0

Postgressql dblink access Oracle [复制链接]

论坛徽章:
0
发表于 2011-12-20 09:46 |显示全部楼层
How to install and configure DBI-Link to join Oracle tables from PostgreSQL on Debian GNU/Linux

If you want to join Oracle tables from PostgreSQL on Debian GNU/Linux, you can use DBI-Link.

Also, you can use PostgreSQL queries to access Oracle tables as local schemas.

At some deployment scenarios, one may have to access Oracle tables transparently using PostgreSQL functions, without installing other language specific libraries.

There is a caveat for this version:

  • Each query will load data from remote Oracle server to the local PostgreSQL shadow table.
  • You can execute remotely on Oracle backend using remote_select as described here.
Installation

You will need the alien package to convert rpm files to deb files.

You will need to have an Oracle account to download the Oracle Instant Client Basic and Oracle Instant Client Devel rpm files suitable for your Oracle backend version from the vendor site.

At this example, we will use

oracle-instantclient-basic-10.2.0.3-1.i386.rpm

oracle-instantclient-devel-10.2.0.3-1.i386.rpm

Convert *.rpm to *.deb

The alien package makes a brute conversion from rpm to deb, without careful checks. You must to test the results on a disposable laboratory machine before deploying at a valuable server.

debian:~# alien --to-deb oracle-instantclient-basic-10.2.0.3-1.i386.rpm debian:~# dpkg --install oracle-instantclient-basic_10.2.0.3-2_i386.deb debian:~# alien --to-deb oracle-instantclient-devel_10.2.0.3-2_i386.rpm debian:~# dpkg --install oracle-instantclient-devel_10.2.0.3-2_i386.debInstall dbi-link and dependencies

The libaio1 is needed by the oracle programs.

debian:~# apt-get update debian:~# apt-get install libdbd-oracle-perl dbi-link libaio1Configure Oracle Instant Client BasicPrepare the tnsnames.oradebian:~# mkdir -p /usr/lib/oracle/10.2.0.3/client/network/admin/ debian:~# nano /usr/lib/oracle/10.2.0.3/client/network/admin/tnsnames.ora /usr/lib/oracle/10.2.0.3/client/network/admin/tnsnames.ora

A very simple example below, adapt it to your Oracle available database service.

your_symbolic_service_name = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP) (HOST = your_oracle_server_address)(PORT = 1521)) ) (CONNECT_DATA = (SID = your_available_oracle_sid)) )/usr/lib/oracle/10.2.0.3/client/network/admin/sqlnet.ora

We will not use sqlnet.ora at this example. Please read the useful links section at the end of article.

/etc/ld.so.conf.d/oracle.conf/usr/lib/oracle/10.2.0.3/client/libdebian:~# nano /etc/ld.so.conf.d/oracle.conf debian:~# ldconfigConfiguring DBI-Link for PostgreSQL access Oracle tables

The step for creating the accessor_functions may be lenghty as it downloads all tables informations from the Oracle backend.

Your local schema MUST NOT EXISTS before creating the accessor functions. Read the dbi-link debian package documentation on your disk.

There are paremeters you must edit to your needs, like the oracle server port, and we use an example query.

Notably, the connection string:

'dbi:Oracle:host=your_oracle_server_address;sid=your_available_oracle_sid;port=1521',

could be something like:

'dbi:Oracle:user=your_available_oracle_username;host=your_oracle_server_address;sid=your_available_oracle_sid;port=1521',

or even could be:

'dbi:Oracle:database=your_oracle_available_database;host=your_oracle_server_address;sid=your_available_oracle_sid;port=1521',

It depends on your user access permissions and Oracle configuration files. Be careful with the syntax.

When it enters psql the first time, one must update the pg_settings for the dbi-link too. The sql is at the Debian dbi-link package documentation.

debian:~# su postgres postgres@debian:/root$ createdb teste postgres@debian:/root$ createlang plperlu teste postgres@debian:/root$ psql -d teste -f /usr/share/dbi-link/dbi_link.sql postgres@debian:/root$ psql teste Bem vindo ao psql 8.3.14, o terminal iterativo do PostgreSQL. Digite: \copyright para mostrar termos de distribuição \h para ajuda com comandos SQL \? para ajuda com comandos do psql \g ou terminar com ponto-e-vírgula para executar a consulta \q para sair teste=# UPDATE pg_catalog.pg_settings SET setting = CASE WHEN 'dbi_link' = ANY(string_to_array(setting, ',')) THEN setting ELSE 'dbi_link,' || setting END WHERE name = 'search_path' ; teste=# SELECT make_accessor_functions( 'dbi:Oracle:host=your_oracle_server_address;sid=your_available_oracle_sid;port=1521', 'your_available_oracle_username', 'your_available_oracle_password', '--- AutoCommit: 1 RaiseError: 1 ', NULL, NULL, NULL, 'your_local_schema' ); teste=# \d teste=# select count(*) from your_local_schema."VW_SISCOR_02"; teste=# select count(*) from your_local_schema."VW_LOTACAO"; teste=# select count(*) from your_local_schema."VW_LOTACAO" where "UF" = 'RS'; teste=# \q postgres@debian:/root$ exit exit debian:~# Useful Links
您需要登录后才可以回帖 登录 | 注册

本版积分规则 发表回复

  

北京盛拓优讯信息技术有限公司. 版权所有 京ICP备16024965号-6 北京市公安局海淀分局网监中心备案编号:11010802020122 niuxiaotong@pcpop.com 17352615567
未成年举报专区
中国互联网协会会员  联系我们:huangweiwei@itpub.net
感谢所有关心和支持过ChinaUnix的朋友们 转载本站内容请注明原作者名及出处

清除 Cookies - ChinaUnix - Archiver - WAP - TOP