oracle_utils.c:22:10: fatal error: oci.h: No such file or directory

[KristianStarr]

similar to previous raised issues but the steps in those did not resolve, not sure if the issue is the server pg install, my attempts to make or something else.

pbhadmin@PBH-PGSQL-TEST:/usr/lib/postgresql/16/lib/bitcode/oracle_fdw$ echo $PATH /usr/lib/postgresql/16/bin:/usr/local/bin:/usr/bin:/bin:/usr/local/games:/usr/games

pbhadmin@PBH-PGSQL-TEST:/usr/lib/postgresql/16/lib/bitcode/oracle_fdw$ echo $ORACLE_HOME /opt/oracle

[KristianStarr]

UPDATE

I installed the postgres developer tools and got a bit further, as it smelt of being that was the issue.

now I have a similar error complaining about a missing oracle library which I think is to do with a misconfigured oracle instant client install

pbhadmin@PBH-PGSQL-TEST:/usr/lib/postgresql/16/lib/bitcode/oracle_fdw$ sudo make install
gcc -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Werror=vla -Wendif-labels -Wmissing-format-attribute -Wimplicit-fallthrough=3 -Wcast-function-type -Wshadow=compatible-local -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -Wno-format-truncation -Wno-stringop-truncation -g -g -O2 -fstack-protector-strong -Wformat -Werror=format-security -fno-omit-frame-pointer -fPIC -fvisibility=hidden -I"/sdk/include" -I"/oci/include" -I"/rdbms/public" -I"/"  -I. -I./ -I/usr/include/postgresql/16/server -I/usr/include/postgresql/internal  -Wdate-time -D_FORTIFY_SOURCE=2 -D_GNU_SOURCE -I/usr/include/libxml2   -c -o oracle_fdw.o oracle_fdw.c
gcc -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Werror=vla -Wendif-labels -Wmissing-format-attribute -Wimplicit-fallthrough=3 -Wcast-function-type -Wshadow=compatible-local -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -Wno-format-truncation -Wno-stringop-truncation -g -g -O2 -fstack-protector-strong -Wformat -Werror=format-security -fno-omit-frame-pointer -fPIC -fvisibility=hidden -I"/sdk/include" -I"/oci/include" -I"/rdbms/public" -I"/"  -I. -I./ -I/usr/include/postgresql/16/server -I/usr/include/postgresql/internal  -Wdate-time -D_FORTIFY_SOURCE=2 -D_GNU_SOURCE -I/usr/include/libxml2   -c -o oracle_utils.o oracle_utils.c
oracle_utils.c:22:10: fatal error: oci.h: No such file or directory
   22 | #include <oci.h>
      |          ^~~~~~~

[KristianStarr]

UPDATE

completely removed instantclient_23_4 and install instantclient_19_23.

exported variables seem correct. can't find oci.h

pbhadmin@PBH-PGSQL-TEST:/usr/lib/postgresql/16/lib/bitcode/oracle_fdw$ sudo find / -name oci.h -print

pbhadmin@PBH-PGSQL-TEST:/usr/lib/postgresql/16/lib/bitcode/oracle_fdw$ cat /etc/ld.so.conf.d/oracle-instantclient.conf
`/opt/oracle/instantclient_19_23`

pbhadmin@PBH-PGSQL-TEST:/usr/lib/postgresql/16/lib/bitcode/oracle_fdw$ echo $PATH
`/usr/lib/postgresql/16/bin:/opt/oracle/instantclient_19_23:/usr/local/bin:/usr/bin:/bin:/usr/local/games:/usr/games`

pbhadmin@PBH-PGSQL-TEST:/usr/lib/postgresql/16/lib/bitcode/oracle_fdw$ echo $ORACLE_HOME
`/opt/oracle`

same error encountered

oracle_utils.c:22:10: fatal error: oci.h: No such file or directory
   22 | #include <oci.h>
      |          ^~~~~~~

[KristianStarr]

