本エントリーはPostgreSQL Advent Calendar 2020の12日目です。

orafce拡張モジュールとは

orafceはPostgreSQL上にOracle Databaseと互換性がある関数やデータ型を提供するオープンソース・ソフトウェアです。orafceはPostgreSQL標準の拡張モジュール（Extension）として提供されているため、PostgreSQLを直接変更せずに利用できます。AWSのマネージドサービスであるRDS上のPostgreSQLやAurora for PostgreSQLでも使うことができます（マニュアル）。

利用方法

インストール方法と利用開始までを説明します。

インストール

ソースコードからビルドする場合はGitHubからダウンロードして、PostgreSQLソースのcontribディレクトリに展開してからビルドします。RPMパッケージはpkgs.orgサイト等で見つけることができるでしょう。

以下の例はPostgreSQL 13.1のソースにorafceを加えてビルドする手順です。

$ cd postgresql-13.1/contrib
$ unzip ~/orafce.zip
$ cd orafce
$ export NO_PGXS=1
$ make 
# make install
# cat /usr/local/pgsql/share/extension/orafce.control

# orafce extension
comment = 'Functions and operators that emulate a subset of functions and packages from the Oracle RDBMS'
default_version = '3.14'
module_pathname = '$libdir/orafce'
relocatable = false

インストールが完了すると、pg_available_extensionsビューで拡張モジュールとして参照できるようになります。

postgres=# SELECT * FROM pg_available_extensions WHERE name = 'orafce';
-[ RECORD 1 ]-----+----------------------------------------------------------------------------------------------
name              | orafce
default_version   | 3.14
installed_version | 3.14
comment           | Functions and operators that emulate a subset of functions and packages from the Oracle RDBMS

postgres=#

利用する前に

orafceを利用するためには他のContribモジュールと同じようにCREATE EXTENSION文を実行します。orafceを利用するデータベースにSUPERUSER属性を持つユーザーで接続して実行する必要があります。

postgres=# CREATE EXTENSION orafce;
CREATE EXTENSION

CREATE EXTENSION文を実行すると、多数のスキーマが作成されます。このうちoracleスキーマにはOracle Database互換の関数が定義されているため、pg_catalogよりも先に参照するようにあらかじめsearch_path変数を変更しておきます。

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=> SHOW search_path;
             search_path
-------------------------------------
 "$user", public, oracle, pg_catalog
(1 row)

互換機能

主な互換機能について簡単に説明します。詳しくはマニュアルを参照してください。

DUALテーブル

Oracle DatabaseユーザーにはおなじみのDUALテーブルが提供されています。FROM句を省略できないOracle Databaseの仕様に対応するために作られたダミー用のテーブルです。orafceではVIEWとして定義されています。

postgres=> SELECT * FROM dual;
 dummy
-------
 X
(1 row)

postgres=> \d+ dual
                                 View "public.dual"
 Column |       Type        | Collation | Nullable | Default | Storage  | Description
--------+-------------------+-----------+----------+---------+----------+-------------
 dummy  | character varying |           |          |         | extended |
View definition:
 SELECT 'X'::character varying AS dummy;

データ型

以下のOracle Database独自データ型が提供されています。

VARCHAR2
NVARCHAR2
DATE

Oracle DatabaseではNVARCHAR2型は異なるエンコードを指定できますが、orafceではVARCHARと変わりません。データ長の指定はPostgreSQLの長さ制限に従います（Oracle Databaseよりも長い）。DATE型はPostgreSQLにもありますが、orafceのDATE型はOracle Databaseと同じように時刻まで含みます。これらのデータ型は単なるエイリアスではなく、独自データ型として定義されています。

postgres=> CREATE TABLE emp(emp_id NUMERIC, first_name VARCHAR2(30), last_name NVARCHAR2(30), birth_date DATE);
CREATE TABLE
postgres=> \d emp
                     Table "public.emp"
   Column   |     Type      | Collation | Nullable | Default
------------+---------------+-----------+----------+---------
 emp_id     | numeric       |           |          |
 first_name | varchar2(30)  |           |          |
 last_name  | nvarchar2(30) |           |          |
 birth_date | date          |           |          |

postgres=> SELECT sysdate()::oracle.date, sysdate()::pg_catalog.date FROM dual;
       sysdate       |  sysdate
---------------------+------------
 2020-12-11 13:27:22 | 2020-12-11
(1 row)

関数

Oracle Database独自の関数が多数提供されています。Oracle Databaseでは関数名の後の()を省略できる場合がありますが、orafceでは省略できません。

postgres=> SELECT sysdate() FROM dual;
       sysdate
---------------------
 2020-12-10 02:13:46
(1 row)

