Обсуждение: Database link from Postgres to Oracle using ODBC

On 12/23/2013 11:59 PM, Bart Klein Ikink wrote:
> I am a newbie so I may have done something wrong. I have made a database
> link from Oracle 11.2 to Postgres 9.2 using unixODBC, Oracle HS and
> psqlODBC. It is working fine.
>
> Now I want to do it the other way around, which is making a database
> link from Postgres 9.2 to Oracle 11.2. I have figured out that I have to
> install ODBC-Link 1.0.4 or Oracle_fdw 0.9.9.
>
> I tried to install ODBC-Link 1.0.4 and Oracle_fdw 0.9.9, but both have
> similar problems as shown below (using ODBC-Link 1.0.4):
>
> [root]# export PATH=$PATH:/usr/pgsql-9.2/bin
> [root]# pg_config --pgxs
> /usr/pgsql-9.2/lib/pgxs/src/makefiles/pgxs.mk
> [root]# USE_PGXS=1 make
> gcc -O2 -g -pipe -Wall -Wp,-D_FORTIFY_SOURCE=2 -fexceptions
> -fstack-protector --param=ssp-buffer-size=4 -m64 -mtune=generic
> -I/usr/include/et -Wall -Wmissing-prototypes -Wpointer-arith
> -Wdeclaration-after-statement -Wendif-labels -Wmissing-format-attribute
> -Wformat-security -fno-strict-aliasing -fwrapv -fpic -I. -I.
> -I/usr/pgsql-9.2/include/server -I/usr/pgsql-9.2/include/internal
> -I/usr/include/et -D_GNU_SOURCE -I/usr/include/libxml2  -I/usr/include
> -c -o odbclink.o odbclink.c
> In file included from odbclink.c:17:
> odbclink.h:4:17: error: sql.h: No such file or directory
> odbclink.h:5:20: error: sqlext.h: No such file or directory

>
> What is the problem here?

The *.h files are supplied by unixodbc-devel, so do you have that
installed or are the files in the PATH where they can be found?

--
Adrian Klaver
adrian.klaver@gmail.com

On 01/06/2014 07:35 AM, Bart Klein Ikink wrote:
> I have included the path, but still having problems. What is wrong here?

> export PATH=$PATH:/root/test/unixODBC-2.3.2/include:/usr/pgsql-9.2/bin
>
> make USE_PGXS=1
> gcc -O2 -g -pipe -Wall -Wp,-D_FORTIFY_SOURCE=2 -fexceptions
> -fstack-protector --param=ssp-buffer-size=4 -m64 -mtune=generic
> -I/usr/include/et -Wall -Wmissing-prototypes -Wpointer-arith
> -Wdeclaration-after-statement -Wendif-labels -Wmissing-format-attribute
> -Wformat-security -fno-strict-aliasing -fwrapv -fpic -I. -I.
> -I/usr/pgsql-9.2/include/server -I/usr/pgsql-9.2/include/internal
> -I/usr/include/et -D_GNU_SOURCE -I/usr/include/libxml2  -I/usr/include
> -c -o odbclink.o odbclink.c
> odbclink.c: In function âget_dataâ:
> odbclink.c:746: error: âtimestamp_inâ undeclared (first use in this
> function)
> odbclink.c:746: error: (Each undeclared identifier is reported only once
> odbclink.c:746: error: for each function it appears in.)
> odbclink.c:749: error: âtimestamptz_inâ undeclared (first use in this
> function)
> make: *** [odbclink.o] Error 1
>

Not quite sure but I would say you do not have the Postgres development
files.
Did you install Postgresql 9.2 from source or from a package?
If from a package you will need to install the postgresql-devel package
for 9.2

--
Adrian Klaver
adrian.klaver@gmail.com

On 01/06/2014 11:02 AM, Bart Klein Ikink wrote:
> I have installed the Postgres development files from RPM.

So they where installed before you got your most recent error?


--
Adrian Klaver
adrian.klaver@gmail.com

On 01/06/2014 11:50 PM, Bart Klein Ikink wrote:
> This is what I did:
> - yum install gcc
> - yum install postgresql92-devel-9.2.5
> - extract unixODBC-2.3.2.tar.gz (in /root/test)
> - extract ODBC-Link-1.0.4.tar.gz (in /root/test)
> - cd /root/test/unixODBC-2.3.2
> - ./configure
> - make
> - make install
> - cd ..
> - cd ODBC-Link-1.0.4
> - export PATH=$PATH:/root/test/unixODBC-2.3.2/include:/usr/pgsql-9.2/bin
> - make USE_PGXS=1
>

I am not sure what is going on. I can compile here. In my case I am
using my distributions(OpenSUSE 12.2) package versions of unixODBC and
unixODBC-devel

You might try to do the same. Note it is important that they be the same
version for the same version of unixODBC. Then you could get rid of the
export path=..


--
Adrian Klaver
adrian.klaver@gmail.com

On 01/09/2014 02:31 AM, Bart Klein Ikink wrote:
> I have RHEL 6. I have now tried something different:
>
> - yum install gcc
> - yum install postgresql92-devel-9.2.5
> - yum install unixODBC-2.3.0
> - yum install unixODBC-devel
>
>
>
> Then:
>
> - cd ODBC-Link-1.0.4
> - export PATH=$PATH:/usr/pgsql-9.2/bin
> - make USE_PGXS=1
>
> But still having:
>
>
>
> gcc -O2 -g -pipe -Wall -Wp,-D_FORTIFY_SOURCE=2 -fexceptions -fstack-protector --param=ssp-buffer-size=4 -m64
-mtune=generic-I/usr/include/et -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Wendif-labels
-Wmissing-format-attribute-Wformat-security -fno-strict-aliasing -fwrapv -fpic -I. -I. -I/usr/pgsql-9.2/include/server
-I/usr/pgsql-9.2/include/internal-I/usr/include/et -D_GNU_SOURCE -I/usr/include/libxml2  -I/usr/include  -c -o
odbclink.oodbclink.c 
> odbclink.c: In function âget_dataâ:
> odbclink.c:746: error: âtimestamp_inâ undeclared (first use in this function)
> odbclink.c:746: error: (Each undeclared identifier is reported only once
> odbclink.c:746: error: for each function it appears in.)
> odbclink.c:749: error: âtimestamptz_inâ undeclared (first use in this function)
> make: *** [odbclink.o] Error 1
>
>

See if this works.

In the ODBC-Link-1.0.4 directory open odbclink.c
and at the top of the file under the other #includes add:

#include "utils/timestamp.h"

If that does not work then I would suggest contacting the authors of
ODBC-Link and see if they have any suggestions.

--
Adrian Klaver
adrian.klaver@gmail.com