UPDATE

I was not using the SDK version of the oracle instant client. replaced basic version with SDK version. oci.h now exists

pbhadmin@PBH-PGSQL-TEST:/usr/lib/postgresql/16/lib/bitcode/oracle_fdw$ sudo find / -name oci.h -print /opt/oracle/instantclient_19_23/sdk/include/oci.h

however the compilation error has not changed. exported path looks ok

[mkgrgis]

@KristianStarr , what about https://github.com/laurenz/oracle_fdw/wiki/Help-for-installing-and-configuring-Oracle ?

[KristianStarr]

That looks very old, I was following the instructions here https://www.oracle.com/uk/database/technologies/instant-client/linux-x86-64-downloads.html#ic_x64_inst

Linux PBH-PGSQL-TEST 6.1.0-18-amd64 #1 SMP PREEMPT_DYNAMIC Debian 6.1.76-1 (2024-02-01) x86_64 GNU/Linux

nothing in that link seems applicable to version 19_23.

How I installed (following instructions on the link I posted)

pbhadmin@PBH-PGSQL-TEST:/opt/oracle$ cd /opt/oracle unzip instantclient-basic-linux.x64-19.23.0.0.0dbru.zip # basic package unzip instantclient-sdk-linux.x64-19.23.0.0.0dbru.zip # SDK package, unzips into same dir over the top of the basic

sudo find / -name oci.h -print /opt/oracle/instantclient_19_23/sdk/include/oci.h

cat /etc/ld.so.conf.d/oracle-instantclient.conf /opt/oracle/instantclient_19_23

sudo apt install libaio1 [sudo] password for pbhadmin: Reading package lists... Done Building dependency tree... Done Reading state information... Done libaio1 is already the newest version (0.3.113-4). 0 upgraded, 0 newly installed, 0 to remove and 53 not upgraded.

sudo ldconfig

echo $PATH /usr/lib/postgresql/16/bin:/opt/oracle/instantclient_19_23:/opt/oracle/instantclient_19_23/sdk:/opt/oracle/instantclient_19_23/sdk/include:/usr/local/bin:/usr/bin:/bin:/usr/local/games:/usr/games

echo $ORACLE_HOME /opt/oracle

interestingly, if I make symbolic links to the oracle_fdw directory the make error moves onto the next missing oracle library file, as if it cannot see them via the path. They are owned by the same user, have rwx rights and the $PATH variable seems correct.

e.g. pbhadmin@PBH-PGSQL-TEST:/usr/lib/postgresql/16/lib/bitcode/oracle_fdw$ sudo ln -s /opt/oracle/instantclient_19_23/sdk/include/oci.h .

pbhadmin@PBH-PGSQL-TEST:/usr/lib/postgresql/16/lib/bitcode/oracle_fdw$ make gcc -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Werror=vla -Wendif-labels -Wmissing-format-attribute -Wimplicit-fallthrough=3 -Wcast-function-type -Wshadow=compatible-local -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -Wno-format-truncation -Wno-stringop-truncation -g -g -O2 -fstack-protector-strong -Wformat -Werror=format-security -fno-omit-frame-pointer -fPIC -fvisibility=hidden -I"/opt/oracle/sdk/include" -I"/opt/oracle/oci/include" -I"/opt/oracle/rdbms/public" -I"/opt/oracle/" -I. -I./ -I/usr/include/postgresql/16/server -I/usr/include/postgresql/internal -Wdate-time -D_FORTIFY_SOURCE=2 -D_GNU_SOURCE -I/usr/include/libxml2 -c -o oracle_utils.o oracle_utils.c In file included from oracle_utils.c:22: ./oci.h:770:10: fatal error: ocidfn.h: No such file or directory 770 | #include | ^~~~~~

[KristianStarr]

UPDATE I think i've solved it I moved all the Oracle client objects from /opt/oracle/instantclient_19_23 to /opt/oracle , as the make file only looked in /opt/oracle for directories such as the sdk etc.

