orafce插件是postgresql兼容oracle的插件,安装了以后,pg能够兼容oracle的一些数据类型和表。
下载地址:https://pgxn.org/dist/orafce
环境:centos7.4
数据库:pg12.3
orafce:3.13
安装
[postgres@node1 ~]$ unzip orafce-3.13.4.zip
[postgres@node1 ~]$ cd orafce-3.13.4/
[postgres@node1 orafce-3.13.4]$ make
[postgres@node1 orafce-3.13.4]$ make install
[postgres@node1 orafce-3.13.4]$ psql
psql (12.3)
Type "help" for help.
postgres=# create extension orafce ;
CREATE EXTENSION
postgres=# \dx
postgres=# \dx
List of installed extensions
Name | Version | Schema | Description
--------------------+---------+------------+-----------------------------------------------------------------------------------------------
orafce | 3.13 | public | Functions and operators that emulate a subset of functions and packages from the Oracle RDBMS
pg_stat_statements | 1.7 | public | track execution statistics of all SQL statements executed
plpgsql | 1.0 | pg_catalog | PL/pgSQL procedural language
(3 rows)
使用oracle兼容的函数和表
postgres=# select * from dual ;
dummy
-------
X
(1 row)
postgres=# SELECT CURRENT_DATE "date" FROM DUAL;
date
------------
2021-08-02
(1 row)
postgres=# \dn
List of schemas
Name | Owner
--------------+----------
dbms_alert | postgres
dbms_assert | postgres
dbms_output | postgres
dbms_pipe | postgres
dbms_random | postgres
dbms_utility | postgres
oracle | postgres
plunit | postgres
plvchr | postgres
plvdate | postgres
plvlex | postgres
plvstr | postgres
plvsubst | postgres
public | postgres
utl_file | postgres
(15 rows)
postgres=# select * from oracle.user_tables;
table_name
-------------------------
t1
t2
t3
t4
t11
t12
t13
a1
tbschedulejob
utl_file_dir
pg_statistic
pg_type
t5
a2
a11
a22
a23
pg_foreign_server
pg_authid
pg_statistic_ext_data
pg_user_mapping
pg_subscription
pg_attribute
pg_proc
pg_class
pg_attrdef
pg_constraint
pg_inherits
pg_index
pg_operator
pg_opfamily
pg_opclass
pg_am
pg_amop
pg_amproc
pg_language
pg_largeobject_metadata
pg_aggregate
pg_largeobject
支持特性
数据类型: varchar2、nvarchar2、 date
系统表:
dual 、user_tab_columns、user_tables、user_cons_columns、user_constraints、 product_component_version 、 user_objects 、user_procedures 、
user_source 、 user_views、user_ind_columns等
函数:
nvl、nvl2、lnvl、decode、bitand、nanvl、sinh、cosh等