Oracle Database Link to PostgreSQL Database

Today’ s topic made my week fun. I struggled a bit, but with a little help of my stackoverflow.com buds got it :). I will not bore you with all of the details. The error I got throughout the my work is about the database encoding. I was looking the wrong place from the beginning. I will share question link at the end. I hope you will not make same mistake(for those who don’ t know about encoding like me:). We have way to go:). )

First of all, my Oracle database character set is WE8ISO8859P9, in other words LATIN5. Therefore, my PostgreSQL database’ s should be configured according to this configuration. Otherwise, you will see “fıstıkçışahap” as “fıstıkçışahap” on your client.

In order create, PostgreSQL database with correct encoding we must do a preparation in advance. Our PostgreSQL database runs on Ubuntu 18.04.5 LTS (Bionic Beaver). Our system should know right locale settings, so we can create database with tr_TR.iso88599.

Remove the comment from /etc/locale.gen file for line that includes “tr_TR ISO-8859–9”(as root). After editing the file just the command below(as root):

root@hostname:~# locale-gen
Generating locales (this might take a while)…
 en_US.UTF-8… done
 tr_TR.ISO-8859–9… done
 tr_TR.UTF-8… done
Generation complete.

It will generate our locale. Now we can create our database with correct encoding.

CREATE DATABASE turkish_charset ENCODING=’LATIN5' LC_COLLATE=’tr_TR.iso88599' LC_CTYPE=’tr_TR.iso88599' TEMPLATE=template0;

Now, we can move on to Oracle side. My Oracle database runs on Oracle Linux Server release 7.7.

Installing required PostgreSQL ODBC drivers:

sudo apt-get install odbc-postgresql

Also, you need an ODBC driver manager. You can install it from http://www.unixodbc.org/ as they say in download page.

We are dealing with two ODBC files to provide heterogonous connection. First one /etc/odbc.ini file. This file contains:

[postgres]
Description = postgres
Driver = /usr/lib64/psqlodbc.so
Servername = $database_server
Username = $database_user
Password = $database_user_password
Port = 5432
Database = turkish_charset($postgresql_db_name)

My OS that Oracle database runs on is x64 based. Thus, I take into account driver as “Driver = /usr/lib64/psqlodbc.so”.

Second file, /etc/odbcinst.ini contains:

# Example driver definitions
# Driver from the postgresql-odbc package
# Setup from the unixODBC package
#[PostgreSQL]
[postgres]
Description = ODBC for PostgreSQL
Driver = /usr/lib64/psqlodbc.so
Driver64 = /usr/lib64/psqlodbc.so
Setup = /usr/lib64/libodbcpsqlS.so
Setup64 = /usr/lib64/libodbcpsqlS.so
FileUsage = 1

Like /etc/odbc.ini file I used x64 based libraries in this file. We can check our ODBC configuration:

Last login: Mon Jan 25 01:10:50 +03 2021
[oracle@hostname ~]$ isql -v postgres
+ — — — — — — — — — — — — — — — — — — — -+
| Connected! |
| |
| sql-statement |
| help [tablename] |
| quit |
| |
+ — — — — — — — — — — — — — — — — — — — -+
SQL>

Adding TNS entry to $ORACLE_HOME(database)/network/admin/tnsnames.ora

postgres =
 (DESCRIPTION =
 (ADDRESS = (PROTOCOL = TCP)(HOST = $database_server)(PORT = 1521))
 (CONNECT_DATA =
 (SERVER = DEDICATED)
 (SID = postgres)
 )
 (HS = OK)
 )

Creating init file. This file’ s name is crucial. Because, when you are calling another database via db link it first will go to TNS file. I don’ t know whether you point out the name in brackets (hint: [postgres]) or not. They are service names. They should same with TNS entry’ s SID value and our init file is also called initpostgres.ora . So, content of initpostgres.ora file:

# Linux
HS_FDS_CONNECT_INFO = postgres
HS_FDS_TRACE_LEVEL = ON
HS_FDS_SHAREABLE_NAME = /usr/lib64/libodbc.so
set ODBCINI=/etc/odbc.ini
set ODBCINSTINI=/etc/odbcinst.ini
HS_NLS_LENGTH_SEMANTICS=BYTE
NLS_LANGUAGE=AMERICAN
NLS_TERRITORY=AMERICA
NLS_CHARACTERSET=WE8ISO8859P9

Note: I did not mention where to create this init file. Because it is a heterogeneous source we need to create it in the $ORACLE_HOME/hs/admin directory. Thanks to Stephan.

HS_FDS_CONNECT_INFO parameter also gets the same value with the TNS entry’ s SID value.

Last file we should edit is $ORACLE_HOME(grid if it is in use, or database)/network/admin/listener.ora file. Added below lines:

#Added manually for dblink 
SID_LIST_LISTENER =
 (SID_LIST =
 (SID_DESC =
 (ORACLE_HOME=/u01/app/oracle/product/12.1/dbhome1)
 (ENV=”LD_LIBRARY_PATH=/u01/app/oracle/product/12.1/dbhome1/lib:/usr/local/unixodbc/lib:/usr/lib:/usr/lib64")
 (SID_NAME=postgres)
 (PROGRAM=dg4odbc)
 )
 )

SID_LIST_LISTENER’ s SID value also gets the same value with the TNS entry’ s SID value.

Last step, creating database link:

CREATE DATABASE LINK POSTGRELINK
 CONNECT TO “ $remote_postgresql_database_user”
 IDENTIFIED BY $remote_postgresql_database_user_password
 USING ‘postgres($TNS_ENTRY_NAME)’;

PostgreSQL side:

=# select * from test;
 username 
 — — — — — — — -
 fıstıkçışahap
(1 row)
postgres=#

Oracle side:

select * from “test”@postgrelink; — — due to case sensivity
fıstıkçışahap

My error: https://stackoverflow.com/questions/65861426/oracle-client-charset

Thanks:)!