latest make output:

pbhadmin@PBH-PGSQL-TEST:/usr/lib/postgresql/16/lib/bitcode/oracle_fdw$ echo $PATH
/opt/oracle:/usr/lib/postgresql/16/bin:/usr/local/bin:/usr/bin:/bin:/usr/local/games:/usr/games
pbhadmin@PBH-PGSQL-TEST:/usr/lib/postgresql/16/lib/bitcode/oracle_fdw$ make
gcc -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Werror=vla -Wendif-labels -Wmissing-format-attribute -Wimplicit-fallthrough=3 -Wcast-function-type -Wshadow=compatible-local -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -Wno-format-truncation -Wno-stringop-truncation -g -g -O2 -fstack-protector-strong -Wformat -Werror=format-security -fno-omit-frame-pointer -fPIC -fvisibility=hidden -I"/opt/oracle/sdk/include" -I"/opt/oracle/oci/include" -I"/opt/oracle/rdbms/public" -I"/opt/oracle/"  -I. -I./ -I/usr/include/postgresql/16/server -I/usr/include/postgresql/internal  -Wdate-time -D_FORTIFY_SOURCE=2 -D_GNU_SOURCE -I/usr/include/libxml2   -c -o oracle_utils.o oracle_utils.c
gcc -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Werror=vla -Wendif-labels -Wmissing-format-attribute -Wimplicit-fallthrough=3 -Wcast-function-type -Wshadow=compatible-local -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -Wno-format-truncation -Wno-stringop-truncation -g -g -O2 -fstack-protector-strong -Wformat -Werror=format-security -fno-omit-frame-pointer -fPIC -fvisibility=hidden -I"/opt/oracle/sdk/include" -I"/opt/oracle/oci/include" -I"/opt/oracle/rdbms/public" -I"/opt/oracle/"  -I. -I./ -I/usr/include/postgresql/16/server -I/usr/include/postgresql/internal  -Wdate-time -D_FORTIFY_SOURCE=2 -D_GNU_SOURCE -I/usr/include/libxml2   -c -o oracle_gis.o oracle_gis.c
gcc -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Werror=vla -Wendif-labels -Wmissing-format-attribute -Wimplicit-fallthrough=3 -Wcast-function-type -Wshadow=compatible-local -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -Wno-format-truncation -Wno-stringop-truncation -g -g -O2 -fstack-protector-strong -Wformat -Werror=format-security -fno-omit-frame-pointer -fPIC -fvisibility=hidden -shared -o oracle_fdw.so oracle_fdw.o oracle_utils.o oracle_gis.o -L/usr/lib/x86_64-linux-gnu  -Wl,-z,relro -Wl,-z,now -L/usr/lib/llvm-16/lib  -Wl,--as-needed -fvisibility=hidden -L"/opt/oracle/" -L"/opt/oracle/bin" -L"/opt/oracle/lib" -L"/opt/oracle/lib/amd64"  -lclntsh 
pbhadmin@PBH-PGSQL-TEST:/usr/lib/postgresql/16/lib/bitcode/oracle_fdw$ make install
/bin/mkdir -p '/usr/lib/postgresql/16/lib'
/bin/mkdir -p '/usr/share/postgresql/16/extension'
/bin/mkdir -p '/usr/share/postgresql/16/extension'
/bin/mkdir -p '/usr/share/doc/postgresql-doc-16/extension'
/bin/mkdir: cannot create directory ‘/usr/share/doc/postgresql-doc-16’: Permission denied
make: *** [/usr/lib/postgresql/16/lib/pgxs/src/makefiles/pgxs.mk:283: installdirs] Error 1
pbhadmin@PBH-PGSQL-TEST:/usr/lib/postgresql/16/lib/bitcode/oracle_fdw$ sudo make install
/bin/mkdir -p '/usr/lib/postgresql/16/lib'
/bin/mkdir -p '/usr/share/postgresql/16/extension'
/bin/mkdir -p '/usr/share/postgresql/16/extension'
/bin/mkdir -p '/usr/share/doc/postgresql-doc-16/extension'
/usr/bin/install -c -m 755  oracle_fdw.so '/usr/lib/postgresql/16/lib/oracle_fdw.so'
/usr/bin/install -c -m 644 .//oracle_fdw.control '/usr/share/postgresql/16/extension/'
/usr/bin/install -c -m 644 .//oracle_fdw--1.2.sql .//oracle_fdw--1.0--1.1.sql .//oracle_fdw--1.1--1.2.sql  '/usr/share/postgresql/16/extension/'
/usr/bin/install -c -m 644 .//README.oracle_fdw '/usr/share/doc/postgresql-doc-16/extension/'
pbhadmin@PBH-PGSQL-TEST:/usr/lib/postgresql/16/lib/bitcode/oracle_fdw$ 

