Windows系统oracle_fdw安装与查询sdo_geometry数据过程记录

oracle_fdw下载

oracle_fdw的github地址点击这里，相关介绍在此不再赘述。

选择所需版本的release

最新的2.6与2.7版本需要编译，而不需要编译的2.5版本及之前版本未成功实现extension故此介绍源码编译流程。

编译过程

前期准备：
在完成下载后需要准备Postgresql与Oracle环境，此处Postgresql选择使用16版本，下载地址；Oracle选择使用Instant Client，下载地址。

由于使用完整版Oracle客户端时在编译oracle_fdw时出现错误：
error C1083: 无法打开包括文 件: “oci.h”: No such file or directory
故此选用Instant Client版本，版本号为Version 19.24.0.0.0，此处我下载了Basic Package，SQL*Plus Package，SDK Package，ODBC Package，此处并未研究最低要求。

其中，环境变量需要设置Path与ORACLE_HOME，设置方法此处省略。

此外，还需要msbuild作为编译工具，此为visual studio工具，使用Native Tools Command Prompt for VS工具打开。

编译：
Windows版本编译方法原文见github的oracle_fdw项目主页，Building oracle_fdw on Windows一栏，在此仅放上编译命令：

cd \path\to\oracle_fdw\msvc
msbuild oracle_fdw.sln /p:Configuration=(Debug or Release) /p:Platform=(Win32 or x64) /p:OracleClient=(path to Oracle Client/SDK) /p:PostgreSQL=(path to PostgreSQL installation)

其中，\path\to\oracle_fdw为oracle_fdw对应地址，括号内部替换为环境对应项，如：

msbuild oracle_fdw.sln /p:Configuration=Debug /p:Platform=x64 /p:OracleClient=D:\oracle /p:PostgreSQL=D:\PGSQL

此处遇到的问题：
使用客户端版本的Oracle 11g作为OracleClient会出现错误：
error C1083: 无法打开包括文 件: “oci.h”: No such file or directory
后改用Oracle Instant Client，并改用对应的Oracle_Home与Path后该问题解决。

创建拓展

编译完成后，需要将编译好的oracle_fdw.dll放入PG目录下的\lib文件夹中，源码中原本存在的oracle_fdw.control与oracle_fdw–1.2.sql、oracle_fdw–1.1–1.2.sql、oracle_fdw–1.0–1.1.sql均放入PG目录下的\share\extension中。

完成上述过程后便可以启动数据库并在PG中添加拓展了，此处需要注意，若需要使用空间数据，需要在添加oracle_fdw之前添加postgis的拓展。

首先，确认oracle_fdw相关文件是否正确放置

select * from pg_available_extensions;

可以在列表中查找所需extension是否存在。
如果正确安装即可获得如下所示：

接下来就可以创建拓展了：

postgres=# create extension oracle_fdw;

Messages：CREATE EXTENSION

若创建成功可以通过以下命令确认：

postgres=# select oracle_diag();

此时，便可以进行对oracle的连接查询了。

此处遇到的问题：
在使用非源码编译版本的oracle_fdw时出现：
无法加载库 “/Postgresql/lib/oracle_fdw.dll”: The specified procedure could not be found
存在oracle path设置错误或存在多个oracle导致的可能，但由于我在设置对应path并屏蔽掉其他oracle后仍然无法解决，故改用使用源码编译获得的dll，该问题解决。

连接oracle

create server ora foreign data wrapper oracle_fdw options (dbserver ‘//localhost/ORCL’);

Messages: CREATE SERVER
此处ora为创建的server名称，dbserver为必须项，’ ’ 内为oracle对应连接串

grant usage on foreign server fdw_server to postgres;

Messages: GRANT
此处为postgres用户赋权

create user mapping for postgres server fdw_server options(user ‘username’,password ‘password’);

Messages: CREATE USER MAPPING
此处为创建用户mapping，需要对应oracle的用户与密码

create foreign table p_table(id integer, geo geometry)server fdw_server options(schema ‘ORA’, table ‘O_TABLE’);

Messages: CREATE FOREIGN TABLE
此处为创建对应的外部表，p_table为pg中使用的表名称，表结构需与oracle类型相对应，名称可不做对应，options中需要注意’ ’ 内部的大小写敏感。

insert into spatial values(1, st_geomfromtext(‘linestring(10 14,6 10)’));

Messages: INSERT 0 1
可进行数据插入，对于spatial数据插入数据格式为postgis的geometry格式，oracle_fdw将会将该数据进行转换。

select * from p_table;

此处查询的数据为Postgis对应的geometry类型数据，在oracle中查询结果如下：

SQL> select * from ora.sdo_test;
ID GEO(SDO_GTYPE, SDO_SRID, SDO_POINT(X, Y, Z), SDO_ELEM_INFO, SDO_ORDINATES)
1 SDO_GEOMETRY(2002, NULL, NULL, SDO_ELEM_INFO_ARRAY(1, 2, 1), SDO_ORDINATE_ARRAY(
10, 14, 6, 10))

此处遇到的问题：
ERROR: column “geo” (50679) of foreign table “p_table” cannot be converted to or from Oracle data type (20)
github中作者对此问题的说明是可能是由于未加载postgis拓展或拓展加载顺序错误导致，但此处我已经加载成功并且是先加载postgis后加载oracle_fdw，后经研究发现仅需要重启一个新的Query tool即可以解决该问题，这意味着需要重启会话才能够让oracle_fdw获取到geometry列的oid值并与oracle的sdo_geometry对应。
需要注意，在oracle端插入数据后记得需要commit，不然数据并未提交，pg端将无法查询展示。