1.下载最新源码:
@H_403_5@https://github.com/laurenz/oracle_fdw/releases
@H_403_5@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
@H_
404_22@
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