so looks like success

[KristianStarr]

UPDATE not quite there, getting an error in postgres now

select * from mytable

ERROR:  foreign-data wrapper "oracle_fdw" has no handler 

SQL state: 55000

RAGTest-# \dew
                      List of foreign-data wrappers
     Name     |  Owner   |       Handler        |       Validator        
--------------+----------+----------------------+------------------------
 oracle_fdw   | postgres | -                    | -
 postgres_fdw | postgres | postgres_fdw_handler | postgres_fdw_validator
(2 rows)

RAGTest-# 

[KristianStarr]

UPDATE

I have confirmed that the Oracle instant Client installed on the postgres server connects to the target Oracle database.

The error is somewhere in the oracle_fdw install and setup. In case of a mis-install, I have deleted the oracle_fdw install and re-downloaded and placed in what I think is the correct place

pbhadmin@PBH-PGSQL-TEST:/usr/lib/postgresql/16/lib/bitcode/oracle_fdw$ ls -ltr
total 1224
-rwxr-xr-x 1 root root    948 Aug 22 09:15 TODO
-rwxr-xr-x 1 root root  43930 Aug 22 09:15 README.oracle_fdw
lrwxrwxrwx 1 root root     17 Aug 22 09:15 README.md -> README.oracle_fdw
-rwxr-xr-x 1 root root   1244 Aug 22 09:15 oracle_fdw--1.2.sql
-rwxr-xr-x 1 root root    240 Aug 22 09:15 oracle_fdw--1.1--1.2.sql
-rwxr-xr-x 1 root root    231 Aug 22 09:15 oracle_fdw--1.0--1.1.sql
drwxr-xr-x 2 root root   4096 Aug 22 09:15 msvc
-rwxr-xr-x 1 root root   1475 Aug 22 09:15 Makefile
-rwxr-xr-x 1 root root   1059 Aug 22 09:15 LICENSE
drwxr-xr-x 2 root root   4096 Aug 22 09:15 expected
-rwxr-xr-x 1 root root  27499 Aug 22 09:15 CHANGELOG
drwxr-xr-x 2 root root   4096 Aug 22 09:15 sql
-rwxr-xr-x 1 root root 104553 Aug 22 09:15 oracle_utils.c
-rwxr-xr-x 1 root root  44511 Aug 22 09:15 oracle_gis.c
-rwxr-xr-x 1 root root   9151 Aug 22 09:15 oracle_fdw.h
-rwxr-xr-x 1 root root    133 Aug 22 09:15 oracle_fdw.control
-rwxr-xr-x 1 root root 227222 Aug 22 09:15 oracle_fdw.c
-rwxr-xr-x 1 root root 744888 Aug 22 09:15 oracle_fdw.o
pbhadmin@PBH-PGSQL-TEST:/usr/lib/postgresql/16/lib/bitcode/oracle_fdw$ 

I am back to the issue of the make not being able to see the oracle library files e.g.

