Foreign Data Wrapper for Oracle
===============================

oracle_fdw is a PostgreSQL extension that provides a Foreign Data Wrapper for
easy and efficient read access to Oracle databases, including pushdown of WHERE
conditions and required columns as well as comprehensive EXPLAIN support.

oracle_fdw was written by Laurenz Albe <laurenz.albe@wien.gv.at>

Objects created by the extension
================================

FUNCTION oracle_fdw_handler() RETURNS fdw_handler
  Oracle foreign data wrapper handler

FUNCTION oracle_fdw_validator(text[], oid) RETURNS void
  Oracle foreign data wrapper options validator

These functions are the handler and the validator function necessary to create
a foreign data wrapper.

The extension automatically creates a foreign data wrapper named "oracle_fdw".
Normally that's all you need and you can proceed to define foreign servers.
You can create additional Oracle foreign data wrappers, for example if you
need to set the "nls_lang" option (you can alter the existing "oracle_fdw"
wrapper, but all modifications will be lost after a dump/restore).

FUNCTION oracle_close_connections() RETURNS void
  closes all open Oracle connections

This function can be used to close all open Oracle connections in this session.
See "Usage" below for when this might be useful.

Options
=======

Foreign data wrapper options
----------------------------

(Caution: If you modify the default foreign data wrapper "oracle_fdw",
any changes will be lost upon dump/restore.  Create a new foreign data wrapper
if you want the options to be persistent.)

- nls_lang (optional)

  Sets the NLS_LANG environment variable for Oracle to this value.
  NLS_LANG is in the form <language>_<territory>.<charset> (for example
  AMERICAN_AMERICA.AL32UTF8).  This must match your database encoding.
  When this value is not set, oracle_fdw will automatically do the right
  thing if it can and issue a warning if it cannot.
  Set this only if you know what you are doing.  See "Problems" below.

Foreign server options
----------------------

- dbserver (required)

  The Oracle database connection string for the remote database.
  This can be in any of the forms that Oracle supports as long as your
  Oracle client is configured accordingly.
  Set this to an empty string for local ("BEQUEATH") connections.

User mapping options
--------------------

- user (required)

  The Oracle user name for the session.
  Set this to an empty string for "external authentication" if you don't
  want to store Oracle credentials in the PostgreSQL database (one simple way
  is to use an "external password store").

- password (required)

  The password for the Oracle user.

Foreign table options
---------------------

- table (required)

  The Oracle table name.  If unquoted, Oracle will translate it to upper case.
  For lower case, mixed case and special characters, you must specify this
  option enclosed in double quotes.

- schema (optional)

  The table's schema (or owner).  Useful to access tables that do not belong
  to the connecting Oracle user.

- plan_costs (optional)

  If set to yes/on/true, Oracle's cost estimates will be used.  The problem is
  that Oracle gives good estimates for the result size, but not for the cost
  of the execution (the only estimate you can get is "execution time" in second
  granularity).
  Since these values are almost useless but expensive to collect, oracle_fdw
  does by default does not bother to get them and estimates the costs to 10000
  independent of the actual query.
  Turn this on only if a) query execution is expensive and b) it has a positive
  influence on PostgreSQL query planning.

Usage
=====

Oracle permissions
------------------

The Oracle user will obviously need CREATE SESSION privilege and the right
to select from the table or view in question.

For EXPLAIN VERBOSE (and query planning if "plan_costs" is turned on),
the user will also need SELECT privileges on V$SQL and V$SQL_PLAN.

Connections
-----------

oracle_fdw caches Oracle connections because it is expensive to create an
Oracle session for each individual query.  All connections are automatically
closed when the PostgreSQL session ends.
The function oracle_close_connections() can be used to close all cached Oracle
connections.  This can be useful for long-running sessions that don't access
foreign tables all the time and want to avoid blocking the resources needed
by an open Oracle connection.

Columns
-------

When you define a foreign table, the columns of the Oracle table are mapped
to the PostgreSQL columns in the order of their definition.

oracle_fdw will only include those columns in the Oracle query that are
actually needed by the PostgreSQL query.

The PostgreSQL table can have more or less columns than the Oracle table.
If it has more columns, and these columns are used, you will receive a warning
and NULL values will be returned.

