Hi Laurenz,
I'm trying your powerful software and I have bad performance during the join between a PostgreSQL table A with foreing Oracle TABLE table B.
are a simple join like
SELECT * from A inner join B on A.id = B.id limit 1;
or
SELECT * from A inner join B on A.id = B.id where A.id in (select id from A limit 2)

but if I put the where condition on the table A it's works perfectly:
SELECT * from A inner join B on A.id = B.id where A.id = 5;

It would be great to have Oracle Spatial / PostGIS support for geometries in this FDW.

Oracle Spatial and PostGIS use different internal geometry representations. Therefore, some kind of conversion needs to be done.

One way to achieve this would be to use the WKB format as a pivot format. Oracle spatial can convert to this format, and PostGIS can read from it.
Text WKT could be used as well, but there may be some digits lost in the process, leading to data change between both systems.

Just opening the conversation, if more information is needed, do not hesitate to ask.

Attempting to perform a LEFT JOIN of two Oracle tables.

For the following query with table/column names obscured:
SELECT COUNT(*) FROM table_a LEFT JOIN table_b ON table_a.char_key = table_b.char_key WHERE table_b.data IS NOT NULL;

Oracle yields: 201788, indicating the tables are successfully being joined.

Postgres, via oracle_fdw yields 0

The query is being executed verbatim on each database. I can query each table independently with oracle_fdw just fine, but the tables are never successfully joined, as if the ON condition is always false.

The two columns being compared in the ON clause are both defined as character varying(18) in the foreign table schema.

Hi,

While I was struggling with oracle_fdw to get worked, I found the "oradiag_postgres" directory (/var/lib/pgsql/oradiag_postgres) which contains a trace file (sqlnet.log) and a log file (log.xml). Which process or component is storing information in this directory?

This information looks to be useful for troubleshooting, but not mentioned in README yet. So, I would like to know more details about this in order to add some explanation to the README.

Regards,

Related to #56 , sys.xds_ace also fails:

LINE 1: ... "xds_ace" ("aclid" bytea, "start_date" timestamp(-78) witho...
                                                             ^
QUERY:  CREATE FOREIGN TABLE "xds_ace" ("aclid" bytea, "start_date" timestamp(-78) without time zone, "end_date" timestamp(-78) without time zone, "is_grant" character varying(5), "invert" character varying(5), "principal" character varying(4000), "privilege" text) SERVER "oracle" OPTIONS (schema 'SYS', table 'XDS_ACE', readonly 'true')

Looking in ALL_TAB_COLUMNS, DATA_TYPE indeed shows 'TIMESTAMP(-78)' and DATA_SCALE is -78.

I also can't exclude this table. I've tried XDS_ACE, xds_ace and "xds_ace":

IMPORT FOREIGN SCHEMA "SYS"
  EXCEPT( EXU8COL, EXU8COLU, EXU8COL_TTS_UNUSED_COL, KU$_TABCLUSTER_COL_VIEW, "xds_ace" )
  FROM SERVER oracle
  INTO sys
  OPTIONS( readonly 'true' )
;

And LIMIT TO is apparently being ignored as well (I tried both upper and lowercase):

IMPORT FOREIGN SCHEMA "SYS"
  --EXCEPT( EXU8COL, EXU8COLU, EXU8COL_TTS_UNUSED_COL, KU$_TABCLUSTER_COL_VIEW, "xds_ace" )
  LIMIT TO( all_source )
  FROM SERVER oracle
  INTO sys
  OPTIONS( readonly 'true' )
;

Hi,

I'm having trouble when using oracle_fdw (1.3.0) with 2 PostgreSQL (one with 9.1.13 and the other with 9.5.1) instances/clusters on the same server.

The first installation/configuration and usage went smoothly with oracle_fdw 1.3.0 & PostgreSQL 9.1.13 port 2345:

@ POSTGRESQL SERVER:

-bash-4.1$ psql -p 2345 postgres
psql (9.1.13)

postgres=# CREATE EXTENSION oracle_fdw;
CREATE EXTENSION

postgres=# CREATE SERVER svr_oracle FOREIGN DATA WRAPPER oracle_fdw OPTIONS(dbserver 'ol7orcl12c:21912/BDTEST');
CREATE SERVER

postgres=# GRANT USAGE ON FOREIGN SERVER svr_oracle TO postgres;
GRANT

postgres=# CREATE USER MAPPING FOR postgres SERVER svr_oracle OPTIONS (user 'SYSTEM', password '********');
CREATE USER MAPPING

postgres=# select oracle_diag();
oracle_diag

oracle_fdw 1.3.0, PostgreSQL 9.1.13, Oracle client 12.1.0.2.0
(1 fila)

postgres=# CREATE FOREIGN TABLE ft_dual (
postgres(# dummy character varying(1) NOT NULL
postgres(# ) SERVER svr_oracle OPTIONS (schema 'SYS', table 'DUAL');
CREATE FOREIGN TABLE

postgres=# SELECT * FROM ft_dual;
dummy

X
(1 fila)

END OF USAGE WITH POSTGRESQL 9.1.13

On the same server, the second installation/configuration with oracle_fdw 1.3.0 & PostgreSQL 9.5.1 port 5432. I had issues after the Installation:

@ POSTGRESQL SERVER:

LETS CHECK THE PARAMETERS & LIBRARIES:

STEP 1

[root@svr-lb-prcat2 ~]# yum list 'postgres*'
Installed Packages
postgresql91.x86_64 9.1.13-1PGDG.rhel6 @/postgresql91-9.1.13-1PGDG.rhel6.x86_64
postgresql91-contrib.x86_64 9.1.13-1PGDG.rhel6 @/postgresql91-contrib-9.1.13-1PGDG.rhel6.x86_64
postgresql91-devel.x86_64 9.1.13-1PGDG.rhel6 installed
postgresql91-libs.x86_64 9.1.13-1PGDG.rhel6 @/postgresql91-libs-9.1.13-1PGDG.rhel6.x86_64
postgresql91-server.x86_64 9.1.13-1PGDG.rhel6 @/postgresql91-server-9.1.13-1PGDG.rhel6.x86_64
postgresql95.x86_64 9.5.1-1PGDG.rhel6 installed
postgresql95-contrib.x86_64 9.5.1-1PGDG.rhel6 installed
postgresql95-devel.x86_64 9.5.1-1PGDG.rhel6 installed
postgresql95-libs.x86_64 9.5.1-1PGDG.rhel6 installed
postgresql95-server.x86_64 9.5.1-1PGDG.rhel6 installed

STEP 2

[root@svr-lb-prcat2 ~]# vim ~/.bash_profile
ORACLE_HOME=/oracle/instantclient_12_1
LD_LIBRARY_PATH=/oracle/instantclient_12_1

PG_CONFIG=/usr/pgsql-9.1/bin

PG_CONFIG=/usr/pgsql-9.5/bin
PATH=$PATH:$HOME/bin:$ORACLE_HOME:$PG_CONFIG

PATH=$PATH:$HOME/bin

export PATH
export ORACLE_HOME
export LD_LIBRARY_PATH

STEP 3

[root@svr-lb-prcat2 ~]# logout

LOGIN:

STEP 4

[root@svr-lb-prcat2 ~]# find / -name pg_config
/usr/pgsql-9.1/bin/pg_config
/usr/pgsql-9.5/bin/pg_config

STEP 5

[root@svr-lb-prcat2 ~]# yum list installed 'postgres*' | grep 'postgresql' | grep 'devel'
postgresql91-devel.x86_64 9.1.13-1PGDG.rhel6 installed
postgresql95-devel.x86_64 9.5.1-1PGDG.rhel6 installed

STEP 6

[root@svr-lb-prcat2 ~]# pg_config | head -n 1
BINDIR = /usr/pgsql-9.5/bin

STEP 7

[root@svr-lb-prcat2 ~]# pg_config --pgxs
/usr/pgsql-9.5/lib/pgxs/src/makefiles/pgxs.mk

STEP 8

[root@svr-lb-prcat2 ~]# echo $ORACLE_HOME
/oracle/instantclient_12_1

STEP 9

[root@svr-lb-prcat2 ~]# echo $LD_LIBRARY_PATH
/oracle/instantclient_12_1

STEP 10

[root@svr-lb-prcat2 ~]# cd /oracle/oracle_fdw-1.3.0/

STEP 11

[root@svr-lb-prcat2 oracle_fdw-1.3.0]# pg_config --pgxs
/usr/pgsql-9.5/lib/pgxs/src/makefiles/pgxs.mk

STEP 12

[root@svr-lb-prcat2 oracle_fdw-1.3.0]# make
make: Does not do anything for `all'.

STEP 13

[root@svr-lb-prcat2 oracle_fdw-1.3.0]# make install
/bin/mkdir -p '/usr/pgsql-9.5/lib'
/bin/mkdir -p '/usr/pgsql-9.5/share/extension'
/bin/mkdir -p '/usr/pgsql-9.5/share/extension'
/bin/mkdir -p '/usr/pgsql-9.5/doc/extension'
/usr/bin/install -c -m 755 oracle_fdw.so '/usr/pgsql-9.5/lib/oracle_fdw.so'
/usr/bin/install -c -m 644 .//oracle_fdw.control '/usr/pgsql-9.5/share/extension/'
/usr/bin/install -c -m 644 .//oracle_fdw--1.1.sql .//oracle_fdw--1.0--1.1.sql '/usr/pgsql-9.5/share/extension/'
/usr/bin/install -c -m 644 .//README.oracle_fdw '/usr/pgsql-9.5/doc/extension/'

STEP 14

[root@svr-lb-prcat2 oracle_fdw-1.3.0]# pg_config | grep PKGLIBDIR
PKGLIBDIR = /usr/pgsql-9.5/lib

STEP 15

[root@svr-lb-prcat2 oracle_fdw-1.3.0]# ls -l /usr/pgsql-9.5/lib/oracle_fdw*
-rwxr-xr-x. 1 root root 323418 mar 2 10:11 /usr/pgsql-9.5/lib/oracle_fdw.so

STEP 16

[root@svr-lb-prcat2 oracle_fdw-1.3.0]# ldd /usr/pgsql-9.5/lib/oracle_fdw.so
linux-vdso.so.1 => (0x00007fffd7acc000)
libclntsh.so.12.1 => /oracle/instantclient_12_1/libclntsh.so.12.1 (0x00007fe66d325000)
libc.so.6 => /lib64/libc.so.6 (0x00007fe66cf87000)
libmql1.so => /oracle/instantclient_12_1/libmql1.so (0x00007fe66cd11000)
libipc1.so => /oracle/instantclient_12_1/libipc1.so (0x00007fe66c993000)
libnnz12.so => /oracle/instantclient_12_1/libnnz12.so (0x00007fe66c288000)
libons.so => /oracle/instantclient_12_1/libons.so (0x00007fe66c043000)
libdl.so.2 => /lib64/libdl.so.2 (0x00007fe66be3f000)
libm.so.6 => /lib64/libm.so.6 (0x00007fe66bbba000)
libpthread.so.0 => /lib64/libpthread.so.0 (0x00007fe66b99d000)
libnsl.so.1 => /lib64/libnsl.so.1 (0x00007fe66b784000)
librt.so.1 => /lib64/librt.so.1 (0x00007fe66b57b000)
/lib64/ld-linux-x86-64.so.2 (0x000000389a200000)
libaio.so.1 => /lib64/libaio.so.1 (0x00007fe66b37a000)
libclntshcore.so.12.1 => /oracle/instantclient_12_1/libclntshcore.so.12.1 (0x00007fe66ae08000)

STEP 17

[root@svr-lb-prcat2 oracle_fdw-1.3.0]# service postgresql-9.5 restart
Stoping service postgresql-9.5: [ OK ]
Starting services postgresql-9.5: [ OK ]

STEP 18

[root@svr-lb-prcat2 oracle_fdw-1.3.0]# su - postgres

STEP 19

-bash-4.1$ psql -p 5432 postgres

psql (9.5.1)

STEP 20

postgres=# CREATE EXTENSION oracle_fdw;
ERROR: could not load library "/usr/pgsql-9.5/lib/oracle_fdw.so": /usr/pgsql-9.5/lib/oracle_fdw.so: undefined symbol: errcontext

END OF INSTALLATION/CONFIGURATION

I assume the root of the error is on STEP 12 something is amiss compared with the installation with postgresql 9.1.13.

My FOREIGN SERVER to oracle is still working with Postgresql 9.1.13 but I Cannot CREATE EXTENSION oracle_fdw; with Postgresql 9.5.1.
*ERROR: could not load library "/usr/pgsql-9.5/lib/oracle_fdw.so": /usr/pgsql-9.5/lib/oracle_fdw.so: undefined symbol: errcontext
*

Why Am I Using another PG9.5 Instance/Cluster on the same server?
Our production data is in PG 9.1 and oracle_fdw cannot INSERT, UPDATE, DELETE from PG 9.1 to an ORACLE scenario. So as PG 9.5 with oracle_fdw CAN do it, I'm going to dblink locally PG9.5 to 9.1 then INSERT, UPDATE, DELETE to ORACLE from 9.5. Also, is so much easier to import a whole schema to 9.5 than creating Foreign by Foreign table on 9.1

So as i said both Postgresql 9.1.13 and 9.5.1 are on the same Server RHEL 6.4 (Santiago). Is there anything I've missed? Thanks in advanced!

Wondering if it makes sense to add an option when creating a foreign table to base it off a query. The MySQL FDW can have
... OPTIONS (table 'hr.employees');
or
... OPTIONS (query 'SELECT * FROM overtime WHERE year = 2010;');

Is this a possibility for the Oracle FDW to do this as well?

Hello.

I am trying to use the IMPORT FOREIGN SCHEMA feature, but I am having trouble.

postgres=# SELECT oracle_diag();
                                               oracle_diag
---------------------------------------------------------------------------------------------------------
 oracle_fdw 1.3.0, PostgreSQL 9.5.0, Oracle client 12.1.0.2.0, ORACLE_HOME=/usr/lib/oracle/12.1/client64

I have my CREATE SERVER defined and I can successfully query data from my Oracle DB when my foreign table is manually setup with:

postgres=# create server oracle foreign data wrapper oracle_fdw options (dbserver '//hostname/test');
CREATE SERVER
postgres=# create user mapping for postgres server oracle options (user 'AUS', password 'xxxx');
CREATE USER MAPPING
postgres=# create foreign table MY_TABLE ( c_number varchar(15) not null, status varchar(60), requested_by varchar(100) ) server oracle options (schema 'AUS', table 'MY_TABLE');
CREATE FOREIGN TABLE
postgres=# select count(*) from MY_TABLE;
 count
-------
     3
(1 row)

Following the example and documentation, I attempt to import the foreign schema:

postgres=# drop foreign table MY_TABLE;
DROP FOREIGN TABLE
postgres=# create schema aus;
CREATE SCHEMA
postgres=# IMPORT FOREIGN SCHEMA "AUS" FROM SERVER oracle INTO aus OPTIONS(case 'lower');
DEBUG:  StartTransactionCommand
DEBUG:  StartTransaction
DEBUG:  name: unnamed; blockState:       DEFAULT; state: INPROGR, xid/subid/cid: 0/1/0, nestlvl: 1, children:
DEBUG:  ProcessUtility
DEBUG:  oracle_fdw: import schema "AUS" from foreign server "oracle"
DEBUG:  oracle_fdw: set NLS_LANG=AMERICAN_AMERICA.AL32UTF8
DEBUG:  oracle_fdw: begin serializable remote transaction
DEBUG:  CommitTransactionCommand
DEBUG:  CommitTransaction
DEBUG:  name: unnamed; blockState:       STARTED; state: INPROGR, xid/subid/cid: 0/1/0, nestlvl: 1, children:
DEBUG:  oracle_fdw: commit remote transaction
IMPORT FOREIGN SCHEMA

I don't receive any errors, but my schema is empty:

postgres=# \det
 List of foreign tables
 Schema | Table | Server
--------+-------+--------
(0 rows)

postgres=# \d
No relations found.

I went through the code a bit and found the Oracle SQL statements in orcale_utils.c for oracleGetImportColumn that are issued on IMPORT FOREIGN SCHEMA. My first thought was my Oracle user may not have the authority to run some of these queries. I'm no Oracle expert, but these queires seem pretty straight-forward. I ran some similar quieries with SQLPlus and didn't have a problem.

I see there are some additional debug messages from oracle_utils.c but I am not sure how to capture those messages.

I suspect my issue is more about my Oracle environment than oracle_fdw but I'm posting it here in the hopes of getting a tip.

My environment: Mac OS X 10.10 (Yosemite), Postgres 9.4, Oracle Instantclient_64 11.2, oracle_fdw_1_3_0

Running 'make', my first issue was that oci.h couldn't be found -- solved by downloading Oracle's SDK.

Running 'make' again, my second issue was a failure to link 'lclntsh':

ld: library not found for -lclntsh
clang: error: linker command failed with exit code 1 (use -v to see invocation)
make: *** [oracle_fdw.so] Error 1

I tried sym-linking like this:
libclntsh.dylib -> libclntsh.dylib.11.1

And got this...

ld: warning: ignoring file /Applications/oracle/product/instantclient_64/11.2.0.4.0/lib/libclntsh.dylib, file was built for i386 which is not the architecture being linked (x86_64): /Applications/oracle/product/instantclient_64/11.2.0.4.0/lib/libclntsh.dylib
Undefined symbols for architecture x86_64:
"_OCIAttrGet", referenced from:
_oracleGetSession in oracle_utils.o
_closeSession in oracle_utils.o
_oracleDescribe in oracle_utils.o
_oracleExecuteQuery in oracle_utils.o
"_OCIAttrSet", referenced from:

... etc. etc. for all the 64 bit libraries ora_fdw is expecting.

Just to make sure I covered my bases, I also installed the Oracle 64 bit SQL Developers app for its libraries, with no luck.

Having oracle_fdw installed will make my world a better place. I'll really appreciate any help getting there. Thanks in advance.

Robert

I am trying to select a varchar field from my foreign data wrapper and keep getting the error:
invalid byte sequence for encoding "UTF8": 0xe3 0x4e 0x9c
CONTEXT: converting column "spbpers_ssn" for foreign table scan of "spbpers", row 1

I managed to add an elog command to the oracle_fdw and determined that this is the value that is being returned by oracle:▒N▒/u▒▒O8▒▒ΰ▒U▒▒▒2▒▒ ▒▒_+S

My best guess is that the company that produced the software which created the table has it set for encryption at rest? When I query in sqlplus I am able to see the column values just fine but in postgres everything I have tried fails. Every other field in the table works. Any thoughts?

While staing postgres with oracle_fdw with shared_preload_libraries = 'oracle_fdw'
got :

[postgres@thunder2 oracle_fdw]$ gdb -q -c /data/95/core.61498 ~/usr/local/pgsql95/bin/postgres
Reading symbols from /home/postgres/usr/local/pgsql95/bin/postgres...done.
[New LWP 61498]
[Thread debugging using libthread_db enabled]
Using host libthread_db library "/lib64/libthread_db.so.1".
Core was generated by `/home/postgres/usr/local/pgsql95/bin/postgres -D /data/95'.
Program terminated with signal 11, Segmentation fault.
#0  RecoveryInProgress () at xlog.c:7537
7537                    LocalRecoveryInProgress = xlogctl->SharedRecoveryInProgress;
Missing separate debuginfos, use: debuginfo-install glibc-2.17-106.el7_2.1.x86_64 libaio-0.3.109-13.el7.x86_64
(gdb) bt
#0  RecoveryInProgress () at xlog.c:7537
#1  0x00000000004cd1db in RecoveryInProgress () at xlog.c:7527
#2  0x0000000000680459 in LockAcquireExtended (locktag=locktag@entry=0x7ffd5a40b3a0, lockmode=lockmode@entry=1,
    sessionLock=sessionLock@entry=0 '\000', dontWait=dontWait@entry=0 '\000',
    reportMemoryError=reportMemoryError@entry=1 '\001') at lock.c:710
#3  0x0000000000680dd1 in LockAcquire (locktag=locktag@entry=0x7ffd5a40b3a0, lockmode=lockmode@entry=1,
    sessionLock=sessionLock@entry=0 '\000', dontWait=dontWait@entry=0 '\000') at lock.c:672
#4  0x000000000067e8b8 in LockRelationOid (relid=1255, lockmode=1) at lmgr.c:112
#5  0x00000000004909f5 in relation_open (relationId=1255, lockmode=) at heapam.c:1060
#6  0x0000000000490c46 in heap_open (relationId=relationId@entry=1255, lockmode=lockmode@entry=1) at heapam.c:1238
#7  0x00007f88e5fb4278 in _PG_init () at oracle_fdw.c:607
#8  0x00000000007769e8 in internal_load_library (
    libname=libname@entry=0x235d750 "/home/postgres/usr/local/pgsql95/lib/oracle_fdw.so") at dfmgr.c:276
