PostgreSQL连接Oracle数据库

2016-12-14 09:49:12来源:oschina作者:清风_人点击

第七城市

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
(
idNUMERIC(18),
need_numer VARCHAR(100),
need_nameVARCHAR(100),
work_timeVARCHAR(100),
problem_NUMERIC VARCHAR(100),
cityVARCHAR(100),
countyVARCHAR(100),
cover_area VARCHAR(100),
cover_sceneVARCHAR(100),
need_fromVARCHAR(100),
precedence VARCHAR(100),
net_type VARCHAR(100),
build_type VARCHAR(100),
cm_station_typeVARCHAR(100),
bandVARCHAR(100),
need_longitude NUMERIC(18,8),
need_latitudeNUMERIC(18,8),
antenna_height NUMERIC(18),
design_nameVARCHAR(100),
design_longitudeNUMERIC(18,8),
design_latitudeNUMERIC(18,8),
design_antenna_height NUMERIC(18),
design_total_up_angle NUMERIC(18),
direction_angle1NUMERIC(18),
direction_angle2NUMERIC(18),
direction_angle3NUMERIC(18),
direction_angle4NUMERIC(18),
venderVARCHAR(100),
allot_station_NUMERICVARCHAR(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_timetimestamp(6),
plan_create_timetimestamp(6),
design_create_timetimestamp(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_result1VARCHAR(100),
approval_result2VARCHAR(100),
change_reasonVARCHAR(100),
change_num NUMERIC(18),
change_creator VARCHAR(100),
cell_num VARCHAR(100),
tac VARCHAR(100),
enodeb_station_deviation NUMERIC(18,2),
need_station_deviationNUMERIC(18,2),
honeycombo_typeVARCHAR(100),
is_zoom_outs VARCHAR(100),
design_antenna_heightsVARCHAR(100),
design_antenna_angles VARCHAR(100),
design_antenna_me_dip_angles VARCHAR(100),
design_antenna_el_dip_angles VARCHAR(100),
design_antenna_total_anglesVARCHAR(100),
irms_csp_timeVARCHAR(20),
irms_psp_timeVARCHAR(20),
irms_finish_timeVARCHAR(20),
irms_open_time VARCHAR(20),
complainin_num NUMERIC,
complainin_noVARCHAR(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
第七城市

最新文章

123

最新摄影

微信扫一扫

第七城市微信公众平台