以下の関数が提供されています。
- LENGTH / LENGTHB
- SUBSTR / SUBSTRB / INSTR
- NLSSORT
- LTRIM / RTRIM / BTRIM / LPAD / RPAD
- TO_DATE / TO_CHAR(date) / TO_MULTI_BYTE / TO_SINGLE_BYTE / TO_NUMBER
- SYSDATE
- DBTIMEZONE / SESSIONTIMEZONE
- BITAND / COSH / TANH / SINH
- ADD_MONTH / LAST_DAY / MONTHS_BETWEEN / TRUNC
- DECODE / LNNVL / NALVL / NVL / NVL2
- LISTAGG / MEDIAN
- DUMP

LENGTH関数はもともとPostgreSQLにもありますが、orafceのLENGTH関数はOracle Databaseと同じ動作になるように調整されています。下記の例ではCHAR型の列に文字列を格納した場合に、LENGTH関数が後端のスペースを含めるかが変化する様子を示しています。

postgres=> CREATE TABLE char1(c1 CHAR(5), c2 CHAR(5));
CREATE TABLE
postgres=> INSERT INTO char1 VALUES ('ABC', 'ABC');
INSERT 0 1
postgres=> SELECT oracle.LENGTH(c1), pg_catalog.LENGTH(c2) FROM char1;
 length | length
--------+--------
      5 |      3
(1 row)

パッケージ

Oracle Databaseには機能を拡張する機能としてパッケージと呼ばれる機能を持っています。Oracle Databaseには様々な機能を持つ多数の標準パッケージが提供されています。orafceでは新規のパッケージを作ることはできませんが、以下の標準パッケージ互換機能が提供されています。

DBMS_ALERT
DBMS_ASSERT
DBMS_OUTPUT
DBMS_PIPE
DBMS_RANDOM
DBMS_UTILITY
UTL_FILE

以下の例はDBMS_OUTPUTとUTL_FILEパッケージを使ったOracle Databaseのファンクション例とorafceを利用して移行した例になります。完全に同じにはなりませんが、多くの部分がそのまま動作します。

Oracle Databaseの例

SQL> CREATE OR REPLACE FUNCTION func1 RETURN VARCHAR2
  2  IS
  3    v1 VARCHAR2(32767);
  4    f1 UTL_FILE.FILE_TYPE;
  5  BEGIN
  6    f1 := UTL_FILE.FOPEN('DATA_PUMP_DIR', 'test1.txt', 'R', 256);
  7    UTL_FILE.GET_LINE(f1, v1);
  8    DBMS_OUTPUT.PUT_LINE(v1);
  9    UTL_FILE.FCLOSE(f1);
 10
 11    RETURN v1;
 12  END;
 13  /

ファンクションが作成されました。

SQL> SET SERVEROUTPUT ON
SQL> SELECT func1() FROM DUAL;

FUNC1()
--------------------------------------------------------------------------------
DATA1

DATA1

orafceを使った例
DIRECTORYオブジェクトの代わりにUTL_FILE.UTL_FILE_DIRテーブルに利用可能なディレクトリを挿入します。

postgres=# INSERT INTO UTL_FILE.UTL_FILE_DIR VALUES('/home/postgres');
INSERT 0 1

postgres=# \connect postgres demo
You are now connected to database "postgres" as user "demo".
postgres=>
postgres=> CREATE OR REPLACE FUNCTION func1() RETURNS VARCHAR2
postgres-> AS $$
postgres$> DECLARE
postgres$>   v1 VARCHAR2(32767);
postgres$>   f1 UTL_FILE.FILE_TYPE;
postgres$> BEGIN
postgres$>   f1 := UTL_FILE.FOPEN('/home/postgres/', 'test1.txt', 'R', 256);
postgres$>   v1 := UTL_FILE.GET_LINE(f1);
postgres$>   PERFORM DBMS_OUTPUT.PUT_LINE(v1);
postgres$>   PERFORM UTL_FILE.FCLOSE(f1);
postgres$>
postgres$>   RETURN v1;
postgres$> END;
postgres$> $$ LANGUAGE plpgsql;
CREATE FUNCTION
postgres=>  
postgres=> SELECT DBMS_OUTPUT.SERVEROUTPUT(TRUE);
 serveroutput
--------------

(1 row)

postgres=> SELECT func1();
DATA1
 func1
-------
 DATA1
(1 row)

postgres=>

トランザクション

残念ながらトランザクションの動作まで同じにはなりません。トランザクション中にエラーが発生するとCOMMITを実行してもトランザクション全体がロールバックされる仕様はそのままです。

postgres=> CREATE TABLE data1(c1 NUMERIC PRIMARY KEY, c2 VARCHAR2(10));
CREATE TABLE
postgres=> BEGIN;
BEGIN
postgres=*> INSERT INTO data1 VALUES (100, 'data1');
INSERT 0 1
postgres=*> INSERT INTO data1 VALUES (100, 'duplicate');
ERROR:  duplicate key value violates unique constraint "data1_pkey"
DETAIL:  Key (c1)=(100) already exists.
postgres=!> COMMIT;
ROLLBACK

明日は@batakoo3さんがテーブル継承について書かれる予定です。

orafce拡張モジュールの紹介