PostgreSQL 11 Administration Cookbook

How to do it…

Here, we provide the steps to follow regarding how to connect to an Oracle server using oracle_fdw:

1. First, we ensure that the extension is loaded:

CREATE EXTENSION IF NOT EXISTS oracle_fdw;

2. Then, we configure the server and the user mapping:

CREATE SERVER myservFOREIGN DATA WRAPPER oracle_fdwOPTIONS (dbserver '//myhost/MYDB');CREATE USER MAPPING FOR myuserSERVER myserv;

3. Then, we create a PostgreSQL foreign table with the same column names as the source table in Oracle, and with compatible column types:

CREATE FOREIGN TABLE mytab(id bigint, descr text)SERVER myservOPTIONS (user 'scott', password 'tiger');

4. Now, we can try to write to the table:

INSERT INTO mytab VALUES (-1, 'Minus One');

5. Finally, we are able to read the ...