1.下载最新源码：

https://github.com/laurenz/oracle_fdw/releases

https://github.com/laurenz/oracle_fdw/archive/ORACLE_FDW_1_5_0.tar.gz

2.设置环境变量：

ORACLE_HOME=/opt/oracle/product/11.2.0/db
export ORACLE_HOME
PATH=$PATH:$ORACLE_HOME/bin
export PATH

PGHOME=/opt/disk/241/postgres
export PGHOME
PGDATA=$PGHOME/data
export PGDATA
PATH=$PATH:$HOME/.local/bin:$HOME/bin:$PGHOME/bin
export PATH

export LD_LIBRARY_PATH=/opt/disk/241/postgres/geos/lib:/opt/disk/241/postgres/proj4/lib:/opt/disk/241/postgres/gdal/lib::$PGHOME/lib:/lib64:/usr/lib64:/usr/local/lib64:/lib:/usr/lib:/usr/local/lib:$LD_LIBRARY_PATH
PATH=$PATH:$HOME/.local/bin:$HOME/bin:$PGHOME/bin
export PATH

3.编译：

make && make install

4.创建连接

CREATE EXTENSION oracle_fdw;
grant all on foreign data wrapper oracle_fdw to postgres;
CREATE SERVER oradb_1314 FOREIGN DATA WRAPPER oracle_fdw OPTIONS (dbserver '//192.168.1.13:1521/ora');
CREATE USER MAPPING FOR postgres SERVER oradb_1314 OPTIONS (user 'username1',password 'userpass1');

5.根据字段类型对应关系创建映射表

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

CREATE FOREIGN TABLE oradb_1314_CENTRAL_PLANNING_H_BASE
(
  id                           NUMERIC(18),need_numer                   VARCHAR(100),need_name                    VARCHAR(100),work_time                    VARCHAR(100),problem_NUMERIC               VARCHAR(100),city                         VARCHAR(100),county                       VARCHAR(100),cover_area                   VARCHAR(100),cover_scene                  VARCHAR(100),need_from                    VARCHAR(100),precedence                   VARCHAR(100),net_type                     VARCHAR(100),build_type                   VARCHAR(100),cm_station_type              VARCHAR(100),band                         VARCHAR(100),need_longitude               NUMERIC(18,8),need_latitude                NUMERIC(18,antenna_height               NUMERIC(18),design_name                  VARCHAR(100),design_longitude             NUMERIC(18,design_latitude              NUMERIC(18,design_antenna_height        NUMERIC(18),design_total_up_angle        NUMERIC(18),direction_angle1             NUMERIC(18),direction_angle2             NUMERIC(18),direction_angle3             NUMERIC(18),direction_angle4             NUMERIC(18),vender                       VARCHAR(100),allot_station_NUMERIC         VARCHAR(200),planning_station_deviation   NUMERIC(18,2),problem_NUMERIC_deviation     NUMERIC(18,remarks1                     VARCHAR(1000),remarks2                     VARCHAR(1000),remarks3                     VARCHAR(1000),state                        VARCHAR(100),stage                        VARCHAR(100),design_catalog_id            VARCHAR(100),need_creator                 VARCHAR(100),plan_creator                 VARCHAR(100),design_creator               VARCHAR(100),need_create_time             timestamp(6),plan_create_time             timestamp(6),design_create_time           timestamp(6),need_system_audit            VARCHAR(1000),plan_system_audit            VARCHAR(1000),design_system_audit          VARCHAR(1000),approval_opinion1            VARCHAR(1000),approval_opinion2            VARCHAR(1000),approval_result1             VARCHAR(100),approval_result2             VARCHAR(100),change_reason                VARCHAR(100),change_num                   NUMERIC(18),change_creator               VARCHAR(100),cell_num                     VARCHAR(100),tac                          VARCHAR(100),enodeb_station_deviation     NUMERIC(18,need_station_deviation       NUMERIC(18,honeycombo_type              VARCHAR(100),is_zoom_outs                 VARCHAR(100),design_antenna_heights       VARCHAR(100),design_antenna_angles        VARCHAR(100),design_antenna_me_dip_angles VARCHAR(100),design_antenna_el_dip_angles VARCHAR(100),design_antenna_total_angles  VARCHAR(100),irms_csp_time                VARCHAR(20),irms_psp_time                VARCHAR(20),irms_finish_time             VARCHAR(20),irms_open_time               VARCHAR(20),complainin_num               NUMERIC,complainin_no                VARCHAR(4000),is_tower                     VARCHAR(100)
) SERVER oradb_1314 OPTIONS (table 'CENTRAL_PLANNING_H_BASE'); 
select * from oradb_1314_CENTRAL_PLANNING_H_BASE limit 5
create table CENTRAL_PLANNING_H_BASE as
select t.*,st_geomfromtext('Point('||need_longitude||' '||need_latitude||')',4326) as geom from oradb_1314_CENTRAL_PLANNING_H_BASE t

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!