5.创建外部数据源服务
create server oradb foreign data wrapper oracle_fdw options(dbserver '192.168.51.92/ORCL');
6.建用户映射
grant usage on foreign server oradb to postgres;
create user mapping for postgres server oradb options(user 'MH',password 'MH');
6.ORACLE库创建个测试表
create table ORACLEDATA_TEST
(
ID NUMBER(10) not null,
XZQMC NVARCHAR2(50),
XZQDM NVARCHAR2(30)
)
insert into oracledata_test values(1,'南区','370202');
insert into oracledata_test values(2,'北区','370203');
7.PG库创建需要访问oracle的对应表
create foreign table ORACLEDATA_TEST_PG
(
ID numeric(10) not null,
XZQMC VARCHAR(50),
XZQDM VARCHAR(30)
) server oradb_110 options(schema 'MH',table 'ORACLEDATA_TEST');
8.在PG上面查询表
select * from ORACLEDATA_TEST_PG
查询表报错:
ERROR: error connecting to oracle: OCIEnvcreate failed to create environment handle
DETAIL: