How to install and configure the Oracle Foreign Data Wrapper

Preinstall Checklist:

   – If you have not already, please download and install “Microsoft Visual C++ 2015 Redistributal” and Java Runtime Environment (version 7 or 8)

Oracle Instant Client install and config:

          01. Download and unzip into the same directory:

                   instantclient-basic-windows

                   instantclient-odbc-windows

                   instantclient-sqlplus-windows

          02. Create “Oracle Instant Client” in Program Files and copy instantclient_12_1 into it.

          03. Double click on “odbc_install.exe”

          04. Create system variables:

                   name: TNS_ADMIN

                   value: C:\Program Files\Oracle Instant Client\instantclient_12_1

                   name: ORACLE_HOME

                   value: C:\Program Files\Oracle Instant Client\instantclient_12_1

          05. Edit a system variable:

                   name: PATH

                   value: C:\Program Files\Oracle Instant Client\instantclient_12_1

          06. (optional). Attempt to run “sqlplus” from the command line. If all is properly configured, you should be prompted to enter a username/password. You can exit “sqlplus” at this point if you                  wish and continue with this guide.

NOTE 1: For Windows 7, to edit system variables, right click on “My Computer”, then click on “Advanced System Settings” in the left of the screen. A popup window titled “System Properties” will appear. Click on the “Advanced” tab and look to the bottom of the window. Above “OK” and “Cancel”, there will be an option titled “Environment Variables”. Click on it and find the pane “System Variables” in the newly created popup window. Here you can create and edit your system variables.

NOTE 2: If you recieve an error concerning “MSvcr100.dll” being missing, it may be necesarry to copy this dll out of your /Java/VERSION/bin folder and into your oracle instant client directory

Assuming PostgreSQL has previously been installed and properly configured,

Configure the Oracle FDW

          01. Open up a PSQL shell, and enter:

                   “CREATE EXTENSION oracle_fdw;”

          Expected Output: CREATE EXTENSION

Example configuration

This instructions below are an example of how to setup PostgreSQL to connect to an oracle database through the Oracle Foreign Data Wrapper. Your specific settings will be different, but the syntax should remain the same. For our purposes, I will show you how to connect to an oracle database with the “SCOTT” schema. This installed. This part of the how-to assumes your oracle database is already set up and configured to accept remote database connections.

          01. CREATE SCHEMA scott;

                   Expected Output: CREATE SCHEMA

          02. CREATE SERVER oracle FOREIGN DATA WRAPPER oracle_fdw OPTIONS (dbserver ‘//192.168.1.254/YOURSERVERNAME’ );

                   Expected Output: CREATE SERVER

          03. CREATE USER MAPPING FOR postgres SERVER oracle OPTIONS (user ‘SCOTT’, password ‘tiger’);

                   Expected Output: CREATE USER MAPPING

          04. IMPORT FOREIGN SCHEMA “SCOTT” from SERVER oracle INTO scott;

                   Expected Output: IMPORT FOREIGN SCHEMA

          05. set search_path=’scott’;

                   Expected Output: SET

Now we can explore the imported data