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.deb
Install 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 libaio1
Configure Oracle Instant Client Basic
Prepare the tnsnames.ora
debian:~# 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
</pre>
/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/lib
debian:~# nano /etc/ld.so.conf.d/oracle.conf
debian:~# ldconfig
Configuring 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
http://keniamilene.wordpress.com/2008/05/29/instalando-e-implementando-dbi-link-no-postgresql/
http://thomas.eibner.dk/oracle/dbi/
https://github.com/davidfetter/DBI-Link
http://pgfoundry.org/forum/forum.php?set=custom&forum_id=161&style=nested&max_rows=25&submit=Change+View
http://www.mail-archive.com/dbi-link-general@pgfoundry.org/msg00055.html
http://pgfoundry.org/forum/forum.php?thread_id=2339&forum_id=162
http://pgfoundry.org/forum/forum.php?forum_id=161
http://alexzeng.wordpress.com/2010/10/16/perl-dbi-connect-oracle-example/
http://www.orafaq.com/wiki/Perl
http://www.easysoft.com/applications/oracle/database-gateway-dg4odbc.html
http://www.easysoft.com/developer/languages/perl/dbd_odbc_tutorial_part_1.html
http://search.cpan.org/~pythian/DBD-Oracle-1.27/Oracle.pm
http://pgfoundry.org/pipermail/dbi-link-general/2006-October/000051.html
http://www.mail-archive.com/pgbr-geral@listas.postgresql.org.br/msg18322.html
http://www.pgcon.org/2008/schedule/events/88.en.html
http://www.pythian.com/news/8369/dbdoracle-1-24-released/
http://www.php-pt.com/index.php?option=com_joomlaboard&Itemid=27&func=view&view=threaded&id=3652&catid=4
http://comments.gmane.org/gmane.comp.db.postgresql.dbi-link/65
http://www.eggheadcafe.com/software/aspnet/35697949/postgresql-to-oracle.aspx
http://www.devmedia.com.br/post-1945-Criando-Visoes--Views-no-Oracle.html
http://packages.debian.org/lenny/libdbd-oracle-perl
http://packages.debian.org/lenny/dbi-link
http://postgresql.1045698.n5.nabble.com/Visao-do-oracle-no-postgresql-td2049948.html
http://pgfoundry.org/docman/view.php/1000045/48/IMPLEMENTATION.txt
Oracle access files tnsnames.ora , sqlnet.ora
http://download.oracle.com/docs/cd/B28359_01/network.111/b28317/sqlnet.htm
http://profissionaloracle.com.br/blogs/rodrigoalmeida/tag/sqlnetora/
http://www.orafaq.com/wiki/Sqlnet.ora#Sample_sqlnet.ora_files
http://support.verio.com/documents/view_article.cfm?doc_id=1495
http://profissionaloracle.com.br/blogs/rodrigoalmeida/tag/tnsnamesora/
http://www.techforce.com.br/news/linux_blog/oracle_10g_r2_in_debian_domu_into_debian_dom0
http://www.techforce.com.br/news/linux_blog/instalar_oracle_client_no_debian_e_ubuntu
http://canonical.wordpress.com/2009/01/04/simple-oracle-instant-client-installation-on-debian-linux/
http://www.oracle.com/technetwork/topics/linuxsoft-082809.html
http://www.oracle.com/technetwork/database/10201linuxsoft-097986.html
Running Perl from PHP
http://pecl.php.net/package/perl
http://devzone.zend.com/article/1712
http://www.linuxjournal.com/article/9282?page=0,0
http://www.codingforums.com/showthread.php?t=189538
http://bytes.com/topic/php/answers/836-how-call-perl-function-php
http://theos.in/perl/how-to-call-perl-or-php-script-from-html-file/
Comentários
Postar um comentário