ORAFCE: Oracle® Compatibility Functions & Packages


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.


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;
 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;
 "$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;
 2016-04-08 14:15:16
(1 row)