pbhadmin@PBH-PGSQL-TEST:/usr/lib/postgresql/16/lib/bitcode/oracle_fdw$ sudo make
[sudo] password for pbhadmin: 
gcc -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Werror=vla -Wendif-labels -Wmissing-format-attribute -Wimplicit-fallthrough=3 -Wcast-function-type -Wshadow=compatible-local -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -Wno-format-truncation -Wno-stringop-truncation -g -g -O2 -fstack-protector-strong -Wformat -Werror=format-security -fno-omit-frame-pointer -fPIC -fvisibility=hidden -I"/sdk/include" -I"/oci/include" -I"/rdbms/public" -I"/"  -I. -I./ -I/usr/include/postgresql/16/server -I/usr/include/postgresql/internal  -Wdate-time -D_FORTIFY_SOURCE=2 -D_GNU_SOURCE -I/usr/include/libxml2   -c -o oracle_utils.o oracle_utils.c
oracle_utils.c:22:10: fatal error: oci.h: No such file or directory
   22 | #include <oci.h>
      |          ^~~~~~~
compilation terminated.
make: *** [<builtin>: oracle_utils.o] Error 1

PATH and /opt/oracle directory both look correct e.g.

/usr/local/bin:/usr/bin:/bin:/usr/local/games:/usr/games:/opt/oracle:/opt/oracle/sdk:/opt/oracle/sdk/include:/usr/lib/postgresql/16
pbhadmin@PBH-PGSQL-TEST:/usr/lib/postgresql/16/lib/bitcode/oracle_fdw$ ls -ltr /opt/oracle
total 240144
-rw-r--r-- 1 pbhadmin pbhadmin     31825 Apr  6 02:37 xstreams.jar
-rwxr-xr-x 1 pbhadmin pbhadmin    237848 Apr  6 02:37 uidrvci
-rw-r--r-- 1 pbhadmin pbhadmin   1698368 Apr  6 02:37 ucp.jar
-rw-r--r-- 1 pbhadmin pbhadmin   4532370 Apr  6 02:37 ojdbc8.jar
drwxr-xr-x 3 pbhadmin pbhadmin      4096 Apr  6 02:37 network
-r-xr-xr-x 1 pbhadmin pbhadmin     43272 Apr  6 02:37 libtfojdbc1.so
-rwxr-xr-x 1 pbhadmin pbhadmin    116376 Apr  6 02:37 liboramysql19.so
-r-xr-xr-x 1 pbhadmin pbhadmin    153648 Apr  6 02:37 libocijdbc19.so
-rwxr-xr-x 1 pbhadmin pbhadmin 130568656 Apr  6 02:37 libociei.so
-rwxr-xr-x 1 pbhadmin pbhadmin   2342024 Apr  6 02:37 libocci.so.19.1
-rwxr-xr-x 1 pbhadmin pbhadmin   5832424 Apr  6 02:37 libnnz19.so
-r-xr-xr-x 1 pbhadmin pbhadmin    478728 Apr  6 02:37 libmql1.so
-r-xr-xr-x 1 pbhadmin pbhadmin   3719008 Apr  6 02:37 libipc1.so
-rwxr-xr-x 1 pbhadmin pbhadmin  84632216 Apr  6 02:37 libclntsh.so.19.1
-rwxr-xr-x 1 pbhadmin pbhadmin   8060888 Apr  6 02:37 libclntshcore.so.19.1
-rwxr-xr-x 1 pbhadmin pbhadmin     59360 Apr  6 02:37 genezi
-rw-r--r-- 1 pbhadmin pbhadmin      1633 Apr  6 02:37 BASIC_README
-r-xr-xr-x 1 pbhadmin pbhadmin      5780 Apr  6 02:37 BASIC_LICENSE
-rwxr-xr-x 1 pbhadmin pbhadmin     42000 Apr  6 02:37 adrci
-rw-r--r-- 1 pbhadmin pbhadmin      1641 Apr  6 02:37 SQLPLUS_README
-r-xr-xr-x 1 pbhadmin pbhadmin      5780 Apr  6 02:37 SQLPLUS_LICENSE
-rwxr-xr-x 1 pbhadmin pbhadmin     24808 Apr  6 02:37 sqlplus
-rwxr-xr-x 1 pbhadmin pbhadmin   1577472 Apr  6 02:37 libsqlplus.so
-rwxr-xr-x 1 pbhadmin pbhadmin   1660784 Apr  6 02:37 libsqlplusic.so
-rw-r--r-- 1 pbhadmin pbhadmin       342 Apr  6 02:37 glogin.sql
-rw-rw-r-- 1 pbhadmin pbhadmin      1627 Apr  6 02:37 SDK_README
-r-xr-xr-x 1 pbhadmin pbhadmin      5780 Apr  6 02:37 SDK_LICENSE
drwxr-xr-x 5 pbhadmin pbhadmin      4096 Apr  6 02:37 sdk
lrwxrwxrwx 1 pbhadmin pbhadmin        15 Aug 21 16:25 libocci.so.18.1 -> libocci.so.19.1
lrwxrwxrwx 1 pbhadmin pbhadmin        15 Aug 21 16:25 libocci.so.12.1 -> libocci.so.19.1
lrwxrwxrwx 1 pbhadmin pbhadmin        15 Aug 21 16:25 libocci.so.11.1 -> libocci.so.19.1
lrwxrwxrwx 1 pbhadmin pbhadmin        15 Aug 21 16:25 libocci.so.10.1 -> libocci.so.19.1
lrwxrwxrwx 1 pbhadmin pbhadmin        15 Aug 21 16:25 libocci.so -> libocci.so.19.1
lrwxrwxrwx 1 pbhadmin pbhadmin        17 Aug 21 16:25 libclntsh.so.18.1 -> libclntsh.so.19.1
lrwxrwxrwx 1 pbhadmin pbhadmin        17 Aug 21 16:25 libclntsh.so.12.1 -> libclntsh.so.19.1
lrwxrwxrwx 1 pbhadmin pbhadmin        17 Aug 21 16:25 libclntsh.so.11.1 -> libclntsh.so.19.1
lrwxrwxrwx 1 pbhadmin pbhadmin        17 Aug 21 16:25 libclntsh.so.10.1 -> libclntsh.so.19.1
lrwxrwxrwx 1 pbhadmin pbhadmin        17 Aug 21 16:25 libclntsh.so -> libclntsh.so.19.1

