Accessing Oracle Schema from Postgresql-11 Database on Ubuntu 18.04 using oracle_fdw

Firstly login to psql

ana@ana-pg ~ % sudo -i -u postgres
[sudo] password for ana:
postgres@ana-pg:~$ psql
psql (11.2 (Ubuntu 11.2-1.pgdg18.04+1))
Type "help" for help.
postgres=# \c testdb;

Now that we are in the test database testdb, we can create a schema here.

testdb=# create schema test;

Now in this test schema, we will try to add the foreign oracle server called “oradb”. Now this server will be accessed by postgres user rtam. Therefore we create a user mapping of rtam to oracle

CREATE SERVER oradb FOREIGN DATA WRAPPER oracle_fdw
OPTIONS (dbserver '//hostname:1521/service');
GRANT USAGE ON FOREIGN SERVER oradb TO rtam;
CREATE USER MAPPING FOR rtam SERVER oradb OPTIONS (user 'oracle_schema_name', password 'oracle_schema_password');

Now lets try a simple experiment. Let us say we have a schema “scott” in oracle. We create a table here called test

CREATE TABLE scott.test
(
id NUMBER,
msg VARCHAR2 (30 CHAR)
);

Now in Postgresql, in our testdb, in our test schema, we run the following in psql

CREATE FOREIGN TABLE test.test (
id integer NOT NULL,
msg character varying(30))
SERVER oradb OPTIONS (schema 'SCOTT', table 'TEST');

If we now do a select on test.test, we will see the values of oracle table in the Postgresql Database.

So basically under Foreign Tables one sees the oracle tables directly accessible to Postgres.

One can also Import the oracle foreign table schemas directly into postgresql using a command:

IMPORT FOREIGN SCHEMA "SCOTT" LIMIT TO
(TABLE1,TABLE2,...) FROM SERVER oradb INTO postgres_schema_name;

Note that when using Oracle names, always use caps. Also in the above schema name, “” have to be used to make sure that Postgres interprets the oracle schema name correctly. Postgresql otherwise tends to search for lower case names and does not find anything.

Back to Import Oracle Foreign Server

Leave a Reply

Your email address will not be published. Required fields are marked *

34 + = 42