PostgreSQL Oracle 兼容性系列之 - orafce

本文涉及的产品
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
云数据库 RDS MySQL,集群版 2核4GB 100GB
推荐场景:
搭建个人博客
云原生数据库 PolarDB PostgreSQL 版,企业版 4核16GB
推荐场景:
HTAP混合负载
简介:
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)
相关实践学习
使用PolarDB和ECS搭建门户网站
本场景主要介绍基于PolarDB和ECS实现搭建门户网站。
阿里云数据库产品家族及特性
阿里云智能数据库产品团队一直致力于不断健全产品体系,提升产品性能,打磨产品功能,从而帮助客户实现更加极致的弹性能力、具备更强的扩展能力、并利用云设施进一步降低企业成本。以云原生+分布式为核心技术抓手,打造以自研的在线事务型(OLTP)数据库Polar DB和在线分析型(OLAP)数据库Analytic DB为代表的新一代企业级云原生数据库产品体系, 结合NoSQL数据库、数据库生态工具、云原生智能化数据库管控平台,为阿里巴巴经济体以及各个行业的企业客户和开发者提供从公共云到混合云再到私有云的完整解决方案,提供基于云基础设施进行数据从处理、到存储、再到计算与分析的一体化解决方案。本节课带你了解阿里云数据库产品家族及特性。
相关文章
|
1天前
|
存储 SQL Oracle
关系型数据库Oracle归档日志备份
【7月更文挑战第19天】
17 5
|
1天前
|
SQL Oracle 关系型数据库
关系型数据库Oracle备份工具
【7月更文挑战第19天】
15 4
|
15小时前
|
存储 Oracle 安全
关系型数据库Oracle备份频率
【7月更文挑战第20天】
7 2
|
15小时前
|
存储 Oracle 关系型数据库
关系型数据库Oracle备份策略
【7月更文挑战第20天】
9 2
|
1天前
|
存储 Oracle 关系型数据库
关系型数据库Oracle差异备份
【7月更文挑战第19天】
12 3
|
7天前
|
SQL Oracle 关系型数据库
|
4天前
|
安全 Oracle 关系型数据库
关系型数据库Oracle安全性
【7月更文挑战第17天】
14 5
|
4天前
|
存储 Oracle 关系型数据库
关系型数据库Oracle备份与恢复
【7月更文挑战第17天】
22 4
|
6天前
|
存储 监控 Oracle
关系型数据库Oracle空间不足
【7月更文挑战第15天】
25 6
|
6天前
|
监控 Oracle 关系型数据库
关系型数据库Oracle常见问题
【7月更文挑战第15天】
14 6

相关产品

  • 云原生数据库 PolarDB