#9  0x000000000077724c in load_file (filename=, restricted=) at dfmgr.c:148
#10 0x000000000077e15b in load_libraries (libraries=,
    gucname=gucname@entry=0x8e5478 "shared_preload_libraries", restricted=restricted@entry=0 '\000') at miscinit.c:1421
#11 0x000000000077f27e in process_shared_preload_libraries () at miscinit.c:1438
#12 0x000000000064076c in PostmasterMain (argc=argc@entry=3, argv=argv@entry=0x2343140) at postmaster.c:920
#13 0x0000000000465b90 in main (argc=3, argv=0x2343140) at main.c:223

compiled with:
CONFIGURE = '--prefix=/home/postgres/usr/local/pgsql95' '--enable-debug' '--without-ldap'
VERSION = PostgreSQL 9.5.1
export LD_LIBRARY_PATH=/home/postgres/src/oracle/instantclient_11_2
export ORACLE_HOME=/home/postgres/src/oracle/instantclient_11_2
oracle_fdw get from origin/master

ldd /home/postgres/usr/local/pgsql95/lib/oracle_fdw.so
        linux-vdso.so.1 =>  (0x00007ffff5191000)
        libclntsh.so.11.1 => /home/postgres/src/oracle/instantclient_11_2/libclntsh.so.11.1 (0x00007f3ead24a000)
        libc.so.6 => /lib64/libc.so.6 (0x00007f3eace7d000)
        libnnz11.so => /home/postgres/src/oracle/instantclient_11_2/libnnz11.so (0x00007f3eacab0000)
        libdl.so.2 => /lib64/libdl.so.2 (0x00007f3eac8ac000)
        libm.so.6 => /lib64/libm.so.6 (0x00007f3eac5a9000)
        libpthread.so.0 => /lib64/libpthread.so.0 (0x00007f3eac38d000)
        libnsl.so.1 => /lib64/libnsl.so.1 (0x00007f3eac174000)
        libaio.so.1 => /lib64/libaio.so.1 (0x00007f3eabf71000)
        /lib64/ld-linux-x86-64.so.2 (0x00007f3eafdda000)

That would make for a nice github frontpage.

I have a field in a table declared in Oracle like this :

"COF_FLAG_REFERENCEUR" CHAR(1 BYTE)

I tried to create a foreign table with :

elg_mod_ou_art "char"

I tried various typings, the only one working is character varying(19), but it is displayed as... timestamp !?

Hi !
i've recently install fdw 1.1.0 , created oradb link and I can from psql prompt select data from remote table , for example:

psql --username=lalala  --dbname=${database} 
psql (9.4.0)
Type "help" for help.
irzdbbkp=> select count(*) from rem.rem_irz_log;
 count  
--------
 162867
(1 row)
irzdbbkp=> 

at the same time, i can't execute SELECT from phpPgAdmin SQL
i ve got an error:

ERROR:  cannot connect to foreign Oracle server
DETAIL:  ORA-06413: Connection not open.

the same error i've got when run psql in TCP mode
psql --host=1.2.3.4.5 --username=postgres --dbname=${database}

That because i compile oracle_fdw with oracle 10R2 XE server.
When i recompile it with oracle instant client 11.2 - the error gone away

( Ubuntu 10.10 , Oracle 10.0.2 XE RDBMS , OracleInstantClient 11.2 PostgreSQL 9.4.0 )

Max.

postgres@oel7:/home/postgres/oracle_fdw-1.2.0/ [PG6] make clean
rm -f oracle_fdw.so   liboracle_fdw.a  liboracle_fdw.pc
rm -f oracle_fdw.o oracle_utils.o oracle_gis.o
rm -rf results/ regression.diffs regression.out tmp_check/ log/
postgres@oel7:/home/postgres/oracle_fdw-1.2.0/ [PG6] make
gcc -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -O2 -fpic -I/sdk/include -I/oci/include -I/rdbms/public -I/usr/include/oracle/12.1/client -I/usr/include/oracle/12.1/client64 -I/usr/include/oracle/11.2/client -I/usr/include/oracle/11.2/client64 -I/usr/include/oracle/11.1/client -I/usr/include/oracle/11.1/client64 -I/usr/include/oracle/10.2.0.5/client -I/usr/include/oracle/10.2.0.5/client64 -I/usr/include/oracle/10.2.0.4/client -I/usr/include/oracle/10.2.0.4/client64 -I/usr/include/oracle/10.2.0.3/client -I/usr/include/oracle/10.2.0.3/client64 -I. -I./ -I/u01/app/postgres/product/95/db_2/include/server -I/u01/app/postgres/product/95/db_2/include/internal -D_GNU_SOURCE -I/usr/include/libxml2   -c -o oracle_fdw.o oracle_fdw.c
oracle_fdw.c: In function ‘oracleGetForeignPlan’:
oracle_fdw.c:839:2: error: too few arguments to function ‘make_foreignscan’
  return make_foreignscan(tlist, keep_clauses, baserel->relid, fdwState->params, fdw_private);
  ^
