Qiita Teams that are logged in
You are not logged in to any team

Log in to Qiita Team
Community
OrganizationEventAdvent CalendarQiitadon (β)
Service
Qiita JobsQiita ZineQiita Blog
4
Help us understand the problem. What are the problem?

orafce拡張モジュールの紹介

本エントリーは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さんがテーブル継承について書かれる予定です。

Why not register and get more from Qiita?
  1. We will deliver articles that match you
    By following users and tags, you can catch up information on technical fields that you are interested in as a whole
  2. you can read useful information later efficiently
    By "stocking" the articles you like, you can search right away
4
Help us understand the problem. What are the problem?