am out of ideas

[KristianStarr]

went back to just copying the entire contents of /opt/oracle/sdk/include to /opt/oracle

sudo make now seems to work :

dmin@PBH-PGSQL-TEST:/usr/lib/postgresql/16/lib/bitcode/oracle_fdw$ sudo make
gcc -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Werror=vla -Wendif-labels -Wmissing-format-attribute -Wimplicit-fallthrough=3 -Wcast-function-type -Wshadow=compatible-local -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -Wno-format-truncation -Wno-stringop-truncation -g -g -O2 -fstack-protector-strong -Wformat -Werror=format-security -fno-omit-frame-pointer -fPIC -fvisibility=hidden -I"/sdk/include" -I"/oci/include" -I"/rdbms/public" -I"/" -I/opt/oracle -I. -I./ -I/usr/include/postgresql/16/server -I/usr/include/postgresql/internal  -Wdate-time -D_FORTIFY_SOURCE=2 -D_GNU_SOURCE -I/usr/include/libxml2   -c -o oracle_utils.o oracle_utils.c
gcc -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Werror=vla -Wendif-labels -Wmissing-format-attribute -Wimplicit-fallthrough=3 -Wcast-function-type -Wshadow=compatible-local -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -Wno-format-truncation -Wno-stringop-truncation -g -g -O2 -fstack-protector-strong -Wformat -Werror=format-security -fno-omit-frame-pointer -fPIC -fvisibility=hidden -I"/sdk/include" -I"/oci/include" -I"/rdbms/public" -I"/" -I/opt/oracle -I. -I./ -I/usr/include/postgresql/16/server -I/usr/include/postgresql/internal  -Wdate-time -D_FORTIFY_SOURCE=2 -D_GNU_SOURCE -I/usr/include/libxml2   -c -o oracle_gis.o oracle_gis.c
gcc -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Werror=vla -Wendif-labels -Wmissing-format-attribute -Wimplicit-fallthrough=3 -Wcast-function-type -Wshadow=compatible-local -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -Wno-format-truncation -Wno-stringop-truncation -g -g -O2 -fstack-protector-strong -Wformat -Werror=format-security -fno-omit-frame-pointer -fPIC -fvisibility=hidden -shared -o oracle_fdw.so oracle_fdw.o oracle_utils.o oracle_gis.o -L/usr/lib/x86_64-linux-gnu  -Wl,-z,relro -Wl,-z,now -L/usr/lib/llvm-16/lib  -Wl,--as-needed -fvisibility=hidden -L"/" -L"/bin" -L"/lib" -L"/lib/amd64" -L/opt/oracle -L/opt/oracle/sdk/include -lclntsh 