In file included from oracle_fdw.c:42:0:
/u01/app/postgres/product/95/db_2/include/server/optimizer/planmain.h:46:21: note: declared here
 extern ForeignScan *make_foreignscan(List *qptlist, List *qpqual,
                     ^
oracle_fdw.c: In function ‘oraclePlanForeignModify’:
oracle_fdw.c:1226:25: error: ‘RangeTblEntry’ has no member named ‘modifiedCols’
    tmpset = bms_copy(rte->modifiedCols);
                         ^
In file included from oracle_fdw.c:10:0:
oracle_fdw.c: In function ‘getOracleWhereClause’:
/u01/app/postgres/product/95/db_2/include/server/fmgr.h:197:27: error: too few arguments to function ‘array_create_iterator’
  pg_detoast_datum((struct varlena *) DatumGetPointer(datum))
                           ^
/u01/app/postgres/product/95/db_2/include/server/utils/array.h:242:49: note: in expansion of macro ‘PG_DETOAST_DATUM’
 #define DatumGetArrayTypeP(X)    ((ArrayType *) PG_DETOAST_DATUM(X))
                                                 ^
oracle_fdw.c:2610:37: note: in expansion of macro ‘DatumGetArrayTypeP’
    iterator = array_create_iterator(DatumGetArrayTypeP(constant->constvalue), 0);
                                     ^
In file included from /u01/app/postgres/product/95/db_2/include/server/utils/acl.h:29:0,
                 from /u01/app/postgres/product/95/db_2/include/server/catalog/objectaddress.h:18,
                 from /u01/app/postgres/product/95/db_2/include/server/catalog/pg_operator.h:26,
                 from oracle_fdw.c:26:
/u01/app/postgres/product/95/db_2/include/server/utils/array.h:448:22: note: declared here
 extern ArrayIterator array_create_iterator(ArrayType *arr, int slice_ndim, ArrayMetaState *mstate);
                      ^
oracle_fdw.c: In function ‘transactionCallback’:
oracle_fdw.c:4263:2: warning: enumeration value ‘XACT_EVENT_PARALLEL_COMMIT’ not handled in switch [-Wswitch]
  switch(event)
  ^
oracle_fdw.c:4263:2: warning: enumeration value ‘XACT_EVENT_PARALLEL_ABORT’ not handled in switch [-Wswitch]
oracle_fdw.c:4263:2: warning: enumeration value ‘XACT_EVENT_PARALLEL_PRE_COMMIT’ not handled in switch [-Wswitch]
oracle_fdw.c: In function ‘oracleGetForeignPlan’:
oracle_fdw.c:840:1: warning: control reaches end of non-void function [-Wreturn-type]
 }
 ^
make: *** [oracle_fdw.o] Error 1

Oracle Linux 7.1

cat /etc/oracle-release 
Oracle Linux Server release 7.1

PostgreSQL 9.5 alpha2 compiled from source. The same issue occurs with alpha1. On the same machine with PostgreSQL 9.4.4 there are no issues. Any idea?

Thanks in advance
Daniel

On Oracle-side i have this simple table:
CREATE TABLE bugs (
nummer integer,
inhalt clob);

On Postgres i have a correspondig foreign table:
create foreign table bugs (
nummer integer,
inhalt text
) server oracledb options (schema 'POSTGRES',table 'BUGS');

The server is up an running, user permissions seems fine, too.
An insert statement on the "nummer" column works fine:
insert into bugs(nummer) value (12);

But every insert statement including the "inhalt" column causes a crash of PostgreSQL, for example
insert into bugs(inhalt) value ('aa');

A restart of the Postgres service is required to continue.

The effect is the same on every configuration i tested, including Postgres on Win XP (32 bit) and Windows 8.1 (64 bit) and Oracle 11g R2 Express on Windows XP (32 bit) and Windows 81. (64 bit) and every combination of those.

Here is the result of select oracle_diag('oracledb');
"oracle_fdw 1.0.0, PostgreSQL 9.3.5, Oracle client 11.2.0.2.0, Oracle server 11.2.0.2.0"

I'm using the binarys availabe for download here on github for Postgres 9.3.

I'm assuming that the crash shouldn't happen. I'm quiet sure im not doing anything wrong, but i might also be wrong about that. As a final piece of information the postgres server log:
2014-08-22 10:56:52 CEST LOG: Serverprozess (PID 5968) wurde durch Ausnahme 0xC0000005 beendet
2014-08-22 10:56:52 CEST DETAIL: Der fehlgeschlagene Prozess führte aus: insert into bugs(inhalt) values ('aa');
2014-08-22 10:56:52 CEST TIPP: Sehen Sie die Beschreibung des Hexadezimalwerts in der C-Include-Datei „ntstatus.h“ nach.
2014-08-22 10:56:52 CEST LOG: aktive Serverprozesse werden abgebrochen
2014-08-22 10:56:52 CEST WARNUNG: breche Verbindung ab wegen Absturz eines anderen Serverprozesses
2014-08-22 10:56:52 CEST DETAIL: Der Postmaster hat diesen Serverprozess angewiesen, die aktuelle Transaktion zurückzurollen und die Sitzung zu beenden, weil ein anderer Serverprozess abnormal beendet wurde und möglicherweise das Shared Memory verfälscht hat.
2014-08-22 10:56:52 CEST TIPP: In einem Moment sollten Sie wieder mit der Datenbank verbinden und Ihren Befehl wiederholen können.
2014-08-22 10:56:52 CEST LOG: alle Serverprozesse beendet; initialisiere neu
2014-08-22 10:57:02 CEST FATAL: bereits bestehender Shared-Memory-Block wird noch benutzt
2014-08-22 10:57:02 CEST TIPP: Prüfen Sie, ob irgendwelche alten Serverprozesse noch laufen und beenden Sie diese.

I hope there is a fix for this problem. It would be very nice to use CLOB columns on oracle side. I found a workaround for my application, but it is not very fast and my guess is that using CLOB columns would help to increase performance in my case significantly.

I am using an Oracle wallet. In ~postgres/.bashrc I have:

export TNS_ADMIN=/path/to/wallet/dir

In psql:

select * from remote_table;

works and returns rows.

If I remove "export TNS_ADMIN" from .bashrc, then pg_ctl restart, then psql:

select * from remote_table;

returns:

ERROR: session for foreign table "remote_table" cannot be authenticated
DETAIL: ORA-01017: invalid username/password; logon denied

which is to be expected.

However, I would expect in psql:

\setenv TNS_ADMIN /path/to/wallet/dir

to fix the error. It appears oracle_fdw is not affected by the psql client using \setenv. Is this by design? Can it be changed?

psql=> select oracle_diag('dvlp');
oracle_fdw 1.1.0, PostgreSQL 9.3.5, Oracle client 12.1.0.2.0, Oracle server 11.2.0.3.0
(1 row)

psql=> select oracle_diag();
oracle_fdw 1.1.0, PostgreSQL 9.3.5, Oracle client 12.1.0.2.0, ORACLE_HOME=/apps/local/instantclient, TNS_ADMIN=/wrong/path
(1 row)

psql=> \setenv TNS_ADMIN /path/to/wallet/dir

psql=> select oracle_diag();
oracle_fdw 1.1.0, PostgreSQL 9.3.5, Oracle client 12.1.0.2.0, ORACLE_HOME=/apps/local/instantclient, TNS_ADMIN=/wrong/path
(1 row)

This is more of a feature request than issue. The Oracle ROWID type (SQLT_RDD in the OCI constants) is not currently supported, so any columns that come after a ROWID typed column cannot be accessed with oracle_fdw.

ERROR: column "rowid" of foreign table "some_foreign_table" cannot be converted to or from Oracle data type
SQL state: HV004

