oracle_fdw安装及使用（无法访问oracle存储过程等对象）

通过oracle_fdw可以访问oracle中的一些表和视图，也可以进行修改，尤其是给比较复杂的系统使用非常方便。

 

（但不能使用oracle_fdw来访问oracle的存储过程、包、函数、序列等对象） 

 

1.安装oracle_fdw:

 

1）编译安装oracle_fdw之前，需要安装Oracle的客户端程序;下载地址：http://www.oracle.com/technetwork/database/database-technologies/instant-client/overview/index.html

 

主要是sdk和basic：

instantclient-basic-linux.x64-18.5.0.0.0dbru.zip

instantclient-sdk-linux.x64-18.5.0.0.0dbru.zip

 

2）下载地址：http://pgxn.org/dist/oracle_fdw/

unzip oracle_fdw-2.1.0.zip

cd oracle_fdw-2.1.0

 

3）配置环境变量：

[root@fce40690-0e46-4603-e80e-ca351bda31ec ~]# cat ora-env.sh

export ORACLE_HOME=/opt/oracle/instantclient

export OCI_LIB_DIR=$ORACLE_HOME

export OCI_INC_DIR=$ORACLE_HOME/sdk/include

 

[root@fce40690-0e46-4603-e80e-ca351bda31ec ~]# cat pg-env.sh

[ -f /etc/profile ] && source /etc/profile

PGDATA=/var/lib/pgsql/11/data

export PGDATA

# If you want to customize your settings,

# Use the file below. This is not overridden

# by the RPMS.

[ -f /var/lib/pgsql/.pgsql_profile ] && source /var/lib/pgsql/.pgsql_profile

 

export PGHOME=/usr/pgsql-11/

export LD_LIBRARY_PATH=$PGHOME/lib:$LD_LIBRARY_PATH:/opt/oracle/instantclient/

export PATH=$PATH:$PGHOME/bin

 

4）编译安装：

make && make install

 

5）切换到postgres创建扩展

postgres=# create extension oracle_fdw;

如果出现找不到libclntsh.so.12.1等库，则考虑将缺少的库从/opt/oracle/instantclient/中拷贝到postgres安装目录的lib下面，有些情况下，指定了LD_LIBRARY_PATH目录都会提示找不到。

postgres=# \dx
List of installed extensions
Name | Version | Schema | Description
------------+---------+------------+----------------------------------------
oracle_fdw | 1.1 | public | foreign data wrapper for Oracle access
plpgsql | 1.0 | pg_catalog | PL/pgSQL procedural language
(2 rows)

 

2.配置oracle数据库tnsnames.ora

可以将Oracle那边的对应文件拷贝过来即可，注意里面的IP地址：

[postgres@fce40690-0e46-4603-e80e-ca351bda31ec ~]$ cat /opt/oracle/instantclient/network/admin/tnsnames.ora

# tnsnames.ora Network Configuration File: /ora/oracle/product/11.2.0/dbhome_1/network/admin/tnsnames.ora

# Generated by Oracle configuration tools.

 

ORCL =

  (DESCRIPTION =

    (ADDRESS = (PROTOCOL = TCP)(HOST = 10.9.10.236)(PORT = 1521))

    (CONNECT_DATA =

      (SERVER = DEDICATED)

      (SERVICE_NAME = orcl)

    )

  )

 

3.创建外部表

 

1）创建server：

create server oracle_test foreign data wrapper oracle_fdw options(dbserver 'ORCL');

 

 

2）创建mapping：

create user mapping for postgres server oracle_test options (user 'oracle', password 'oracle');

 

3）创建外部表：

CREATE FOREIGN TABLE test(

id int

) SERVER oracle_test options (schema 'ORACLE', table 'TEST');

 

 

4）检查：

postgres=# select oracle_diag();

                         oracle_diag

-------------------------------------------------------------

 oracle_fdw 2.1.0, PostgreSQL 11.2, Oracle client 18.5.0.0.0

(1 row)

 

5）查询：

postgres=# select * from test;

 id

----

  1

(1 row)

 

6）查看外部表相关对象：

postgres=# \des
            List of foreign servers
    Name     |  Owner   | Foreign-data wrapper
-------------+----------+----------------------
 oracle_test | postgres | oracle_fdw
(1 row)

postgres=# \deu
  List of user mappings
   Server    | User name
-------------+-----------
 oracle_test | postgres
(1 row)

postgres=# \det
       List of foreign tables
 Schema |    Table    |   Server
--------+-------------+-------------
 public | test        | oracle_test
 public | test_no_fdw | oracle_test
 public | test_seq    | oracle_test
(3 rows)

 

 

4.在查询时如果提示错误：ORA-12154

postgres=# select * from test;

ERROR:  connection for foreign table "test" cannot be established

DETAIL:  ORA-12154: TNS:could not resolve the connect identifier specified

 

1）确认环境变量是否在postgres用户下设置

2）确认oracle的instantclient是否有postgres的访问权限

 

5.外部表的一些配置选项：

Foreign table options

table (required)

The Oracle table name. This name must be written exactly as it occurs in Oracle's system catalog, so normally consist of uppercase letters only.

