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

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏杨建荣的学习笔记

MySQL和Oracle对比学习之数据字典元数据(r4笔记第33天)

MySQL和Oracle虽然在架构上有很大的不同,但是如果从某些方面比较起来,它们有些方面也是相通的。 毕竟学习的主线是MySQL,所以会从MySQL的角度来对...

30460
来自专栏Java学习123

ORACLE数据库日常维护

60280
来自专栏程序员宝库

Mysql - JOIN 详解

一个完整的SQL语句中会被拆分成多个子句,子句的执行过程中会产生虚拟表(vt),但是结果只返回最后一张虚拟表。从这个思路出发,我们试着理解一下JOIN查询的执行...

42330
来自专栏乐沙弥的世界

使用exchange方式切换普通表到分区表

      随着数据库数据量的不断增长,有些表需要由普通的堆表转换为分区表的模式。有几种不同的方法来对此进行操作,诸如导出表数据,然后创建分区表再导入数据到分区...

7510
来自专栏数据库新发现

如何获得跟踪文件名称

http://www.eygle.com/faq/How.To.Get.Tracefile.Name.htm

13020
来自专栏用户画像

新闻发布 sql server代码

8540
来自专栏杨建荣的学习笔记

关于物化视图疑问(32天)

--初始化操作, 创建两个用户一个,testo,一个test. 在testo上创建表,test上创建物化视图。 SQL> create user testo ...

36650
来自专栏数据库新发现

Oracle9i新特性-使用DBMS_METADATA包获得对象DDL语句

从Oracle9i开始Oracle提供了一个新的系统包DBMS_METADATA,可以用于提取对象创建的DDL语句。

14720
来自专栏Aloys的开发之路

DB2常用语句

CREATE TABLE STAFF_BAK LIKE STAFF; INSERT INTO STAFF_BAK SELECT * FROM STAFF; S...

20050
来自专栏沃趣科技

SQL优化案例-分区索引之无前缀索引(六)

无前缀索引:分区索引不包含分区字段就叫无前缀索引,那么什么时候用无前缀索引和前缀索引呢?

13720

扫码关注云+社区

领取腾讯云代金券