ORAFCE: Oracle® Compatibility Functions & Packages

Overview

The ORAFCE Extension is available with BigSQL Windows, Linux & OSX distributions. It provides many Oracle® compatible built-in functions and packages to PostgreSQL. Take a look at the documentation to learn more.

Tutorial

First install the orafce extension using BigSQL Manager or PGC command line:

  ./pgc install orafce3-pg95

Next enable the extension the first time we wnt to use it in a given PG database


postgres=# CREATE EXTENSION orafce;

Now, as an example, you can easily use the Oracle Compatible sysdate function and the dual table as follows.


postgres=# SELECT oracle.sysdate() FROM dual;
      sysdate
---------------------
 2016-04-08 13:14:15
(1 row)

It's inconvenient, in the above example, to prefix the new sysdate built-in function with the oracle schema, because the default search_path has not yet been altered.


postgres=# SHOW search_path;
   search_path
-----------------
 "$user", public
(1 row)

Let's add oracle to the front of our default search path as follows:


postgres=# SET search_path TO oracle, "$user", public;

Now we can use the SYSDATE function with qualifying it into the ORACLE schema.


postgres=# SELECT sysdate() FROM dual;
      sysdate
---------------------
 2016-04-08 14:15:16
(1 row)