PostgreSQL是和Oracle最接近的企业数据库，包括数据类型，功能，架构和语法等几个方面。甚至大多数的日常应用的性能也不会输给Oracle。
但是Oracle有些函数或者包，默认PostgreSQL是没有的，需要安装orafce包来实现这些兼容性。
例如现在orafce已经包含了如下内容。
1. 类型 date, varchar2 and nvarchar2
2. 函数 concat, nvl, nvl2, lnnvl, decode, bitand, nanvl, sinh, cosh, tanh and oracle.substr
3. dual 表
4. package :
dbms_output
utl_file
dbms_pipe
dbms_alert
PLVdate
PLVstr and PLVchr
PLVsubst
DBMS_utility
PLVlex
DBMS_ASSERT
PLUnit
DBMS_random
orafce的安装步骤如下：
http://pgxn.org/dist/orafce/
下载最新版本。
wget http://api.pgxn.org/dist/orafce/3.1.2/orafce-3.1.2.zip
安装
unzip orafce-3.1.2.zip
mv orafce-3.1.2 /opt/soft_bak/postgresql-9.4.5/contrib
cd /opt/soft_bak/postgresql-9.4.5/contrib/orafce-3.1.2
把pg_config命令放到当前路径，之后就可以编译安装。
export PATH=/opt/pgsql/bin:$PATH
make clean
make
make install
创建extension 。
su - postgres
psql
postgres=# create extension orafce;
CREATE EXTENSION
Oracle兼容 函数列表：
postgres=# \df
List of functions
Schema | Name | Result data type | Argument data types | Type
--------+---------------------+-----------------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------
public | bitand | bigint | bigint, bigint | normal
public | cosh | double precision | double precision | normal
public | decode | bigint | anyelement, anyelement, bigint | normal
public | decode | bigint | anyelement, anyelement, bigint, anyelement, bigint | normal
public | decode | bigint | anyelement, anyelement, bigint, anyelement, bigint, anyelement, bigint | normal
public | decode | bigint | anyelement, anyelement, bigint, anyelement, bigint, anyelement, bigint, bigint | normal
public | decode | bigint | anyelement, anyelement, bigint, anyelement, bigint, bigint | normal
public | decode | bigint | anyelement, anyelement, bigint, bigint | normal
public | decode | character | anyelement, anyelement, character | normal
public | decode | character | anyelement, anyelement, character, anyelement, character | normal
public | decode | character | anyelement, anyelement, character, anyelement, character, anyelement, character | normal
public | decode | character | anyelement, anyelement, character, anyelement, character, anyelement, character, character | normal
public | decode | character | anyelement, anyelement, character, anyelement, character, character | normal
public | decode | character | anyelement, anyelement, character, character | normal
public | decode | date | anyelement, anyelement, date | normal
public | decode | date | anyelement, anyelement, date, anyelement, date | normal
public | decode | date | anyelement, anyelement, date, anyelement, date, anyelement, date | normal
public | decode | date | anyelement, anyelement, date, anyelement, date, anyelement, date, date | normal
public | decode | date | anyelement, anyelement, date, anyelement, date, date | normal
public | decode | date | anyelement, anyelement, date, date | normal
public | decode | integer | anyelement, anyelement, integer | normal
public | decode | integer | anyelement, anyelement, integer, anyelement, integer | normal
public | decode | integer | anyelement, anyelement, integer, anyelement, integer, anyelement, integer | normal
public | decode | integer | anyelement, anyelement, integer, anyelement, integer, anyelement, integer, integer | normal
public | decode | integer | anyelement, anyelement, integer, anyelement, integer, integer | normal
public | decode | integer | anyelement, anyelement, integer, integer | normal
public | decode | numeric | anyelement, anyelement, numeric | normal
public | decode | numeric | anyelement, anyelement, numeric, anyelement, numeric | normal
public | decode | numeric | anyelement, anyelement, numeric, anyelement, numeric, anyelement, numeric | normal
public | decode | numeric | anyelement, anyelement, numeric, anyelement, numeric, anyelement, numeric, numeric | normal
public | decode | numeric | anyelement, anyelement, numeric, anyelement, numeric, numeric | normal
public | decode | numeric | anyelement, anyelement, numeric, numeric | normal
public | decode | text | anyelement, anyelement, text | normal
public | decode | text | anyelement, anyelement, text, anyelement, text | normal
public | decode | text | anyelement, anyelement, text, anyelement, text, anyelement, text | normal
public | decode | text | anyelement, anyelement, text, anyelement, text, anyelement, text, text | normal
public | decode | text | anyelement, anyelement, text, anyelement, text, text | normal
public | decode | text | anyelement, anyelement, text, text | normal
public | decode | time without time zone | anyelement, anyelement, time without time zone | normal
public | decode | time without time zone | anyelement, anyelement, time without time zone, anyelement, time without time zone | normal
public | decode | time without time zone | anyelement, anyelement, time without time zone, anyelement, time without time zone, anyelement, time without time zone | normal
public | decode | time without time zone | anyelement, anyelement, time without time zone, anyelement, time without time zone, anyelement, time without time zone, time without time zone | normal
public | decode | time without time zone | anyelement, anyelement, time without time zone, anyelement, time without time zone, time without time zone | normal
public | decode | time without time zone | anyelement, anyelement, time without time zone, time without time zone | normal
public | decode | timestamp with time zone | anyelement, anyelement, timestamp with time zone | normal
public | decode | timestamp with time zone | anyelement, anyelement, timestamp with time zone, anyelement, timestamp with time zone | normal
public | decode | timestamp with time zone | anyelement, anyelement, timestamp with time zone, anyelement, timestamp with time zone, anyelement, timestamp with time zone | normal
public | decode | timestamp with time zone | anyelement, anyelement, timestamp with time zone, anyelement, timestamp with time zone, anyelement, timestamp with time zone, timestamp with time zone | normal
public | decode | timestamp with time zone | anyelement, anyelement, timestamp with time zone, anyelement, timestamp with time zone, timestamp with time zone | normal
public | decode | timestamp with time zone | anyelement, anyelement, timestamp with time zone, timestamp with time zone | normal
public | decode | timestamp without time zone | anyelement, anyelement, timestamp without time zone | normal
public | decode | timestamp without time zone | anyelement, anyelement, timestamp without time zone, anyelement, timestamp without time zone | normal
public | decode | timestamp without time zone | anyelement, anyelement, timestamp without time zone, anyelement, timestamp without time zone, anyelement, timestamp without time zone | normal
public | decode | timestamp without time zone | anyelement, anyelement, timestamp without time zone, anyelement, timestamp without time zone, anyelement, timestamp without time zone, timestamp without time zone | normal
public | decode | timestamp without time zone | anyelement, anyelement, timestamp without time zone, anyelement, timestamp without time zone, timestamp without time zone | normal
public | decode | timestamp without time zone | anyelement, anyelement, timestamp without time zone, timestamp without time zone | normal
public | dump | character varying | "any" | normal
public | dump | character varying | "any", integer | normal
public | dump | character varying | text | normal
public | dump | character varying | text, integer | normal
public | nanvl | double precision | double precision, character varying | normal
public | nanvl | double precision | double precision, double precision | normal
public | nanvl | numeric | numeric, character varying | normal
public | nanvl | numeric | numeric, numeric | normal
public | nanvl | real | real, character varying | normal
public | nanvl | real | real, real | normal
public | nvarchar2 | nvarchar2 | nvarchar2, integer, boolean | normal
public | nvarchar2_transform | internal | internal | normal
public | nvarchar2in | nvarchar2 | cstring, oid, integer | normal
public | nvarchar2out | cstring | nvarchar2 | normal
public | nvarchar2recv | nvarchar2 | internal, oid, integer | normal
public | nvarchar2send | bytea | nvarchar2 | normal
public | nvarchar2typmodin | integer | cstring[] | normal
public | nvarchar2typmodout | cstring | integer | normal
public | nvl | anyelement | anyelement, anyelement | normal
public | nvl2 | anyelement | anyelement, anyelement, anyelement | normal
public | sinh | double precision | double precision | normal
public | tanh | double precision | double precision | normal
public | to_multi_byte | text | str text | normal
public | to_single_byte | text | str text | normal
public | varchar2 | varchar2 | varchar2, integer, boolean | normal
public | varchar2_transform | internal | internal | normal
public | varchar2in | varchar2 | cstring, oid, integer | normal
public | varchar2out | cstring | varchar2 | normal
public | varchar2recv | varchar2 | internal, oid, integer | normal
public | varchar2send | bytea | varchar2 | normal
public | varchar2typmodin | integer | cstring[] | normal
public | varchar2typmodout | cstring | integer | normal
(88 rows)
Oracle兼容 dual表，在PG里用了一个视图来实现。
postgres=# \dv
List of relations
Schema | Name | Type | Owner
--------+------+------+----------
public | dual | view | postgres
(1 row)
postgres=# \d+ dual
View "public.dual"
Column | Type | Modifiers | Storage | Description
--------+-------------------+-----------+----------+-------------
dummy | character varying | | extended |
View definition:
SELECT 'X'::character varying AS dummy;
postgres=# select * from dual;
dummy
-------
X
(1 row)
postgres=# select 1 from dual;
?column?
----------
1
(1 row)
Oracle兼容 包列表：
在PostgreSQL里用schema+函数来实现。
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
madlib | postgres
oracle | postgres
plunit | postgres
plvchr | postgres
plvdate | postgres
plvlex | postgres
plvstr | postgres
plvsubst | postgres
public | postgres
utl_file | postgres
(16 rows)
例如dbms_alert包:
postgres=# \df dbms_alert.*
List of functions
Schema | Name | Result data type | Argument data types | Type
------------+----------------+------------------+-------------------------------------------------------------------------------+---------
dbms_alert | _signal | void | name text, message text | normal
dbms_alert | defered_signal | trigger | | trigger
dbms_alert | register | void | name text | normal
dbms_alert | remove | void | name text | normal
dbms_alert | removeall | void | | normal
dbms_alert | set_defaults | void | sensitivity double precision | normal
dbms_alert | signal | void | _event text, _message text | normal
dbms_alert | waitany | record | OUT name text, OUT message text, OUT status integer, timeout double precision | normal
dbms_alert | waitone | record | name text, OUT message text, OUT status integer, timeout double precision | normal
(9 rows)
PostgreSQL Oracle 兼容性系列之 - orafce
2015-12-21
6980
版权
版权声明：
本文内容由阿里云实名注册用户自发贡献，版权归原作者所有，阿里云开发者社区不拥有其著作权，亦不承担相应法律责任。具体规则请查看《
阿里云开发者社区用户服务协议》和
《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容，填写
侵权投诉表单进行举报，一经查实，本社区将立刻删除涉嫌侵权内容。
本文涉及的产品
简介：
相关实践学习
阿里云数据库产品家族及特性
阿里云智能数据库产品团队一直致力于不断健全产品体系，提升产品性能，打磨产品功能，从而帮助客户实现更加极致的弹性能力、具备更强的扩展能力、并利用云设施进一步降低企业成本。以云原生+分布式为核心技术抓手，打造以自研的在线事务型(OLTP)数据库Polar DB和在线分析型(OLAP)数据库Analytic DB为代表的新一代企业级云原生数据库产品体系， 结合NoSQL数据库、数据库生态工具、云原生智能化数据库管控平台，为阿里巴巴经济体以及各个行业的企业客户和开发者提供从公共云到混合云再到私有云的完整解决方案，提供基于云基础设施进行数据从处理、到存储、再到计算与分析的一体化解决方案。本节课带你了解阿里云数据库产品家族及特性。
目录
相关文章
|
5月前
|
Oracle
关系型数据库
数据库
【赵渝强老师】在PostgreSQL中访问Oracle
本文介绍了如何在PostgreSQL中使用oracle_fdw扩展访问Oracle数据库数据。首先需从Oracle官网下载三个Instance Client安装包并解压，设置Oracle环境变量。接着从GitHub下载oracle_fdw扩展，配置pg_config环境变量后编译安装。之后启动PostgreSQL服务器，在数据库中创建oracle_fdw扩展及外部数据库服务，建立用户映射。最后通过创建外部表实现对Oracle数据的访问。文末附有具体操作步骤与示例代码。
167
6
7
|
7月前
|
SQL
Oracle
关系型数据库
|
9月前
|
SQL
存储
Oracle
【YashanDB观点】论Oracle兼容性，我们需要做什么
我们经常发现，部分国产数据库声称与 Oracle兼容性高达90%，但在实际迁移过程中，仍需要频繁地修改业务应用的代码。为何实现与Oracle高兼容度的数据库产品如此困难？其中一个重要原因是Oracle兼容性不仅是模仿，而是一个非常复杂和工程量庞大的逆向工程。其技术实现的复杂性以及多如牛毛的细节，足以让多数“年轻”的数据库团队望洋兴叹。YashanDB作为一款从核心理论到关键系统均为原创的数据库产品，从构建初期就具备了技术优势，在Oracle兼容性实现上，敢于亮剑并充分发挥工匠精神，不断打磨，努力构筑一个真正形神兼备的数据库产品。以下将从YashanDB SQL引擎技术、Oracle兼容性的开发
91
9
9
|
9月前
|
SQL
存储
Oracle
|
Oracle
NoSQL
关系型数据库
|
人工智能
Oracle
关系型数据库
|
存储
Oracle
关系型数据库
PolarDB 开源版通过orafce支持Oracle兼容性
背景PolarDB 的云原生存算分离架构, 具备低廉的数据存储、高效扩展弹性、高速多机并行计算能力、高速数据搜索和处理; PolarDB与计算算法结合, 将实现双剑合璧, 推动业务数据的价值产出, 将数据变成生产力.本文将介绍PolarDB开源版通过orafce支持Oracle兼容性 .测试环境为m...
274
0
0
|
SQL
Oracle
关系型数据库
Oracle 实时复制到 PostgreSQL或EnterpriseDB
5388
0
1
|
15天前
|
Oracle
关系型数据库
Linux
【赵渝强老师】Oracle数据库配置助手：DBCA
Oracle数据库配置助手（DBCA）是用于创建和配置Oracle数据库的工具，支持图形界面和静默执行模式。本文介绍了使用DBCA在Linux环境下创建数据库的完整步骤，包括选择数据库操作类型、配置存储与网络选项、设置管理密码等，并提供了界面截图与视频讲解，帮助用户快速掌握数据库创建流程。
189
93
98
|
3月前
|
存储
Oracle
关系型数据库
服务器数据恢复—光纤存储上oracle数据库数据恢复案例
一台光纤服务器存储上有16块FC硬盘，上层部署了Oracle数据库。服务器存储前面板2个硬盘指示灯显示异常，存储映射到linux操作系统上的卷挂载不上，业务中断。
通过storage manager查看存储状态，发现逻辑卷状态失败。再查看物理磁盘状态，发现其中一块盘报告“警告”，硬盘指示灯显示异常的2块盘报告“失败”。
将当前存储的完整日志状态备份下来，解析备份出来的存储日志并获得了关于逻辑卷结构的部分信息。
74
4
4
热门文章
最新文章
1
德哥的PostgreSQL私房菜 - 史上最屌PG资料合集
2
Linux 性能诊断 perf使用指南
3
PostgreSQL 如何潇洒的处理每天上百TB的数据增量
4
时序数据库分析 - TimescaleDB时序数据库介绍
5
MongoDB 存储引擎 WiredTiger 原理解析
6
关于MongoDB Sharding，你应该知道的
7
PostgreSQL OLTP高并发请求性能优化
8
Greenplum 通过gpfdist + EXTERNAL TABLE 并行导入数据
9
PostgreSQL 全文检索加速 快到没有朋友 - RUM索引接口(潘多拉魔盒)
10
PostgreSQL 分库分表 插件之一 pg_shard
1
PostgreSQL 18 发布，快来 PolarDB 尝鲜！
758
2
使用 Java 构建强大的 REST API 的四个基本技巧
86
3
Linux装软件神器：yum 超详细入门指南
428
4
一键开启百倍加速！RDS DuckDB 黑科技让SQL查询速度最高提升200倍
184
5
云时代SQL Server的终极答案：阿里云 RDS SQL Server如何用异地容灾重构系统可靠性
122
6
RDS DuckDB技术解析一：当 MySQL遇见列式存储引擎
527
7
mybatis批量插入对比
231
8
从自建MySQL到阿里云RDS：程序员的数据库减负革命
136
9
【赵渝强老师】Oracle客户端与服务器端连接建立的过程
99
10
客户说｜古茗选用阿里云PolarDB，以云端之力解锁茶饮数字化新高度
172