Data types
----------

You must define the PostgreSQL columns with data types that oracle_fdw can
translate (see the conversion table below).  This restriction is only enforced
if the column actually gets used, so you can define "dummy" columns for
untranslatable data types as long as you don't access them.  If an Oracle
value exceeds the size of the PostgreSQL column (e.g., the length of a varchar
column or the maximal integer value), you will receive a runtime error.

These conversions are automatically handled by oracle_fdw:

Oracle type                    | Possible PostgreSQL types
-------------------------------+---------------------------------------------
CHAR                           | char, varchar, text
NCHAR                          | char, varchar, text
VARCHAR                        | char, varchar, text
VARCHAR2                       | char, varchar, text
NVARCHAR2                      | char, varchar, text
CLOB                           | char, varchar, text
CFILE                          | char, varchar, text
RAW                            | uuid, bytea
BLOB                           | bytea
BFILE                          | bytea
NUMBER                         | numeric, float4, float8, char, varchar, text
NUMBER(n,m) with m<=0          | numeric, float4, float8, int2, int4, int8,
                               |   boolean, char, varchar, text
FLOAT                          | numeric, float4, float8, char, varchar, text
BINARY_FLOAT                   | numeric, float4, float8, char, varchar, text
BINARY_DOUBLE                  | numeric, float4, float8, char, varchar, text
DATE                           | date, timestamp, timestamptz, char,
                               |   varchar, text
TIMESTAMP                      | date, timestamp, timestamptz, char,
                               |   varchar, text
TIMESTAMP WITH TIME ZOME       | date, timestamp, timestamptz, char,
                               |   varchar, text
TIMESTAMP WITH LOCAL TIME ZOME | date, timestamp, timestamptz, char,
                               |   varchar, text
INTERVAL YEAR TO MONTH         | interval, char, varchar, text
INTERVAL DAY TO SECOND         | interval, char, varchar, text

If a NUMBER is converted to a boolean, "0" means "false", everything else "true".

The deprecated types LONG and LONG RAW are not supported at this time.
NCLOB is currently not supported because Oracle cannot automatically convert
it to the client encoding.

If you need conversions exceeding the above, define an appropriate view in
Oracle or PostgreSQL.

WHERE conditions
----------------

PostgreSQL will use all applicaple parts of the WHERE clause as a filter
for the scan.  The Oracle query that oracle_fdw constructs will contain a WHERE
clause corresponding to these filter criteria whenever such a condition can
safely be translated to Oracle SQL.  This feature, also known as "push-down
of WHERE clauses", can greatly reduce the number of rows retrieved from Oracle
and may enable Oracle's optimizer to choose a good plan for accessing the
required tables.

To make use of that, try to use simple conditions for the foreign table.
Choose PostgreSQL column data types that correspond to Oracle's types,
because otherwise conditions cannot be translated.

The expressions "now()", "transaction_timestamp()", "current_timestamp",
"current_date" and "localtimestamp" will be translated correctly.

The output of EXPLAIN will show the Oracle query used, so you can see which
conditions were translated to Oracle and how.

Explain
-------

PostgreSQL's EXPLAIN will show the query that is actually issued to Oracle.
EXPLAIN VERBOSE will show Oracle's execution plan.

Installation Requirements
=========================

PostgreSQL 9.1 or better are required.
Oracle client version 10 or better is required.

oracle_fdw should compile and run on any platform supported by PostgreSQL,
although I could only test it on Linux and Windows.

It has been tested with Oracle 10.2 and 11.2.  It might work with other
versions.  Both Instant Client and the regular client should work.

Installation
============

oracle_fdw has been written as a PostgreSQL extension and uses the Extension
Building Infrastructure "PGXS".  It should be easy to install.

You will need PostgreSQL headers and PGXS installed (if your PostgreSQL was
installed with packages, install the development package).
You need to install Oracle's headers as well (SDK package for Instant
Client).

Make sure that PostgreSQL is configured "--without-ldap" (at least the server).
See "Problems" below.

Make sure that "pg_config" is in the PATH (test with "pg_config --pgxs").
Set the environment variable ORACLE_HOME to the location of the Oracle
installation.