then re-checked Oracle connection:

SQL*Plus: Release 19.0.0.0.0 - Production on Thu Aug 22 12:19:38 2024
Version 19.23.0.0.0

Copyright (c) 1982, 2023, Oracle.  All rights reserved.

Last Successful login time: Thu Aug 22 2024 10:58:58 +01:00

Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0

SQL> exit

ran make install

pbhadmin@PBH-PGSQL-TEST:/usr/lib/postgresql/16/lib/bitcode/oracle_fdw$ sudo make install
/bin/mkdir -p '/usr/lib/postgresql/16/lib'
/bin/mkdir -p '/usr/share/postgresql/16/extension'
/bin/mkdir -p '/usr/share/postgresql/16/extension'
/bin/mkdir -p '/usr/share/doc/postgresql-doc-16/extension'
/usr/bin/install -c -m 755  oracle_fdw.so '/usr/lib/postgresql/16/lib/oracle_fdw.so'
/usr/bin/install -c -m 644 .//oracle_fdw.control '/usr/share/postgresql/16/extension/'
/usr/bin/install -c -m 644 .//oracle_fdw--1.2.sql .//oracle_fdw--1.0--1.1.sql .//oracle_fdw--1.1--1.2.sql  '/usr/share/postgresql/16/extension/'
/usr/bin/install -c -m 644 .//README.oracle_fdw '/usr/share/doc/postgresql-doc-16/extension/'

seems ok, now to try and add the fdw to postgres again

[KristianStarr]

Postgres side extension creation and user mapping + table creation all fine now. confirmed working.

The issue was that the oracle_fdw makefile for some reason would not look in the sdk/include directory for the relevant libraries. fixed by copying all those library files to /opt/oracle (the $ORACLE_HOME)

[laurenz]

Sorry, I have been on vacation.

You should have set ORACLE_HOME to /opt/oracle/instantclient_19_23, since the Mahefile contains:

# add include and library paths for both Instant Client and regular Client
PG_CPPFLAGS = -I"$(ORACLE_HOME)/sdk/include" -I"$(ORACLE_HOME)/oci/include" -I"$(ORACLE_HOME)/rdbms/public" -I"$(ORACLE_HOME)/" $(FIND_CPPFLAGS)

[KristianStarr]

it was originally, it made no difference so I changed it to .opt/oracle in case the makefile was using hard coding. all PATHs seemed to be ignored by the make command which must be an issue on the server/user that I didn't get to the bottom of.

[laurenz]

Looking again, the sudo must be the problem. It doesn't pass through environment variables for security reasons.

You could

sudo env ORACLE_HOME=opt/oracle/instantclient_19_2 make

but the proper solution is not to use sudo when you build oracle_fdw; only when you make install.