PostgreSQL连接Oracle数据库

最新推荐文章于 2024-05-09 10:53:40 发布

weixin_33756418

最新推荐文章于 2024-05-09 10:53:40 发布

阅读量1.8k

点赞数

文章标签：数据库 python

原文链接：https://my.oschina.net/fengyunfu/blog/805012

版权

2019独角兽企业重金招聘Python工程师标准>>>

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

转载于:https://my.oschina.net/fengyunfu/blog/805012