Mapping an oracle timestamp(0) field as timestamp(0) or date gives the following error:

ERROR: invalid input syntax for type timestamp: "1977-06-09 18:00:00."

It seems that oracle answers appending a point, even if the field is defined timestamp(0), and oracle_fdw is unable to handle that.

Follow the output of oracle_diag():

oracle_fdw 1.2.0, PostgreSQL 9.4.5, Oracle client 11.2.0.4.0, Oracle server 11.2.0.4.0

P.S.
Temporary I solved mapping the field as varchar.

Hi,
would it be possible to add the option "readonly" to the IMPORT FOREIGN SCHEMA command ?
I'd like to bulk import multiple tables, but only in "readonly" mode.

As the option is available for CREATE FOREIGN TABLE, does it make sense ?

Hi - I had the same problem (& solution!) described in this post:

On Tue, Nov 20, 2012 at 10:40 AM, Albe Laurenz <laurenz.albe at wien.gv.at> wrote:

So /usr/lib/oracle/11.2/client64/lib is in
/etc/ld.so.conf and you ran ldconfig, right?
That should be good enough (better than the dreaded
LD_LIBRARY_PATH, actually).
Did you restart the PostgreSQL server after you did that?

ok, i think i put it wrong on ld.so.conf... anyway i create a new
oracle.conf file on ld.so.conf.d/ run ldconfig again with -v option
and now i see oracle libs.

i restarted postgresql and tried again... and it works.
thanks a lot

Jaime Casanova

