orafce: Oracle functions and packages inside PostgreSQL

A compatibility layer, not a compatibility promise

orafce implements in Postgres some of the functions from the Oracle database that are missing there or behaving differently. The description in the repository says those functions were verified on Oracle 10g and that the module is useful for production work. The README is careful about the scope, saying the module contains functions and operators that emulate a subset of functions and packages from the Oracle RDBMS, and that it helps with porting an Oracle application to PostgreSQL or is generally useful. It is supported in Amazon Aurora with PostgreSQL compatibility and in Azure Database for PostgreSQL, which is a useful signal about how widely it is deployed, and there is an associated Google group for the project. The word subset does a lot of work in that paragraph, and the version numbers in the release history run well past 4.17, so the surface is large even if it is not complete.

The date functions, and why the format strings matter

The largest part of the documented surface is dates, and the reason is that Oracle date arithmetic has conventions Postgres does not share. The README lists the format strings accepted by trunc and round, which run from year, iso year and quarter through week definitions, day and month and century and day-of-year and hour and minute. It notes that the round functions round up, so a date of July 1st is rounded to the next year and the 16th of July to August. The functions include add_months, which returns a date plus n months, last_day, which returns the last day of the month, next_day, which returns the first weekday greater than a date, and months_between, which returns the number of months between two dates. The example in the README is explicit about the fractional month rule.

	months_between(date '1995-02-02', date '1995-01-01') -> 1.0322580645161

The fractional part is calculated on the basis of a 31-day month, which is a rule you inherit rather than choose, and it is exactly the kind of detail that makes porting arithmetic produce different totals.

Oracle's date bug, reproduced rather than fixed

This is the most unusual thing about the extension and worth reading twice. The built-in Oracle date functions were tested against Oracle 10 for conformance, and date ranges from 1960 to 2070 work correctly. Dates before 1582-10-05 with the J format, and before 1100-03-01 with other formats, cannot be verified because of a bug in Oracle itself. So orafce does not quietly return a sensible answer for those dates, it returns an error matching Oracle's, because a silent difference is what causes data to diverge between two systems. The error message says the date cannot be verified due to a bug in Oracle.

	SELECT oracle.to_date('112012', 'J');
	ERROR:  Dates before 1582-10-05 ('J2299159') cannot be verified due to a bug in Oracle.

And the behaviour can be changed, with a setting that turns the reporting off and lets the date through.

	SET orafce.oracle_compatibility_date_limit TO off;

With that off, the same query returns a date in the fourth year of the common era, which is what Oracle itself would give if it did not raise the error. Whether you want that depends on whether you are comparing outputs or restoring old rows.

An oracle.date type and a session schema

Beyond the functions there is a type. The module contains an implementation of the Oracle compatible DATE data type, oracle.date, and the functions that use it, add_months, last_day, next_day and months_between among them, and they work on timestamps with time zone where the plain versions work on dates. The type is used by prefixing a literal, as in an oracle.date value, and the example in the README creates a table with a date column and inserts a value cast to that type, after setting the search path so the oracle schema is visible.

    set search_path TO oracle,"$user", public, pg_catalog;
    create table oracle_date(col1 date);
    insert into oracle_date values('2014-06-24 12:12:11'::date);

The search_path line matters more than it looks, because it shows the extension expects to be reached through a schema named oracle, and every function call in the documentation is qualified with it. The timezone functions follow the same pattern, with sysdate returning a statement timestamp at the server timezone, dbtimezone emulated through a setting of the same name, sessiontimezone returning the current PostgreSQL timezone, and sys_extract_utc converting a timestamp into UTC.

How the extension is built and versioned

The repository layout is that of a PostgreSQL extension in C, with the source files at the root rather than in a src directory, and the names tell you what is implemented: aggregate and alert and charlen and charpad and convert and datefce and file and magic and math and others, plus the plpgsql-derived set of pipe, plvstr, plvdate, plvsubst, plvlex, plunit, random, sqlparse, putline, dbms_assert, dbms_sql, replace_empty_string, utl_raw, varchar2 and nvarchar2, and an orafce.c that ties them together. The extension also has a Makefile and a meson.build, plus an msvc directory and a README.msvc, so it builds with GNU make, with Meson, and with MSVC, and install_bindist.py is for building a bindist. The versioning scheme is the interesting part. Alongside the current orafce--4.17.sql there is a long chain of upgrade scripts, one per PostgreSQL major version from 3.2 onwards, running through the 4.x line, and the release tags follow the same numbering, with VERSION_4_17_1 the most recent. Every PostgreSQL major release gets its own upgrade path, which is what a long-lived extension looks like.

Where to look when the function you need is not there

The README is candid about its own limits in a way that is useful. It says all the functions are fully compatible with Oracle and respect all known format strings, and that detailed descriptions can be found on the internet, and it points at keywords like oracle, round, trunc, date and iyyy as the way to find things. The `oracle` keyword is a hint about the search path convention rather than documentation, but it does suggest the intended way to discover the surface is by trial and by the upstream Oracle manual. The repository also carries a SECURITY.md, an expected directory for the regression test output, a doc directory, an install script and a Makefile with a REGRESS list naming the test groups, which include orafce, orafce2, dbms_output, dbms_utility, files, varchar2, nvarchar2, aggregates, nlssort, dbms_random, regexp_func, dbms_sql, hextoraw and utl_raw. The project uses a Google group for discussion, and the licence is one GitHub cannot classify, so the LICENSE file in the repository root is the authority on the terms.

Editorial conclusion

orafce fits a team moving an Oracle application to PostgreSQL and finding that the SQL depends on Oracle specific functions, or a team running Oracle and Postgres side by side and wanting the same date semantics on both. It does not fit a project that only needs a couple of functions, since the extension is a large surface to take on, and it does not fit a team that would rather change the application than the database. Verify first which Oracle version your source is written against, since the functions were verified against Oracle 10g, and read the date bug notes before you rely on the pre-1582 behaviour, because the extension deliberately reproduces an Oracle defect and can be told to stop complaining about it.

Frequently asked questions

Official sources

1. Issues
2. orafce/orafce on GitHub
3. README
4. Releases