To define a foreign table based on an arbitrary Oracle query, set this option to the query enclosed in parentheses, e.g.

OPTIONS (table '(SELECT col FROM tab WHERE val = ''string'')')
Do not set the schema option in this case.
INSERT, UPDATE and DELETE will work on foreign tables defined on simple queries; if you want to avoid that (or confusing Oracle error messages for more complicated queries), use the table option readonly.

schema (optional)

The table's schema (or owner). Useful to access tables that do not belong to the connecting Oracle user. This name must be written exactly as it occurs in Oracle's system catalog, so normally consist of uppercase letters only.

max_long (optional, defaults to "32767")

The maximal length of any LONG or LONG RAW columns in the Oracle table. Possible values are integers between 1 and 1073741823 (the maximal size of a bytea in PostgreSQL). This amount of memory will be allocated at least twice, so large values will consume a lot of memory.
If max_long is less than the length of the longest value retrieved, you will receive the error message ORA-01406: fetched column value was truncated.

readonly (optional, defaults to "false")

INSERT, UPDATE and DELETE is only allowed on tables where this option is not set to yes/on/true. Since these statements can only be executed from PostgreSQL 9.3 on, setting this option has no effect on earlier versions. It might still be a good idea to set it in PostgreSQL 9.2 and earlier on tables that you do not wish to be changed, to be prepared for an upgrade to PostgreSQL 9.3 or later.

sample_percent (optional, defaults to "100")

This option only influences ANALYZE processing and can be useful to ANALYZE very large tables in a reasonable time.

The value must be between 0.000001 and 100 and defines the percentage of Oracle table blocks that will be randomly selected to calculate PostgreSQL table statistics. This is accomplished using the SAMPLE BLOCK (x) clause in Oracle.

ANALYZE will fail with ORA-00933 for tables defined with Oracle queries and may fail with ORA-01446 for tables defined with complex Oracle views.

prefetch (optional, defaults to "200")

Sets the number of rows that will be fetched with a single round-trip between PostgreSQL and Oracle during a foreign table scan. This is implemented using Oracle row prefetching. The value must be between 0 and 10240, where a value of zero disables prefetching.

Higher values can speed up performance, but will use more memory on the PostgreSQL server.

 

但是在实际操作过程中，往往会遇到oracle中通dblink访问其他数据库创建的synonym（同义词），这些同义词包括了表、视图、序列、包，那么是否可以通过oracle_fdw来访问序列和包呢？

oracle_fdw的外部表可以是一个SQL语句，基于此，下面做一些实验：

 

6.通过oracle_fdw访问oracle的序列、包和函数等对象：

1）测试序列

oracle端：

create sequence test_id_seq

start with 1

increment by 1

nomaxvalue

nominvalue

nocycle

nocache;

 

select test_id_seq.nextval from dual;

 

pg端：

drop foreign table test_seq;

CREATE FOREIGN TABLE test_seq(

id int

) SERVER oracle_test options (table '(select test_id_seq.nextval from dual)', readonly 'yes');

 

不能配置schema名称，SQL要用括号括起来。

但还是报错：

postgres=# select * from test_seq;

ERROR:  error describing remote table: OCIStmtExecute failed to describe table

DETAIL:  ORA-02287: sequence number not allowed here

 

说明不能通过外部表的方式访问序列？？？

 

测试访问表的SQL：

drop foreign table test_seq;

CREATE FOREIGN TABLE test_seq(

id int

) SERVER oracle_test options (table '(select * from test)', readonly 'yes');

select * from test_seq;

postgres=#

 id

----

  1

(1 row)

 

测试访问一个没有定义为外部表的表：

drop foreign table test_no_fdw;

CREATE FOREIGN TABLE test_no_fdw(

id int

) SERVER oracle_test options (table '(select * from test2)', readonly 'yes');

select * from test_no_fdw;

postgres=#

 id

----

  1

  1

  1

(3 rows)

 

可以访问，说明可以通过这种方式来下放表的jion，因为说明文档中说了，两个外部表的jion会下推到oracle端，增强效率，但是两个表都应该是外部表。

现在又多了一种选择。

 

2）测试函数：

#如果函数有入参怎么处理？

下面实验一下没有入参的函数，看看能否访问：

实验存储过程：

Oracle端：

create or replace PROCEDURE firstPro

         IS

         BEGIN

         DBMS_OUTPUT.PUT_LINE('Hello World!');

         END;

 

pg端：

drop foreign table test_procdure;

CREATE FOREIGN TABLE test_procdure(

info text

) SERVER oracle_test options (table '(begin firstPro(); end;)', readonly 'yes');

select * from test_procdure;

 

postgres=# select * from test_procdure;

ERROR:  error describing remote table: OCIStmtExecute failed to describe table

DETAIL:  ORA-00907: missing right parenthesis

postgres=# \d+ test_procdure

                                 Foreign table "public.test_procdure"

 Column | Type | Collation | Nullable | Default | FDW options | Storage  | Stats target | Description

--------+------+-----------+----------+---------+-------------+----------+--------------+-------------

 info   | text |           |          |         |             | extended |              |

Server: oracle_test

FDW options: ("table" '(begin firstPro(); end;)', readonly 'yes')

 

结论是不能用。