(http://lists.pgfoundry.org/pipermail/oracle-fdw-general/2014q3/000138.html)

I wanted to update the mailing list with some additional info but when I try to access the mailing list I get an error:

Forbidden

You don't have permission to access /mailman/listinfo/oracle-fdw-general on this server.

Is the mailing list still active? Has it moved?

Thanks,
Alan Thompson

I'm triing to create a materialized view:

CREATE MATERIALIZED VIEW base_ext.fdw_pastel_mouvements_vmat_m AS
SELECT * FROM base_ext.fdw_pastel_mouvements m
WHERE date_part('year'::text, m.dt_fin) = date_part('year'::text, now()) AND date_part('month'::text, m.dt_fin) = date_part('month'::text, now())
WITH DATA;

but i have this kind of error:

ERREUR: error executing query: OCIDateTimeFromText failed to convert parameter
DETAIL: ORA-01843: ce n'est pas un mois valide
********** Erreur **********

ERREUR: error executing query: OCIDateTimeFromText failed to convert parameter
État SQL :HV00L
Détail :ORA-01843: ce n'est pas un mois valide

After testing this:

CREATE MATERIALIZED VIEW base_ext.fdw_pastel_mouvements_vmat_m AS
SELECT * FROM base_ext.fdw_pastel_mouvements m
WHERE date_part('year'::text, m.dt_fin) = date_part('year'::text, now()) AND date_part('month'::text, m.dt_fin) = 2
WITH DATA;

i have the same issue...
On a Windows 2008R2 server (9.3) it works but not on a debian one (9.5 with instant client 12.1)

Did i forget anything in the configuration?

Hye Laurenz.
I get an error at compilation with PostgreSQL 9.5b1 and Oracle client 11g.
See below.
However I've dowloaded the fdw_master.zip
Thanks for your help.
Regards.

[yep@postgres-oracle_fdw-master]$ which pg_config
/usr/pgsql/bin/pg_config

[yep@postgres-oracle_fdw-master]$ make
gcc -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -O2 -g -pipe -Wall -Wp,-D_FORTIFY_SOURCE=2 -fexceptions -fstack-protector-strong --param=ssp-buffer-size=4 -grecord-gcc-switches -m64 -mtune=generic -DLINUX_OOM_ADJ=0 -fpic -I/home/oracle/11g/client/sdk/include -I/home/oracle/11g/client/oci/include -I/home/oracle/11g/client/rdbms/public -I/usr/include/oracle/12.1/client -I/usr/include/oracle/12.1/client64 -I/usr/include/oracle/11.2/client -I/usr/include/oracle/11.2/client64 -I/usr/include/oracle/11.1/client -I/usr/include/oracle/11.1/client64 -I/usr/include/oracle/10.2.0.5/client -I/usr/include/oracle/10.2.0.5/client64 -I/usr/include/oracle/10.2.0.4/client -I/usr/include/oracle/10.2.0.4/client64 -I/usr/include/oracle/10.2.0.3/client -I/usr/include/oracle/10.2.0.3/client64 -I. -I./ -I/usr/pgsql-9.5/include/server -I/usr/pgsql-9.5/include/internal -D_GNU_SOURCE -I/usr/include/libxml2 -I/usr/include -c -o oracle_fdw.o oracle_fdw.c
oracle_fdw.c: In function 'oracleGetForeignPlan':
oracle_fdw.c:876:8: error: too many arguments to function 'make_foreignscan'
);
^
In file included from oracle_fdw.c:43:0:
/usr/pgsql-9.5/include/server/optimizer/planmain.h:46:21: note: declared here
extern ForeignScan make_foreignscan(List *qptlist, List *qpqual,
^
make: ** [oracle_fdw.o] Error 1

Hello!
I got the table :
CREATE FOREIGN TABLE dop_p_ft2( user_sys varchar (60) OPTIONS (key 'true'), id_dd bigint) SERVER oradb OPTIONS (schema 'INFOSTREAM_D', table 'DOP_P');

After
delete from dop_p_ft2 ive got :
ERROR: DELETE on Oracle table removed 7 rows instead of one in iteration 1
HINT: This probably means that you did not set the "key" option on all primary key columns.

In source code i found in oracle_fdw.c:

if (rows != 1)
ereport(ERROR,
(errcode(ERRCODE_FDW_UNABLE_TO_CREATE_EXECUTION),
errmsg("DELETE on Oracle table removed %d rows instead of one in iteration %lu", rows, fdw_state->rowcount),
errhint("This probably means that you did not set the "key" option on all primary key columns.")));

That means that i cant delete more than one row in time when unsing oracle_fdw?
Thank you in advance!

Hello,

A field in our source is a FLOAT. The field that we've chosen for the foreign table in postgres is a FLOAT4. When pulling back data, the values are slightly off in most cases.

For instance here are 10 examples

COLUMN (FDW) COLUMN (Oracle) DIFF
32935140 32935141 1
37645816 37645818 2
33514182 33514182 0
33615264 33615264 0
31862788 31862787 -1
33149166 33149166 0
31849372 31849373 1
33106476 33106475 -1
36868032 36868031 -1

We surmised that the issue was with the type conversion. We tried to use bigint in the foreign table instead and it worked like a charm.

Any thoughts on why it's not converting appropriately?

Thanks!

Oracle - Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
PostGres - PostgreSQL 9.4.0, compiled by Visual C++ build 1800, 64-bit
FDW - Version 1.1

Hi, be possible to add the IMPORT FOREIGN TABLE command ?

Hi,
I encountered a problem while trying to read spatial data from a database in which there is the SDE scheme of ArchGIS software; the database has oracle_fdw, postgis and postgis_topology extensions installed.
I found that in the SDE schema are replicated some Postgis function, particularly the ST_GeomFromWKB function.
In your code (oracle_fdw.c) is a check on this function, if found duplicated in schemas other the "public", it generates an error preventing the conversion of spatial data.

Extracto from postgres log:
ERROR: column "xxx" of foreign table "yyy" can not be converted to or from Oracle data types

From another database of the same postgres instance, without SDE schema, I could read spacial data successfuly without errors.

Hi,

Thanks for the work on this extension.

I'm running PostgreSQL 9.4 on Windows 64-bit. I've downloaded the binary package and copied all of the files to their respective folder locations. When I try to run CREATE EXTENSION oracle_fdw, I get the following error: "ERROR: could not load library "C:/Program Files/PostgreSQL/9.4/lib/oracle_fdw.dll": The specified module could not be found.
SQL state: XX000". Any thoughts on why I'm encountering this issue?

When I execute "SELECT name, default_version, installed_version, left( comment, 30) As comment FROM pg_available_extensions" your extension does appear as available. Just can't get it to install.

Thanks.

Hello,

I think this would be directed to Vincent. I am a little perplexed by the built in equivalents in the epsgFromOracle and epsgToOracle functions. I am not sure if this some kind of continental difference or what. But 4269 as far as I know is a truly North American coordinate system based on NAD83.
http://spatialreference.org/ref/epsg/4269/
It should always equate to 8265 in Oracle. What is this 8267 primed onto Paris?

Similarly WGS84 is the ubiquitous global system at 4326
http://spatialreference.org/ref/epsg/4326/
which I have always equated to 8307. Looking at 8307 and 8192 I suppose they are equivalent. So this does seem like a valid disagreement.

I am wondering if you should do this at all, perhaps it would be better to just equate PostGIS 4269 to Oracle 4269 and if the user is using 9i or 10gR1 (less and less likely), then they need to edit the srid file to match an older Oracle srid to the PostGIS srid.

Cheers,
Paul

switch (srid)
{
case 8192: return 4326; /* WGS84 /
case 8306: return 4322; / WGS72 /
case 8267: return 4269; / NAD83 /
case 8274: return 4277; / OSGB 36 /
case 81989: return 27700; / UK National Grid */
default: return srid;
}

@vmora, I just got a problem report by somebody who uses this Oracle geometry:

MDSYS.SDO_GEOMETRY(
   2001,2193,NULL,
   MDSYS.SDO_ELEM_INFO_ARRAY(1,0,6000,4,1,1),
   MDSYS.SDO_ORDINATE_ARRAY(1,0,0,1803127,5801479)
)

This causes the following error message:

ERROR:  error converting SDO_GEOMETRY to geometry: unsupported etype 0 with interpretation 6000 in elem_info

That is fundamentally OK because according to the documentation a value of 0 means

Type 0 (zero) element. Used to model geometry types not supported by Oracle Spatial.*

But later on I read:

Geometries with type 0 elements must contain at least one nonzero element, that is, an element with an SDO_ETYPE value that is not 0. The nonzero element should be an approximation of the unsupported geometry, and therefore it must have both:

• An SDO_ETYPE value associated with a geometry type supported by Spatial
• An SDO_INTERPRETATION value that is valid for the SDO_ETYPE value (see Table 2-2)

Wouldn't the correct thing be to skip the part with ETYPE = 0 and use the approximation instead?
In this case it would be a point.

defination of makeConst():

Const *
makeConst(Oid consttype,
          int32 consttypmod,
          Oid constcollid,
          int constlen,
          Datum constvalue,
          bool constisnull,
          bool constbyval)
{
}

so following is the patch:

diff --git a/oracle_fdw.c b/oracle_fdw.c
index 2867436..e184832 100644
--- a/oracle_fdw.c
+++ b/oracle_fdw.c
@@ -4004,15 +4004,15 @@ Const
 *serializeLong(long i)
 {
        if (sizeof(long) <= 4)
-               return makeConst(INT4OID, -1, InvalidOid, 4, Int32GetDatum((int32)i), 1, 0);
+               return makeConst(INT4OID, -1, InvalidOid, 4, Int32GetDatum((int32)i), false, true);
        else
-               return makeConst(INT4OID, -1, InvalidOid, 8, Int64GetDatum((int64)i),
+               return makeConst(INT8OID, -1, InvalidOid, 8, Int64GetDatum((int64)i), false,
 #ifdef USE_FLOAT8_BYVAL
-                               1,
+                               true
 #else
-                               0,
+                               false
 #endif  /* USE_FLOAT8_BYVAL */
-                               0);
+                               );
 }

@vmora, I just got this e-mail from Dominique Legendre:

Now, we have a problem with Oracle legacy srid for postgis geometry.
In France, we use some projected coord sys named "IGN NTF Lambert ...".
The "Lambert 2 étendu" is the 27572 epsg srid. Due to history, our data
use Oracle legacy srid= 41014.

In the fdw code, you define 2 functions to translate legacy srid to epsg
and epsg to legacy srid (epsgToOracle and epsgFromOracle). Of course the
41014/27572 convert is not included.
Another point: the legacy srid for wgs84 is 8307 not 8192.

May be, it's possible to use the sdo_cs.map_oracle_srid_to_epsg and the
sdo_cs.map_epsg_srid to Oracle functions from Oracle.
For example :
select sdo_cs.map_epsg_srid_to_oracle(4326) from dual;
LEGACY_SRID
-----------
8307

Often, the convert function returns null, not the good srid, but it's
possible to fill the missing with a update
update sdo_coord_ref_sys set legacy_code=<le_code_legacy> where srid = <le_code_epsg>;
like update sdo_coord_ref_sys set legacy_code=41014 where srid = 27572;

I think it's a better solution.

What do you think?
Is it possible to get a list of all such mappings?
Does everybody agree on these mappings or will we have to come up with something configurable?
I'd like to avoid calling Oracle's functions (costly and messy), but maybe we should use the data from SDO_COORD_REF_SYS to improve our mapping functions.

our application ceate a session to postgres, and send a query to postgres then waiting many times to do something else, when we send another query, it occur error like this:

ERROR: error connecting to Oracle: OCITransStart failed to start a transaction 
Detail: ORA-02396: exceeded maximum idle time, please connect again

it means the lag between twice query send over idle_time,.but I think fwd should automatic connect to oracle again, isn't it?
the whole process our application is connect to postgres by on session.
the idle_time setting in oracle like this:

RESOURCE_NAME                    LIMIT
-------------------------------- ----------------------------------------
IDLE_TIME                        60

This is an example query to demonstrate the behavior I'm seeing:

SELECT 
    a.*, b.*, c.* 
FROM main_table A
LEFT JOIN join_table B ON
    A.key = B.key AND B.column = <some discrete value>
LEFT JOIN join_table C ON
    A.key = C.key AND C.column = <some discrete value>
LIMIT 1;

Where both main_table and join_table are foreign tables created with oracle_fdw.

When running this query via psql no response is ever generated. However, if join_table is joined only once:

SELECT 
    a.*, b.*
FROM main_table A
LEFT JOIN join_table B ON
    A.key = B.key AND B.column = <some discrete value>
LIMIT 1;

The expected rows are returned just fine.

I tested the same queries against the foreign Oracle server and it returns the appropriate rows almost instantaneously in both the single join and double join situation.

Known issue? Unsupported behavior?

PG some process is restart ,why oracle_fdw is can't connect to Oracle.
is can't find handle,environment is cache?

If I have this table in an Oracle 11G R2 Enterprise database

CREATE TABLE "GC"."TIB"
( "LAST_UPDATE" DATE,
"INCIDENT_ID" NUMBER NOT NULL ENABLE,
"PROBLEM_ID" NUMBER NOT NULL ENABLE,
"CUSTOMER_ID" VARCHAR2(16) NOT NULL ENABLE,
"CONTACT_ID" NUMBER,
"OWNER_ID" VARCHAR2(16),
"DEPARTMENT_ID" NUMBER,
"INCIDENT_STAT_ID" NUMBER NOT NULL ENABLE,
"DATE_OPENED" DATE NOT NULL ENABLE,
"REFRESH_ID" NUMBER NOT NULL ENABLE,
"PRIORITY_ID" NUMBER,
"NOTIFY_OWNER" VARCHAR2(1),
"SEVERITY_ID" NUMBER,
"SHORT_DESC" VARCHAR2(120),
"LONG_DESC" VARCHAR2(4000),
"PBLONG_DESC" CLOB,
"DATE_CLOSED" DATE,
"CLOSE_DESC" VARCHAR2(4000),
"PBCLOSE_DESC" CLOB,
"CLOSE_REASON_ID" NUMBER,
"TEMPORARY_FIX_ID" NUMBER,
"DATE_FIX_SHIPPED" DATE,
"DT_LAST_STAT_CHG" DATE,
"SOURCE_ID" NUMBER,
"LICENSE_NUMBER" VARCHAR2(30),
"NOT_UNDER_MAINT" VARCHAR2(1),
"TEST_ID" VARCHAR2(64),
"CUST_NEEDS_EST" VARCHAR2(1),
"EST_NUM_DAYS" FLOAT(126),
"ESTIMATED_COST" FLOAT(126),
"DATE_PROMISED" DATE,
"MAINT_TYPE_ID" NUMBER,
"MISC1" VARCHAR2(40),
"MISC2" VARCHAR2(40),
"MISC3" VARCHAR2(40),
"MISC4" VARCHAR2(40),
"MISC5" VARCHAR2(40),
"MISC6" VARCHAR2(40),
"MISC7" VARCHAR2(40),
"MISC8" VARCHAR2(40),
"MISC9" VARCHAR2(40),
"MISC10" VARCHAR2(40),
"MISC11" VARCHAR2(40),
"MISC12" VARCHAR2(40),
"MISC13" VARCHAR2(40),
"MISC14" VARCHAR2(40),
"MISC15" VARCHAR2(40),
"MISC16" VARCHAR2(40),
"CONTRACT_ID" VARCHAR2(30),
"APPLIED" VARCHAR2(1),
"CALLBACK_HOURS" FLOAT(126),
"DAYS_TO_CLOSE" FLOAT(126),
"CLOSED_IMMED" VARCHAR2(1),
"ALTERNATE_PHONE" VARCHAR2(32),
"PAYMENT_REFERENCE" VARCHAR2(40),
"INC_PAY_TYPE_ID" NUMBER,
"INCOMPLETE_FLAG" NUMBER,
"INC_ORIGIN_ID" NUMBER,
"PROJECT_ID" NUMBER,
"PUBLIC_INCIDENT" VARCHAR2(1),
"ELAPSED_WORK_DAYS" FLOAT(126),
"ELAPSED_WORK_HRS" FLOAT(126),
"LAST_CALC_DATE" DATE,
"MISC_DROPDOWN1_ID" NUMBER,
"MISC_DROPDOWN2_ID" NUMBER,
"MISC_DROPDOWN3_ID" NUMBER,
"MISC_DROPDOWN4_ID" NUMBER,
"MISC_DROPDOWN5_ID" NUMBER,
"MISC_DROPDOWN6_ID" NUMBER,
"MISC_DROPDOWN7_ID" NUMBER,
"MISC_DROPDOWN8_ID" NUMBER,
"MISC_DROPDOWN9_ID" NUMBER,
"MISC_DROPDOWN10_ID" NUMBER,
"MISC_DROPDOWN11_ID" NUMBER,
"MISC_DROPDOWN12_ID" NUMBER,
"MISC_DROPDOWN13_ID" NUMBER,
"MISC_DROPDOWN14_ID" NUMBER,
"MISC_DROPDOWN15_ID" NUMBER,
"MISC_DROPDOWN16_ID" NUMBER,
"MISC_DROPDOWN17_ID" NUMBER,
"MISC_DROPDOWN18_ID" NUMBER,
"MISC_DROPDOWN19_ID" NUMBER,
"MISC_DROPDOWN20_ID" NUMBER,
"MISC_DATE1" DATE,
"MISC_DATE2" DATE,
"MISC_DATE3" DATE,
"MISC_DATE4" DATE,
"MISC_DATE5" DATE,
"MISC_DATE6" DATE,
"MISC_DATE7" DATE,
"MISC_DATE8" DATE,
"PRIMARY_PROG_ID" NUMBER,
"LAST_ACTIVITY_DATE" DATE,
"MISC_CUST_ID1" VARCHAR2(16),
"SOURCE_INCIDENT_ID" VARCHAR2(32),
"MISC_DT_DATE1" DATE,
"MISC_DT_DATE2" DATE,
"CALLBACK_DATE" DATE,
"PERCENT_COMPLETED" FLOAT(126),
"MISC_FLAG1" VARCHAR2(1),
"MISC_FLAG2" VARCHAR2(1),
"MISC_FLAG3" VARCHAR2(1),
"MISC_NUM1" FLOAT(126),
"MISC_NUM2" FLOAT(126),
"MISC_NUM3" FLOAT(126),
"MISC_PERSON1_ID" VARCHAR2(16),
"TOTAL_TIME" FLOAT(126),
"MISC_TEXT1" VARCHAR2(4000),
"MISC_TEXT2" VARCHAR2(4000),
"MISC_TEXT3" VARCHAR2(4000),
"MISC_TEXT4" VARCHAR2(4000),
"TOTAL_TASK_TIME" FLOAT(126),
"REOPENED" NUMBER,
"CHAR_LOCKED" VARCHAR2(1),
"SLA_HRS" FLOAT(126),
"EVENT_DATE_CHGD" DATE,
"FROM_ADDR_ID" NUMBER,
"FROM_ADDR_SOURCE" VARCHAR2(1),
"WRK_CAL_DTL_ID" NUMBER,
"WRK_CAL_SOURCE_ID" NUMBER,
"WRK_CALENDAR_DT" DATE,
"ELAPSED_RECALC_DT" DATE,
"MISC_CURR1" FLOAT(126),
"MISC_CURR2" FLOAT(126),
"MISC_CURR3" FLOAT(126),
"CONTACT2_ID" NUMBER,
"CONTACT3_ID" NUMBER,
"TOTAL_BILL_TIME" FLOAT(126),
"TOTAL_ABSORB_TIME" FLOAT(126),
"TOTAL_OTHER_TIME" FLOAT(126),
"TOTAL_TIME1" FLOAT(126),
"TOTAL_TIME2" FLOAT(126),
"TOTAL_TIME3" FLOAT(126),
"TOT_TASK_BILL_TM" FLOAT(126),
"TOT_TASK_ABS_TM" FLOAT(126),
"TOT_TASK_OTH_TM" FLOAT(126),
"TOT_TASK_TIME1" FLOAT(126),
"TOT_TASK_TIME2" FLOAT(126),
"TOT_TASK_TIME3" FLOAT(126),
"TOT_INCTSK_BILL_TM" FLOAT(126),
"TOT_INCTSK_ABS_TM" FLOAT(126),
"TOT_INCTSK_OTH_TM" FLOAT(126),
"TOT_INCTSK_TIME1" FLOAT(126),
"TOT_INCTSK_TIME2" FLOAT(126),
"TOT_INCTSK_TIME3" FLOAT(126),
"TOT_INCTSK_TOT_TM" FLOAT(126),
"DEALER_ID" VARCHAR2(16)
)

I try and create a postgres link like this:

=> create foreign table tib (INCIDENT_ID numeric) server rac options (schema 'GC',table 'TIB');
CREATE FOREIGN TABLE
=> select * from tib;
ERROR: column "incident_id" of foreign table "tib" cannot be converted to or from Oracle data type 9 scale 0

I added a display of the oracle data type enum into the error message

if I create a view on the INCIDENT_ID column, and make a foreign table on that, then I can select it fine.

I don't know if it's the number of columns, or the number of big varchar2 columns that are in there.

If you need more info, happy to provide.

I'm using oracle_fdw-0.9.10 built from source on Cent OS 6.5 x86_64

With

-bash-4.1$ psql --version
psql (PostgreSQL) 9.3.2

Hi - We are trying to use oracle_fdw to access a legacy Oracle DB in the pharmaceutical industry. I got the oracle_fdw installed & working last night on an AWS instance using postgres 9.4.1 on Ubuntu.

An unexpected problem is that Oracle requires a function call "pkg_rls.set_context ('admin', '1', 'username', 'password') to be the very first thing performed by each connection to the DB. This is used to control "multi-tenancy" (the name derives from the Package "Row Level Security" and the "Enterprise Context"). Are you familiar with this at all? If one fails to issue this function call, all select statements return zero rows (with no warning).

If accessing the ODB with a GUI like Squirrel, sqlplus, or SQL Developer, this is merely an annoyance. However, since this is a non-standard Oracle-only requirement, tools like oracle_fdw, Teiid, etc are not expecting it and they either fail or require awkward workarounds. Accessing the ODB via JDBC is possible, but pkg_rls.set_context(...) must be called as the first operation for each connection or no table rows are visible. This complicates even simple programs since the connection must be cached and the function must be called again in the event a connection is dropped & new one established.

For the time-being, I'm going to use Clojure & JDBC to export the data from the ODB for testing in a local pg instance. In the long term, it would be nicer if we could extend the oracle_fdw extension to accommodate the pkg_rls.set_context(...) weirdness.

Have you ever encountered pkg_rls.set_context(...) before? Do you have any suggestions on the best way to extend oracle_fdw to accomodate a "prelude" of sorts?

Thanks for a great extension,
Alan Thompson

Hello,
is there any plan to add this fonctionnality ?
I use this extension in order to aggregate data from multiple remote oracle databases.
I don't have write access on these oracle databases, so I'm not able to create views on them to optimize the complex queries execution and fetching.
This option would allow these optimizations without having to have write access on the remote databases.

Thanks again for this useful extension! 🎆

Hello,
when trying to set "key" option on FOREIGN TABLE postgresql gives me this error:

ERROR: invalid option "key"
HINT: Valid options in this context are: schema, table, plan_costs, max_long, readonly

oracle_diag: oracle_fdw 1.0.0, PostgreSQL 9.3.2, Oracle client 11.2.0.3.0, Oracle server 11.2.0.3.0

EDIT:
same result on second server (oracle_fdw 1.0.0, PostgreSQL 9.3.4, Oracle client 11.2.0.3.0, ORACLE_HOME=/usr/lib64/oracle/11.2.0.3/client, TNS_ADMIN=/etc/oracle/)

(See also #57 )

ERROR:  length for type char must be at least 1
LINE 1: ...ic, "flags" numeric, "colprop" numeric, "adtname" character(...
                                                             ^
QUERY:  CREATE FOREIGN TABLE "exu8col" ("tobjid" numeric, "towner" character varying(30), "townerid" numeric, "tname" character varying(30), "name" character varying(30), "length" numeric, "precision" numeric, "scale" numeric, "type" numeric, "isnull" numeric, "conname" character varying(30), "colid" numeric, "intcolid" numeric, "segcolid" numeric, "comment$" character varying(4000), "default$" text, "dfltlen" numeric, "enabled" numeric, "defer" numeric, "flags" numeric, "colprop" numeric, "adtname" character(0), "adtowner" character(0), "charsetid" numeric, "charsetform" numeric, "fsprecision" numeric, "lfprecision" numeric, "charlen" numeric, "tflags" numeric) SERVER "oracle" OPTIONS (schema 'SYS', table 'EXU8COL', readonly 'true')
CONTEXT:  importing foreign table "exu8col"

Running this command:

CREATE SCHEMA IF NOT EXISTS sys;

IMPORT FOREIGN SCHEMA "SYS"
  FROM SERVER oracle
  INTO sys
  OPTIONS( readonly 'true' )
;

Hi Laurenz,

I was wondering if there is any interest in supporting LRS spatial measures in oracle_fdw. In looking through the code it seems excluded (particularly in how you process the gtype) though it's not any more complicated than any other 3D information that you already support.

Cheers,
Paul

I am having trouble with the environment variables running postgres 9.3 on CentOS 7. When I start the database with pg_ctl, the Oracle environment variables are set appropriately and the foreign data wrapper works. When I start the database from systemctl then I get the following error:

ERROR: could not load library "/usr/pgsql-9.3/lib/oracle_fdw.so": libclntsh.so.11.1: cannot open shared object file: No such file or directory

I may not have the best solution, but I was able to resolve the issue by removing the environment variable references from new environment variables...

So, I changed the following
Environment=PGDATA=/var/lib/pgsql/9.3/data/
Environment=ORACLE_VERSION=11.2
Environment=ORACLE_HOME=/usr/lib/oracle/${ORACLE_VERSION}/client64
Environment=PATH=$PATH:${ORACLE_HOME}/bin
Environment=LD_LIBRARY_PATH=${ORACLE_HOME}/lib
Environment=TNS_ADMIN=${ORACLE_HOME}/network/admin

To
Environment=PGDATA=/var/lib/pgsql/9.3/data/
Environment=ORACLE_VERSION=11.2
Environment=ORACLE_HOME=/usr/lib/oracle/11.2/client64
Environment=PATH=$PATH:/usr/lib/oracle/11.2/client64/bin
Environment=LD_LIBRARY_PATH=/usr/lib/oracle/11.2/client64/lib
Environment=TNS_ADMIN=/usr/lib/oracle/11.2/client64/network/admin

Hope this helps someone.

Executing any query with now() or current_date in the WHERE clause crashes Postgre in my environment. For example, the following query:

SELECT 1 FROM FOREIGN_TABLE WHERE current_date=current_date
Error: server process (PID 2404) was terminated by exception 0xC0000005

EXPLAIN VERBOSE:
Output: 1
One-Time Filter: (('now'::cstring)::date = ('now'::cstring)::date)
-> Foreign Scan on public.foreign_table (cost=10000.00..10000.00 rows=1000 width=0)
Oracle query: SELECT /27044e1d4cb9a9ea5fb63948080dbec3/ '1' FROM "SCOTT"."EMP" WHERE (TRUNC(CAST (:now AS DATE)) = TRUNC(CAST (:now AS DATE)))
Oracle plan: SELECT STATEMENT
Oracle plan: FILTER (filter TRUNC(CAST(:NOW AS DATE))=TRUNC(CAST(:NOW AS DATE)))
Oracle plan: INDEX FULL SCAN EMP_PK

Server OS: Windows
oracle_diag(): oracle_fdw 1.1.0, PostgreSQL 9.4.0, Oracle client 10.2.0.5.0, Oracle server 10.2.0.4.0

Some problem with binding for :now perhaps? The relevant code seems to be here.

I have implemented your FDW on our Postgres 9.3 server. We are connecting to an Oracle 11g R2 instance. The problem we are running into is that it is taking around 3 to 4 minutes to pull back approximately 2000 to 3000 records. We have checked the connection between the two servers by pinging them. The return time is .34ms, so the connection speed is good. That leads us to believe there is an issue with the performance of the FDW. Therefore, we would like to know if there is any way to tweak the performance of the FDW?

Thanks,

Kris

It's in 9.5-to-be :)

Might it be worthwhile trying to do some kind of type mapping, per

Setting up oracle instant client may be not so easy, but using alien to convert rpm packages to deb files in Debian works quite well. Nevertheless, when one needs tnsnames.ora, there must be set an environment variable TNS_ADMIN that leads to the location of the tnsnames.ora. I didn't find it easy to configure this in oracle_fdw. But there's an easy solution: oracle instant client searches for tnsnames.ora in /etc, when there is no TNS_ADMIN set. It would be great to have this hint in your readme on setup instructions to save time for oracle_fdw users as tnsnames.ora is very common to oracle users.

I got many warnings like below when I built oracle_fdw on OS X.

oracle_fdw.c:2448:10: warning: expression which evaluates to zero treated as a null pointer constant of type 'char *' [-Wnon-literal-null-conversion]
                return false;
                       ^~~~~
/Users/hanada/pgsql/dev/include/server/c.h:187:15: note: expanded from macro 'false'
#define false   ((bool) 0)
                ^~~~~~~~~~

The cause would be that getOracleWhereClause uses false as return value where a pointer is required. return NULL; would be correct code for such case.

Mapping an oracle timestamp(6) field as timestamp(6) gives the following error:

ERREUR: error executing query: OCIStmtExecute failed to execute remote query
État SQL :HV00L
Détail :ORA-01406: la valeur de la colonne extraite a été tronquée

Follow the output of oracle_diag():

oracle_fdw 1.3.0, PostgreSQL 9.5.0, Oracle client 12.1.0.2.0

Hi. thank you for this nice project. I was able to get oracle_fdw running with a postgres 9.4 server. But on the same machine I doesn't work with 9.5.

Is it possible that the code already supports 9.5 but the compiled version not?

I'm running postgres on windows.

$ make
gcc -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -g -g -O2 -fstack-protector-strong -Wformat -Werror=format-security -I/usr/include/mit-krb5 -fPIC -pie -fno-omit-frame-pointer -fpic -I/sdk/include -I/oci/include -I/rdbms/public -I/usr/include/oracle/12.1/client -I/usr/include/oracle/12.1/client64 -I/usr/include/oracle/11.2/client -I/usr/include/oracle/11.2/client64 -I/usr/include/oracle/11.1/client -I/usr/include/oracle/11.1/client64 -I/usr/include/oracle/10.2.0.5/client -I/usr/include/oracle/10.2.0.5/client64 -I/usr/include/oracle/10.2.0.4/client -I/usr/include/oracle/10.2.0.4/client64 -I/usr/include/oracle/10.2.0.3/client -I/usr/include/oracle/10.2.0.3/client64 -I. -I./ -I/usr/include/postgresql/9.5/server -I/usr/include/postgresql/internal -D_FORTIFY_SOURCE=2 -D_GNU_SOURCE -I/usr/include/libxml2  -I/usr/include/tcl8.6  -c -o oracle_fdw.o oracle_fdw.c
oracle_fdw.c: In function ‘oracleGetForeignPlan’:
oracle_fdw.c:872:9: error: too many arguments to function ‘make_foreignscan’
  return make_foreignscan(tlist, keep_clauses, baserel->relid, fdwState->params, fdw_private
         ^
In file included from oracle_fdw.c:43:0:
/usr/include/postgresql/9.5/server/optimizer/planmain.h:46:21: note: declared here
 extern ForeignScan *make_foreignscan(List *qptlist, List *qpqual,
                     ^
<builtin>: recipe for target 'oracle_fdw.o' failed
make: *** [oracle_fdw.o] Error 1

Appears to be similar to issue #37