Oracle to PostgreSQL Data Migration Using HS Database Link (Step-by-Step)

 

📘 Introduction

In some enterprise environments, there’s a need to query or migrate data directly between Oracle and PostgreSQL without using ETL tools or third-party connectors.

Oracle’s Heterogeneous Services (HS) feature allows exactly that — it lets Oracle access non-Oracle databases such as PostgreSQL, MySQL, SQL Server, etc., using Oracle Database Gateway for ODBC (DG4ODBC).

In this article, we’ll walk through how to connect Oracle Database to PostgreSQL using HS, create a database link, and migrate data from Oracle to PostgreSQL seamlessly.

🧩 1. What Is Oracle Heterogeneous Services (HS)?

Heterogeneous Services is an Oracle feature that allows communication between Oracle and non-Oracle data sources through ODBC drivers.
It acts as a bridge layer — Oracle SQL queries are translated into the syntax of the remote system (PostgreSQL, in this case).

⚙️ 2. Prerequisites

Before starting, make sure you have:

Requirement	Description
Oracle Database	Version 12c or above
PostgreSQL Database	Version 12 or above
ODBC Driver	PostgreSQL ODBC driver installed on Oracle server
Oracle Client utilities	tnsnames.ora, listener.ora, sqlnet.ora configured
OS Access	Root or sudo privileges to edit Oracle networking files

🏗️ 3. Install PostgreSQL ODBC Driver on Oracle Server

For Linux:

sudo yum install -y unixODBC unixODBC-devel
sudo yum install -y postgresql-odbc

Verify installation:

odbcinst -q -d

You should see:

[PostgreSQL]

🧾 4. Configure ODBC DSN

Edit the ODBC configuration file (/etc/odbc.ini) and add a DSN for PostgreSQL:

[PG_LINK]
Driver=/usr/lib64/psqlodbcw.so
Description=PostgreSQL ODBC Connection
Servername=10.11.225.90
Port=5432
Database=ipp_data
Username=postgres
Password=Postgres@123
ReadOnly=no
Protocol=7.4

Test connectivity:

isql -v PG_LINK postgres Postgres@123

If successful, you’ll see:

Connected!

🔧 5. Create Oracle HS Initialization File

Create file:

$ORACLE_HOME/hs/admin/initPG_LINK.ora

Add:

HS_FDS_CONNECT_INFO = PG_LINK
HS_FDS_TRACE_LEVEL = 0
HS_FDS_SHAREABLE_NAME = /usr/lib64/libodbc.so
HS_LANGUAGE = AMERICAN_AMERICA.UTF8

🌐 6. Update Oracle Network Files

📝 listener.ora

Add an entry for HS service:

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = PG_LINK)
      (ORACLE_HOME = /u01/app/oracle/product/19.0.0/dbhome_1)
      (PROGRAM = dg4odbc)
      (ENVS = "LD_LIBRARY_PATH=/usr/lib64:/u01/app/oracle/product/19.0.0/dbhome_1/lib")
    )
  )

Restart listener:

lsnrctl stop
lsnrctl start

📝 tnsnames.ora

Add TNS entry:

PG_LINK =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 10.11.225.60)(PORT = 1521))
    (CONNECT_DATA = (SID = PG_LINK))
    (HS = OK)
  )

🧠 7. Create Database Link in Oracle

Now connect to Oracle DB and create the link:

CREATE DATABASE LINK HO.WORLD
CONNECT TO "postgres" IDENTIFIED BY "Postgres@123"
USING 'PG_LINK';

Test the connection:

SELECT * FROM "public"."pg_table_name"@HO.WORLD;

If you can query successfully, your HS link is working fine 🎯

📦 8. Migrate Data from Oracle to PostgreSQL

Example PL/SQL block to copy data from Oracle table to PostgreSQL table:

SET SERVEROUTPUT ON
DECLARE
  CURSOR c1 IS
    SELECT group_id, ipp_name, data_source, tag_time_stamp, access_time_stamp, check_status
    FROM master.sample;

  v_count NUMBER := 0;
BEGIN
  FOR rec IN c1 LOOP
    INSERT INTO "public"."sample"@HO.WORLD
    (group_id, ipp_name, data_source, tag_time_stamp, access_time_stamp, check_status)
    VALUES
    (rec.group_id, rec.ipp_name, rec.data_source, rec.tag_time_stamp, rec.access_time_stamp, rec.check_status);

    v_count := v_count + 1;
    IF MOD(v_count, 1000) = 0 THEN
      COMMIT;
      DBMS_OUTPUT.PUT_LINE(v_count || ' rows committed...');
    END IF;
  END LOOP;
  COMMIT;
  DBMS_OUTPUT.PUT_LINE('Total ' || v_count || ' rows migrated.');
END;
/

🔍 9. Verification

From Oracle:

SELECT COUNT(*) FROM "public"."ipp_33_tags_mda_status"@HO.WORLD;

From PostgreSQL:

SELECT COUNT(*) FROM public.ipp_33_tags_mda_status;

Both counts should match ✅

🏁 Conclusion

Using Oracle HS with DG4ODBC, you can directly integrate Oracle and PostgreSQL without ETL tools.
This approach is especially useful for incremental data migration, cross-database reporting, and archival automation.

For large-scale migrations, consider Oracle GoldenGate or Oracle Data Integrator (ODI), but for medium-volume transactional data, HS DB Link is a powerful and lightweight solution.