Postgresql – Connecting Oracle 11g to Postgres via dblink

_{Community Wiki answer collecting answers left in question comments}

a_horse_with_no_name:

The ODBC driver needs to be on the computer that connects to the Oracle server not on the Oracle server itself. Plus: you can't use dblink for this, Postgres' DBLink can only connect to other Postgres servers. But you can use a foreign data wrapper. You only need the Oracle client libraries (not the ODBC driver) on the Postgres server, nothing needs to be installed on the Oracle server.

If you don't have administrator access to the Oracle database nor can you change anything in Postgres, then there is nothing you can do. Oracle's DBLink can only connect to other Oracle servers. You could install Oracle's "Heterogeneous Services" to be able to connect through ODBC (the Postgres ODBC driver, not the Oracle one) but that again would require that you have admin access to the Oracle database.

You need install Oracle's "Heterogeneous Services" then install the ODBC driver for Postgres, not the one for Oracle.

EdStevens:

It's more than just the ODBC driver. You'll need to install the Oracle Transparent Gateway for ODBC. The Oracle database sends a client request to the Oracle listener, that uses a gateway config file to further resolve the request to an ODBC DSN. The full instructions are pretty complex but I have an article outlining the pieces and their connections. That should give you a good overview and framework with which to understand the more detailed instructions.

You'll need both the Oracle gateway and the Postgres ODBC driver. The best link I could find was this:

Where to download DG4ODBC from?

Of course, when you get there, you'll want to adjust for your version. For the ODBC driver, that would come from Postgres.