Unpack the source code of oracle_fdw and change into the directory.
Then the software installation should be as simple as

  $ make
  $ make install

For the second step you need write permission to PostgreSQL's shared library
directory.

Since the Oracle client shared library is probably not in the standard
library path, you have to make sure that the PostgreSQL server will be able
to find it.  How this is done varies from operating system to operating
system; on Linux you can set LD_LIBRARY_PATH or use /etc/ld.so.conf.

Make sure that all necessary Oracle environment variables are set in the
environment of the PostgreSQL server process (ORACLE_HOME if you don't use
Instant Client, TNS_ADMIN if you have configuration files, etc.)

To install the extension in a database, connect as superuser and

  CREATE EXTENSION oracle_fdw;

That will define the required functions and create a foreign data wrapper.

Internals
=========

oracle_fdw sets the MODULE of the Oracle session to "postgres" and the
ACTION to the backend process number.  This can help identifying the Oracle
session and allows you to trace it with DBMS_MONITOR.SERV_MOD_ACT_TRACE_ENABLE.

oracle_fdw uses Oracle's result prefetching to avoid unnecessary client-server
round-trips. The prefetch counter is set to 200, the memory limit to 24KB.
These limits can be changed in oracle_utils.c.

Rather than using a PLAN_TABLE to explain an Oracle query (which would require
such a table to be created in the Oracle database), oracle_fdw uses execution
plans stored in the library cache.  For that, an Oracle query is "explicitly
described", which forces Oracle to parse the query.  The hard part is to find
the SQL_ID and CHILD_NUMBER of the statement in V$SQL because the SQL_TEXT
column contains only the first 1000 bytes of the query.
Therefore, oracle_fdw adds a comment to the query that contains an MD5 hash
of the query text.  This is used to search in V$SQL.
The actual execution plan or cost information is retrieved from V$SQL_PLAN.

Problems
========

Encoding
--------

If you use a PostgreSQL database encoding that Oracle does not know
(currently, these are EUC_CN, EUC_KR, LATIN10, MULE_INTERNAL, WIN874
and SQL_ASCII), non-ASCII characters cannot be translated correctly.
You will get a warning and the characters will be replaced by "replacement
characters", typically a question mark.

You can set the "nls_lang" option of the foreign data wrapper to force a
certain Oracle encoding, but the resulting characters will most likely be
incorrect and lead to PostgreSQL error messages.  This is probably only
useful for SQL_ASCII encoding if you know what you are doing.
See "Options" above.

Planning
--------

Oracle's planner does not give good cost estimates (you can only get the
estimated execution time in second granularity, and it is never less than
one).  So by default, oracle_fdw does not use these estimates.
See the description of the "plan_costs" option above.

Even without that, the current implementation of oracle_fdw requires
calls to the Oracle server for every foreign table during query planning.
That means that there might be a noticeable performance improvement
if query plans are cached, for example by using prepared statements or
PL/pgSQL functions.

LDAP libraries
--------------

The Oracle client shared library comes with its own LDAP client
implementation conforming to RFC 1823, so these functions have the same
names as OpenLDAP's.  This will lead to a name collision when the PostgreSQL
server was configured "--with-ldap".

The name collision will not be detected, because oracle_fdw is loaded at
runtime, but trouble will happen if anybody calls an LDAP function.
Typically, OpenLDAP is loaded first, so if Oracle calls an LDAP function
(for example if you use "directory naming" name resolution), the backend
will crash.  This can lead to messages like the following (seen on Linux)
in the PostgreSQL server log:

  ../../../libraries/libldap/getentry.c:29: ldap_first_entry:
  Assertion `( (ld)->ld_options.ldo_valid == 0x2 )' failed.

The best thing is to configure PostgreSQL "--without-ldap".  This is the only
safe way to avoid this problem.
Even when PostgreSQL is built "--with-ldap", it may work as long as you don't
use any LDAP client functionality in Oracle.
On some platforms, you can force Oracle's client shared library to be loaded
before the PostgreSQL server is started (LD_PRELOAD on Linux).  Then Oracle's
LDAP functions should get used.  In that case, Oracle may be able to use
LDAP functionality, but using LDAP from PostgreSQL will crash the backend.

You cannot use LDAP functionality both in PostgreSQL and in Oracle, period.