How to do it…

Here, we provide stepwise instructions on 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 myserv
        FOREIGN DATA WRAPPER oracle_fdw
        OPTIONS (dbserver '//myhost/MYDB');
        CREATE USER MAPPING FOR myuser
        SERVER 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 myserv
        OPTIONS (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 values that we have inserted:

        SELECT * FROM mytab WHERE id = -1;

This should result in the following output:

          id |   descr   
         ----+-----------
          -1 | Minus One
         (1 row)