postgresql访问oracle,PostgreSQL连接Oracle数据库

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, varchar, text

VARCHAR | char, varchar, text

VARCHAR2 | char, varchar, text

NVARCHAR2 | char, varchar, text

CLOB | char, varchar, text

LONG | char, varchar, text

RAW | uuid, bytea

BLOB | bytea

BFILE | bytea (read-only)

LONG RAW | bytea

NUMBER | numeric, float4, float8, char, varchar, text

NUMBER(n,m) with m<=0 | numeric, float4, float8, int2, int4, int8,

| boolean, char, varchar, text

FLOAT | numeric, float4, float8, char, varchar, text

BINARY_FLOAT | numeric, float4, float8, char, varchar, text

BINARY_DOUBLE | numeric, float4, float8, char, varchar, text

DATE | date, timestamp, timestamptz, char, varchar, text

TIMESTAMP | date, timestamp, timestamptz, char, varchar, text

TIMESTAMP WITH TIME ZOME | date, timestamp, timestamptz, char, varchar, text

TIMESTAMP WITH | date, timestamp, timestamptz, char, varchar, text

LOCAL TIME ZONE |

INTERVAL YEAR TO MONTH | interval, char, varchar, text

INTERVAL DAY TO SECOND | interval, char, varchar, 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,8),

antenna_height NUMERIC(18),

design_name VARCHAR(100),

design_longitude NUMERIC(18,8),

design_latitude NUMERIC(18,8),

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,2),

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,2),

need_station_deviation NUMERIC(18,2),

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